Pre-requirement, Restriction, Steps to Create & benefits for Database Mirroring

In previous article on 21 March 2012, we had discussed about Database mirroring & log shipping in MSFT SQL server, here we will discuss on following topics:

1. Pre-requirement for Database Mirroring
2. Restriction for database mirroring
3. Steps to Create Mirrored database
4. Benefits of database Mirroring

1. Pre-requirement for Database Mirroring: Before going to create a mirrored database for your database, you must perform below operation:

• Principle database must use the full recovery model
• Make sure that the mirror server has sufficient disk space for the mirror database
• Make sure that both the server must be running on the same version of SQL server
• Make sure that you restore the backup of principle database with specifying the same database name with NORecovery

2. Restriction for database mirroring: There are some restrictions also when you have decided to create a mirror database; bellows are the lists of restriction:

• You can create mirror database for the user database only, can not create mirror database for the master, model, tempdb & msdb.
• It does not support filestream filegroup. You can not configure mirror database for the database that contains filestream filegroup.
• You can not rename mirrored database during the database mirroring session
• You can not configure mirror database for the database that is using simple or bulk-logged recovery model

3. Steps to Create Mirrored database: If you have decided to configure a mirror database for your desired database then follow bellow steps to configure:

• first of all, make sure that both servers principle & mirror have same version of sql server installed
• Now verify that principle database uses full recovery model
• Backup the principle database to a full database backup
• Now verify that your desire mirror server has sufficient disk space to restore full database backup created using principle database
• To create mirror database, restore full database backup on the mirror server instance. Restore database statement must be specify with Restore Database your-database-name with NoRecovery

Note: It is recommended that try to use identical path for the mirrored and principle database.

4. Benefits of database Mirroring: Database mirroring is the part of SQL server high availability solution and offers following benefits:

1. Increase availability of your database: In case of any disaster, failover quickly brings mirrored database online without data loss or with minimum data loss. It minimizes the downtime of your database and helps in recovery of SQL database.
2. Increase the data protection
3. Increase the availability of your production database during upgradation