Thursday, February 27, 2014

Top Tips to Improve DB2 Performance

Along with various other issues, the performance issue is one of the major concerns for DB2 users. If you are a Database Administrator (DBA), then you have to look at this problem more carefully and take all the measures to rectify it. That means being DBA; you have to look for all the possible areas where there are chances of improvements. Therefore, in order to help you out, we are providing you some tips to get back the performance of your DB2:

  • Turn-on Monitor Switches: It is suggested to turn on the monitor switches. If they are not, then you will not have the information regarding performance that you require. Issue the following command to turn-on the monitor switches:

db2 “update monitor switches using   
lock ON sort ON bufferpool ON uow ON   
table ON statement ON”
  • Ensure You Have Sufficient DB2 Agents: Make sure that you have sufficient DB2 agents for handling your workload. Issue the following command to find the number of agents:

db2 “get snapshot for database manager”


After that look for the following lines:

High water mark for agents registered = 7  
High water mark for agents waiting for a token = 0  
Agents registered= 7  
Agents waiting for a token= 0  
Idle agents= 5  
Agents assigned from pool= 158  
Agents created from empty Pool = 7  
Agents stolen from another application= 0  
High water mark for coordinating agents= 7  
Max agents overflow= 0


In these commands, if you find that your agents are waiting or stolen from the application like Agents waiting for token or Agents stolen from another application, then increasing the number of agents available for database manager is recommended.
  • Watch out Maximum Open Files: The DB2 is designed in a way that it can manage multiple open files at a time. With MAXFILOP command, you can get to know about the upper limit of opened files that DB2 can handle concurrently. However, when this maximum limit is reached, DB2 starts closing and opening tablespace files. This slows its performance. Issue the following command to find out if DB2 is closing files or not: db2 “get snapshot for database on DBNAME”. After that, look for the line that reads: Database files closed = 0. If you find that your files are being closed, then increase the value of ‘MAXFILOP’ till this closing and opening stops. Issue the following command for the same:
db2 “update db cfg for DBNAME using MAXFILOP N”

By keeping these points in mind and checking them on a regular basis, you can improve the performance of DB2 and work more efficiently and quickly.
»»  Read More...

Thursday, January 23, 2014

Security Features of MS Access 2010

MS Access is one of the most useful applications of MS Office suite. MS Access is a database management system that is suited for home users and small businesses. MS Access 2010 is included with MS Office 2010 suite. Along with various other useful and efficient features, this application includes more reliable ‘Security Features’. The security features of MS Access 2010 ensure enhanced security of your database file. The enhancements to these features help you to apply them more easily than before. Mentioned below are different security features included in MS Access 2010:

  • The ‘Trust Center’: The ‘Trust Center’ is a common feature of all MS Office applications for managing and changing the security settings of Office application. The location of ‘Trust Center’ in MS Access 2010 is ‘File >> Trust Center >> Trust Center Settings’. Generally, it performs a set of security checks when you open your database file. The process it follows for checking your database is as follows:
When you initially open the .accdb or .accde file, then Access submits the location of the database to the ‘Trust Center’. If ‘Trust Center’ finds that the location is trusted, then this database file opens with all its functionalities. If you open a database that has an earlier file format, then too Access submits that location and digital signature to ‘Trust center’.

Note: The decision made by ‘Trust Center’ for opening an Access file entirely depends upon the settings that you or your system administrator has made in ‘Trust Center’.
 
  • Disabled Mode: When the ‘Trust Center’ finds a database unreliable, it opens this database in ‘Disabled Mode’. That means it turns off all its executable content irrespective of its file format. The contents that are disabled by MS Access in disabled mode are VBA code, action queries, Data Definition Language (DDL) queries, SQL pass through queries, ActiveX controls, and unsafe actions in all macros. However, when you disagree with the decision of ‘Trust Center’, you can use the ‘Message Bar’ to enable its contents. 
  • Encrypt the Database with Password: Access 2010 enables you to password-protect your database. The password ensures that any unauthorized computer user does not use the data. Moreover, the password-protected Access database file is unreadable by any other Access application as well until you provide the correct password to open it. You can also encrypt the content of your Access database in Access 2010 version.
