SQL Server – Database Mirroring for Disaster-Recovery Solution

What is Database Mirroring?

Database mirroring is a solution for increasing the availability of a SQL Server database. But it has been gained popularity as a disaster-recovery option.

How Database Mirroring Working.

The principle server sends the active transaction log record to the mirrored server. The mirrored server applies the transaction log record one by one in sequence.

Prerequisites

• The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server.
• The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database.
• When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY.
• We cannot mirror the master, msdb, tempdb, or model databases. Only user databases can be mirrored.
• A mirrored database cannot be renamed during a database mirroring session.
• Database mirroring does not support FILESTREAM feature.
• Database mirroring can support a maximum of about 10 databases per server instance on a 32-bit system.
• Cross-database transactions or distributed transactions in not supported in Database mirroring.

Restrictions

• We cannot mirror the master, msdb, tempdb, or model databases. Only user databases can be mirrored.
• A mirrored database cannot be renamed during a database mirroring session.
• Database mirroring does not support FILESTREAM feature.
• Database mirroring can support a maximum of about 10 databases per server instance on a 32-bit system.
• Cross-database transactions or distributed transactions in not supported in Database mirroring.

Here we will give you demo – How to Setup the database Mirroring.

• Go to database Properties >> Navigate to Configure Security
  

  SQL Server – Database Mirroring for Disaster-Recovery Solution

• Click Next

• For the demonstration purpose, we are not going to configure the witness server that is used in synchronous mode with automatic failover.

• Here we have selected the Principal Server where originally database located.

• Here we selected the Mirror instance where mirror copy will be located.

• Here we mentioned the service account of principal and Mirror server Instance.

• Successfully configure the endpoints.

• Once, you click the close button, dialog box prompt you to start the Database Mirroring. We have to not started the Database mirroring and close that dialog box.   Because we would like to change the operating mode to High performance (asynchronous). Must refer Book Online for study about the “Operating Mode”. Before starting the mirroring make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY.

Once you start mirroring. You principal database looks as shown in below snapshot:

You Mirror database looks as shown in below snapshot:

For Database Mirroring Administration click here