With these features, MS Access 2010 provides more reliable and trusted working environment to its users.
 
»»  Read More...

Friday, August 23, 2013

All about RMAN backup


RMAN stands for Recovery Manager, is the backup and recovery solution of Oracle database. It is the preferred method for backup and recovery. The best part about RMAN is that it detects block corruption while performing backup. It can be accessible through command line or Oracle Enterprise Manager. Since RMAN is the feature of Oracle so no separate installation is required for it. It comes with some advanced features like parallelization of backup/restore; keep history of all backup and many more. You can backup following files through RMAN:



  1. Archive redo log file
  2. Data files
  3. Control files
  4. SPFILES.

Types of backup in RMAN:-
  1. Full backup: It is used to backup whole database block by block.
  2. Incremental backup: It can be of two types, either a 1 or 0 levels backup.
  • Level 1 backup: It backups only those blocks that have been changed since the last parent backup. Parent backup can be of 0 level or 1 level.
  • Level 0 backup: It is same as full backup. The only difference is that it does not backup empty blocks.
  1. Consistent backup: It is performed when database was shutdown using SHUTDOWN command.
  2. Inconsistent backup: It is performed when database is open or failed after crash.
  3. Open backup: It is performed when database is in online mode.
  4. Closed backup: It is performed when database is in offline mode.

How to perform backup with RMAN?
To backup whole database: Use following command to backup the whole database:
RMAN> BACKUP DATABASE;

RMAN new features in Oracle 11g
  1. Data Recovery Advisor  Data Recovery Advisor automatically detects corruption and data failures. It repairs block corruption, data dictionary corruptions and many more.
  2. Cloning of database: You can easily clone your database without requiring existing backups.
  3. Improvement in backup compression: In Oracle 11g, RMAN backup compression is done using ZLIB algorithm which is much faster than BZIP2 algorithm that is used in Oracle 10g.
  4. Check physical corruption: A new command is introduced in Oracle 11g to check database for physical corruption. The command is VALIDATE DATABASE.

Why use RMAN?
RMAN is the preferred method of backup and recovery. Since RMAN is supported by Oracle so while using it if you find any issue you can always rely on Oracle for support. Here are the advantages of using RMAN:
  1. RMAN automatically detects block corruption while performing backup. In RAMN you can recover only few corrupted block unlike traditional backups where you have to backup entire data file.
  2. RMAN also performs incremental backup. In incremental backup only those blocks are backed up that are changed since last backup. So it reduces recovery time because now you don't have to backup the entire database.
  3. It provides automatic backup of controlfile and spfile.
  4. You can also perform encrypted backup using RMAN which is not possible in traditional backups.
  5. Using RMAN you can easily check any backup that whether it is recoverable or not.
  6. In RMAN backup compression there is a feature by which you can skip unused blocks.
  7. RMAN can work fairly well with third party tools.
  8. RMAN backup is much faster than other traditional backups.
  9. RMAN has a highly advanced feature named Data Recovery Advisor that automatically detects and repair block corruption.
  10. RMAN automatically deletes expired or unnecessary backup of data file from disk.
  11. You can perform backup in online mode without putting the tablespace in hot backup mode.

RMAN backup best practices
If you are using RMAN as your backup strategy then follow best practices for performing backup. However RMAN is intelligent enough to automatically detect which files need to be restored and backup. Consider following factors while performing backup

  1. Choose the backup option depending upon your business requirement: RMAN provide many type of backups. Choose the one that can provide fastest recovery for your business.
  2. Check validity of backup: Perform a media check on your backup storage. A disk to disk backup is considered as most reliable and fastest backup.
  3. Check the speed of backup: Check how long does it take to perform backup.

RMAN backup optimization
There are two commands in RMAN backup optimization. Here they are:
RMAN> configure backup optimization ON;
RMAN> configure backup optimization OFF;

When backup optimization is ON then BACKUP command will skip the backup of that file which has already been backed up to a specific device. RMAN check for identical files; for example in case of datafile, it will check for checkpoint SCN, DBID, creation SCN to see if they are already backed up. By default backup optimization is OFF. Consider some of the key features of configure backup optimization ON command:
  1. If you want to backup flash recovery area using RMAN then you need to set the Configure backup optimization command to ON.
  2. If you set backup optimization to ON then RMAN will skip the backup of identical files.

Some of the useful commands of RMAN are:

RMAN> show all; (It shows all the RMAN parameters of the database.
RMAN> backup database; (To perform database backup)
RMAN> delete expire backup; (To remove archive log)
RMAN> report obsolete; (To delete obsolete backup)
RMAN> alter database; (To mount a database)
RMAN> delete; (To delete backup copy of database)
RMAN> exit; (To exit RMAN executable)
RMAN>restore; (To restore files from backup to an new location)
RMAN> duplicate; (To create a duplicate database of target backup for testing purpose)
RMAN>show; (To show the current configuration settings.) and list can go on....

»»  Read More...

Friday, July 26, 2013

How to move SQL server database from one location to another?


Do you want to change the location of your SQL server database? If yes then it is not a tough task! SQL server database files (data or transaction log) can be detached and reattached to the different instances of SQL server at the same machine or move to the another machine. Detaching & Reattaching is the best way to move the SQL server database from one location to another.

Steps to change the location: You will have to perform 3 tasks to change the location of your SQL server database from source computer to designation computer and the task are...
  1. Detach the SQL server database from source computer.
  2. Copy the database on the designation computer.
  3. Attach the SQL server database on the designation computer.
Detach a SQL server database: Detaching is a process to remove database from the SQL server instance without altering the data within data and transaction log file. These files are used to reattach the database to another SQL server instance at the same machine or instance at the different machine.

How to Detach a Database: SQL server database administrator can detach a database by two methods:
  1. Detach a database using stored procedure.
  2. Detach a database using SQL server management studio (SSMS).
Detach a database using Stored Procedure: Run the below syntax to detach a database using stored procedure (sp_detach_db).

sp_detach_db [ @dbname= ] 'database_name'
[ , [ @skipchecks= ] 'skipchecks' ]
[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

Output: If output is '0' then you have successfully detach the database from instance, if '1' then you will get an error message on your screen. In case of failure, try to run the syntax again.

Detach a database using SQL server management studio: Follow below steps to detach a database using SSMS:
  • Connect to the instance and then expand it.
  • Now expand the databases, and select your desired user database.
  • If database is in use currently then restrict it to single access.
  • Right click on the database->Tasks->Detach.
  • Detach dialog box will be open on your screen.
  • Select the database from the database name column to detach.
  • Verify that you are detaching the right database.
  • Click on the 'OK' button to start the detach process.
Note: Always perform detach operation on the database when database is not in use.

Limitations: A database administrator can't detach a database when any of the following point is true:
  1. Database is being mirrored in mirroring session.
  2. Detach database is a system database.
  3. Database is in suspect mode.
  4. Database is replicated.
How to attach a database: Database administrator can attach a previously detached database from the same machine or database files are location at another machine. There are 3 methods available to attach a SQL server database files.
  1. Attach a database using stored procedure
  2. Attach a database using 'Create Database' statement
  3. Attach a database using SQL server management studio
Attach a database using stored procedure: It is the easiest method to attach a database to the SQL server instance. Microsoft suggest that don't use this method to attach a production database using it because it will be remove in the future version of SQL server. To attach a database, run below stored procedure:

sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]

Where [ @dbname= ] 'dbname' is the name of database & [ @filename1= ] 'filename_n' is the physical file name.

Output: If output is '0' then you have successfully attach the database to an instance, if '1' then you will get an error message on your screen.

Attach a database using 'Create Database' statement: It is the recommended method for attaching a new or detached database to the SQL server instance. To attach a database using this method, run below T-SQL syntax:

To attach a database
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]

Attach a database using SSMS: Follow below steps to attach a database using SSMS.
  • Connect to the instance and then expand it.
  • Now right click on the databases, and select attach.

  • A attach dialog box will be open on your screen.
  • Click on the 'Add' button to specify the database.

  • Select the database from the disk drive for attach.
  • Verify that you are attaching the right database.
  • Click on the 'OK' button to start the attach process.
Note: To attach a database, all the data files (.mdf & .ndf) are necessary. If any of the data file is unavailable then you can't attach the database to SQL server instance.

Need to attach a database without Transaction log file: As I have mentioned above, for attaching a database all SQL server data file (.mdf & .ndf) are necessary but you can attach a database without transaction log file (.ldf). Follow below steps to test this.
  • Download a sample database (AdventureWork) from the codeplex website.
  • After downloading the sample database, you will have only sample data file not transaction log files.
  • Now attach this sample database using SSMS.
  • Connect to the instance and then expand it.
  • Now right click on the databases, and select attach.
  • A attach dialog box will be open on your screen.
  • Click on the 'Add' button to specify the database.
  • Select the database from the disk drive for attach.
  • Verify that you are attaching the right database.
  • To attach database without transaction log file (.ldf); select & remove transaction log file. Now click on 'OK' to attach.
  • SQL server automatically creates a transaction log file (.ldf) for you while attaching SQL server database.
Summary: Now you are able to detach & reattach the SQL server database. Additionally you can attach database without the transaction log file.
»»  Read More...

Tuesday, July 23, 2013

A Beginner's Guide to Improver SQL Server Database Performance


Most of the SQL server users do not think about SQL database performance before creating a database but after creating SQL database they realize that their database performance is decreasing day by day. After this, they think about how to gain the database performance as similar to the early database. The main purpose of this article is to guide you this point at the initial stage (when you create a new database) to overcome the database performance problem. Before going in details, it will be better to summarize files & filegroups.

Files:
To create and manage a database, MS SQL uses some files. All the related data and objects of the database such as tables, indexes, views, triggers, stored procedure are stored in operating system files. Following are the list of operating system files that are used by SQL server to store database:
  • Primary File: It is the starting point of the database and points other files of the database. Primary file is used to store data. Every SQL server database has one primary file and the recommended file extension for the primary file is .mdf. I have used term recommended because you can choose any other file extension for your primary file as you want. But it may create some problem to understand what file is by others.
  • Secondary File: A file that is not a primary file called secondary file. It stores all the data & the data that does not fit into the primary file. If your primary file can store all the data of database then there is no need to have secondary file. It is shortly known as optional data file. Some databases have many secondary file while others may have not any secondary file. The recommended file extension for secondary data file is .ndf.
  • Transaction Log File: It stores all the log information that might be required to recover a database. Every SQL server has at least one transaction log file and it can be more than one. The recommended file extension for transaction log file is .ldf.

Filegroups:
As name suggests it is used to group database objects and file together. There are two types of filegroups available in the SQL server: primary filegroup and user-defined filegroup.
  • Primary Filegroup: It contains primary data file (.mdf) and other files that are not associated with any other filegroup.
  • User-Defined Filegroup: A filegroup specified by using filegroup keyword in create or alter database statement is known as user-defined filegroup.
Create a new database: A simple new database can be created using primary data file that holds data & objects of the database and a transaction log file that stores log information. Alternatively, database administrator can create a complex database using one primary data file and two secondary data files. All the data and objects of the database are spread across three files and two transaction log files. As we have discussed above filegroup groups all the files for the administrative purpose.

Example
Two secondary data files can be created on two disks and associated with a filegroup. Now a table can be created specifically on the filegroup. Queries for data from the table will be spread across two disks, thereby improving performance. The same performance improvement can be accomplished with a single file created on a RAID stripe set.

Note: Files and filegroups help database administrator to add new files to the new disk drives. If you notice that your database has reached the maximum size for a single Windows NT file then, you can use a new secondary data files to gain size.

Rules for designing files and filegroups: When you design file or filegroup, you should adhere given below rules.
  • Make sure that any file or filegroup cannot be used by more than one SQL server database. For example, student.mdf & student.ndf hold the data and objects of the student database, so it cannot be used by the teacher database.
  • A file (primary or secondary) can be member of only one filegroup.
  • Transaction log file (.ldf) is never a part of any filegroups.

Recommendations for designing files and filegroups:
  • Most of the database work better with single data and log file.
  • Try to place transaction log file on different physical disk location that does not has files and filegroup.
  • If you have multiple files then try to create a new filegroup for the additional files and make it as default filegroup. In this way, primary filegroup will contain tables and objects of system only.
  • Try to use filegroup in such manner that it works as a placement of objects on the specific physical disk.
If you create a new SQL server database by following manner then I ensure that your database will run smoothly.

STEP 1:
Open Microsoft SQL server management studio (SSMS) and Right click on databases and then click on new database. To create a new database by default value then click on the OK button otherwise write the database name. Database name can be anything, we have take Firstdatabase as for the example purpose. A window will be open like bellow figure.

SQL server management studio -> Right on the databases -> New database -> OK (default value)

Automatically logical name of data & log file of the database 'Firstdatabase' will be assigned by Firstdatabase_data and Firstdatabase_log. Data file of the Firstdatabase will be created on Primary filegroup because it is the default filegroup for the database. To ensure that click on the Filegruop, you will see there is only one filegroup. Here the entire Firstdatabase_data file will store all system data & objects. Now close this window.

STEP 2:
Now right click on newly created database (Firstdatabase) and point to the properties. Now click on the Filegroups option and then Add button. A newly blank row will be added into list. Give the name of this newly created filegroup as Secondary and click on OK button.

Database -> Properties -> Filegroup option -> Add -> Rename -> Ok

Step 3:
Again right click on Firstdatabase database and goes to the properties section. Now click on files and then Add button. A blank new row will be added into database files list. Give the logical names as Firstdatabase_User (for example) and choose filegroup as Secondary. Finally click on OK button to finish the task.

Database -> Properties -> File option -> Add -> Rename -> Filegroup selection ->Ok

Step 4:
Again right click on firstdatabase database and points to the properties section. Now select secondary Filegroup as default Filegroup and click on the Ok button.

Database -> Properties -> Make Secondary Filegroup as default Filegroup

From now whatever data you store or object you create, it will be automatically stored or created into specified default filegroup. Here SECONDARY is the default filegroup. Now the question is how you would know that the objects you are going to create it will be automatically created into SECONDARY filegroup. Create a table and see the properties of that table. You will see the filegroup of that table is SECONDARY.

In this way if you separate the system data and objects from user data and objects, your database will run smoothly.
»»  Read More...

Sunday, June 30, 2013

SQL Server Maintenance Plans


SQL Server Database Maintenance Plans help users to maintain SQL databases and their structures. It also helps to automate many database administration tasks in Microsoft SQL Server. You can create maintenance plans very easily by using an easy wizard-based process. SQL Server Maintenance Plans is the group of many tasks that you need to perform on SQL Server Database to improve the performance and availability. 

You can easily check the list of tasks in the below picture:  



You can select the maintenance tasks from “Maintenance Plan Wizard” whatever you want to perform. You can also select one or more tasks according to your need. You can also change the sequence of the tasks on Select Maintenance Task Order page; use the Move Up and Move Down buttons. 





Before creating the maintenance plan, please check out some benefits & shortcomings of maintenance plan:

Benefits:


  • Support for multiserver plans  
  • Support for logging plan history to remote servers.
  • Support for Windows & SQL Server Authentication.  


Shortcomings:

It is not cover all database maintenance options: If you want to use any other database maintenance option which is not available in the “Maintenance Plan Wizard” then you have to use scripts for those tasks. 

Can’t Run Multiple Tasks: Some tasks become difficult to perform because it can only run once within a single maintenance plan. Like if you create a plan to delete backup files then it can delete one file type at a time not multiple backup file type at the same time.  

Can't script and moved to other SQL Server instances: When we create Maintenance Plans with the Wizard, it cannot be scripted and moved to other SQL Server instances. But you can created multi-server Maintenance Plans. 

Bugs available in earlier SQL Server versions: There are few bugs available in earlier version of SQL Server Maintenance plan but it get resolved in SQL Server 2005 Service Pack 2 or higher, or SQL Server 2008.

Let’s discuss one by one: 

1. Define Database Check Integrity (CHECKDB) Tasks


First Select “Check Database Integrity” option that allows you to check internal consistency of the database then index database pages. After selecting this option, you will see “Define Database Check Integrity Task” panel. 





On the Define Database Check Integrity Task page, click on the drop-down button to the right of the database. Now you will have four options:

1) All databases
2) System databases
3) All user databases 
4) These Databases

Choose your database from the list or you can also select multiple databases. After that Click OK to close the database selection window, then click next.


Define Database Shrink Tasks

First Select “Shrink Database” option that allows you to shrink databases which compact the database and saves diskspace. This process is done by clearing logs and empty data pages. 





On the Define Shrink Database Task page, click on the down-arrow to the right of Database. Now you will have four options:

1. All databases
2. System databases
3. All user databases 
4. These Databases

Choose your database from the list or you can also select multiple databases. After that Click OK to close the database selection window. Click Next.

Reorganize Index

First Select “Reorganize Index” option that allows you to Reorganize index function, defrag indexes on sql tables. You can also check which improves search performance with the help of this option. 

Rebuild Index

First Select “Rebuild Index” option that allows you to rebuild the whole index. It helps to increase the search times. 

On the Define Rebuild Index Task page, you will see three options:

1. Databases
2. Free Space Options
3. Advanced Options 





Choose your database from the list or you can also select multiple databases. After that click on checkbox 'reorganize pages with default amount of free space'. Click Next.

Define the Update Statistics Task

First Select “update statistics task” option to ensure that table values are up to date. It helps to make better decisions regarding data access.   

On the Define the Update Statistics Task page, you will see three options:

1. Databases
2. Update
3. Scan Type





Choose your database from the list or you can also select multiple databases. After that click on checkbox 'all existing statistics' and 'Full Scan' Click Next.

Define the History Cleanup Task

On the Define History Cleanup Task page, you can select historical data to delete after that click Next.




Define the Execute Agent Job Task

On the Define Execute Agent Job Task page, you can select a single job or multiple jobs to run. This option is not available if you have no SQL Agent jobs.  

Define Backup Tasks:

You can use this option to perform full, differential and log backups to prevent database from corruptions. To create a backup job on SQL Server 2012 using maintenance plans:

On the Define Back Up Database (Full) Task page, click the down-arrow to the right of Databases and choose your database from the list.  Choose the media for the backup (i.e., Disk or Tape) and the location for the backup. Select Verify backup integrity. Click Next.





Define Maintenance Cleanup Tasks

On the Define Maintenance Cleanup Task page, you can delete same type of files at the same time.

For this, first specify the types of files to delete including text reports created by maintenance plans and database backup files. You can also delete specific file by using “File location option”. Or there are multiple option to delete a file. After that click 'Next'





After finishing all maintenance option, now you need to select report option.  

Select Report Option: 

You can save report maintenance plan as indicate in following picture:





























1.Save it in a folder 
2.email report

Complete the Wizard: 

In the last step, review your plan what you are creating. After that click on Finish button

You can also edit your maintenance plan if you wish to. You can do it by using SQL Server agent. 

Here is the small overview: 




Congratulation your maintenance plan is ready.
»»  Read More...

Monday, June 3, 2013

SQL Server 2012- FileTables Overview


SQL Server 2012 introduced a new feature named as FileTable that allows you to store file, directory, and documents in a special table that builds on the top of SQL Server FILESTREAM technology. The benefit of this feature is that it provides Windows API compatibility for file data stored within a SQL Server database & also compatible with SQL Server features including management tools, services and file attribute data. SQL Server also supports backup and restore job for this feature.  

Below three steps must be completed, in order to creating and using FileTable:

1. Enable FileStream at the instance Level
2. Create a FILESTREAM-Enabled Database
3. Create a FileTable 

1. Enable FileStream at the instance level Via SQL Server Configuration Manager: 


  • Open SQL Server configuration Manager.
  • Locate SQL Server Instance on which, you need to enable file stream on it.
  • Right click on instance and select Properties












  • Select Filestream and Click on the checkboxes 'Enable FILESTREM for transact SQL access' and 'Enable FILESTREM for file I/O access' and enter windows share name as well.  







  • Click apply


2. Create a FILESTREAM-Enabled Database Via SQL Server Management Studio:


  • Create a new database in SQL Server Management Studio by right click on the database. 
  • Now enter all the information required to create a simple database.
  • Click on options and set two properties mark as red circle in the image:







  • Now click Ok. Finally database is created. 


3.Create a FileTable Via SQL Server Management Studio

In Object Explorer, Right click on the Tables folder, and then select New FileTable. 






Now a new script window will open. It contains a T-SQL template that you can customize and run to create a FileTable. 

I will recommend this feature to all users who want to store files/directories inside database.  
»»  Read More...

Tuesday, April 30, 2013

SQL SERVER 2012 – Auto Recovery Feature Overview


Have you ever face the SQL Server Management Studio crashes issue without saving the SQL scripts. In this case, SQL SERVER Auto Recovery Scripts is one of the helpful features introduced in SQL Server 2012. It automatically saves open SQL scripts according to user definable period of time. By default, this time period is set for 5 minutes. User can change it according to their needs. By default, this feature is turned on with default settings in SQL SERVER 2012 (SSMS). 

Location of Auto Recovery Option in SSMS:

This option is available in Tools >> Options >> Environment >> AutoRecover

Settings available in Auto Recovery:

Given below are the settings available in Auto Recovery.


  1. Save AutoRecover Information every: By this setting, you can set automatic saving of your script; it means after how many minutes your script should be saved automatically. By default, this setting is after 5 minutes. 
  2. Keep AutoRecover information for : By this setting, you can set for how many days SSMS should keep those files. By default, this setting is 7 days.


Here is full overview picture of Auto Recovery setting:





You can find the recovery files here: C:\Users\windows user\Documents\SQL Server Management Studio\Backup Files\Solution1






NOTE: SSMS window might get hang for some time due to “Saving Auto Recovery information“.

If you want to disable this feature then you can also do this by using two methods:

First Method:

Open SSMS 2012 & click on Tools >> Options






Uncheck "Save AutoRecover information every" checkbox to disable Auto Recovery:





Second Method:

You can also do it by make adjustment in registry setting.

1.  Open Registry

Start -> Run -> RegEdit





2. Find and modify registry value

The location of this registry key in SSMS 2012:
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\General\AutoRecover

When you double click on “AutoRecover Enabled" then its value will be "1" (ON), it means that Auto Recovery setting is enable. To disable Auto Recovery setting, set it to "0" (OFF) then press OK to save. 

So you can easily recover your unsaved SQL script from SQL Server Management Studio by using SQL Server 2012 AutoRecovery Feature.
»»  Read More...

Sunday, April 14, 2013

Overview of Access Database 2013 Apps

Access app is an advanced feature of Access Database 2013 that is used in web browser for sharing and collaboration within your organization. To use an Access app, you need Access 2013 and SharePoint Server 2013. You can create an app using two options: 

From a template:

  • Click on Access startup screen>> featured templates>>click on globe icon to create Access apps:






  • Click on template and enter a name in the App Name box.
  • Select a web location for the app 
  • click Create.


From a scratch: 

  • Open Access>>click on Custom web app






  • Enter a name
  • Select Web location for your app 
  • click Create.


What NEXT?

Now you will need to add some tables in your new created app. You can either add template tables or just start a new blank table


To add a template table:  As shown in figure just type in search box what kind of information you want to store like tasks, employees, projects etc. Now click on Search button and select a suitable table from the list: 






To add new blank table:  


  • Click on Add a new blank table.
  • Now You will the newly created tables in the left pane.


Now WHAT?

Enter Data in the Tables: 


  • Home tab>>click Open in Browser
  • Select the table and view you want to use
  • use the action bar buttons to add, edit, and delete items.

Import data from an external source:

As shown in figure, You can also import data like Access desktop database, a Microsoft Excel file, an ODBC Data, a text/CSV file, or a SharePoint list. This data will be added as a new table. 






  • Start App>>open it in Access (in the browser, click Settings > Customize in Access)
  • click Home > Table.
  • Now you can see “Add Tables page” as clearly shown in picture. 
  • At the bottom of this page, click the type of data source you want to import, and follow the instructions in the Get External Data wizard.






VERY VERY IMPORTANT:

Microsoft also add backup feature in Access web apps so that you can take a backup of your apps that can be restored to a different location. You can create web apps backup copies in App Package files and restore them by uploading them to any SharePoint 2013 site. 

Saving your App


To backup your app, save it as a SharePoint app package by clicking File > Save As > Save as Package. 






Now App Package file is saved to your system.
»»  Read More...