Database Mirroring in SQL Server - Introduction (Part - 1)

The most important feature for any application is Availability. An end user gets satisfied completely when the application is available at any time. This availability is achieved from the database itself. SQL Server offers many such availability mechanisms. One of such mechanisms is Database Mirroring. I'm going to produce a series of articles on Database Mirroring.

Database Mirroring was first introduced in SQL Server 2005 and it is available in Enterprise, Developer, Business Intelligence and Standard Editions. The main concept of Database Mirroring is maintaining a standby copy for your database and keeping both the databases in a sync so that the copy database serves the clients when the main database faces any kind of problem.

There are three instances participated in Database Mirroring,

Principal - This is where the main copy of database resides.

Mirror - This is where the standby copy of database resides.

Witness - This is an optional instance which initiates automatic failover from Principal to Mirror and is set only in High Safety Operating Mode which I'll discuss later in this post.

When database mirroring is set up, Principal sends the transaction log records to the Mirror over network and Mirrors gets synchronized with Principal. When Principal becomes unavailable due to any problem, Mirror becomes the new Principal and serves the clients. This process is called Failover. When a Witness is configured, it initiates this failover automatically.

In SQL Server, Database Mirroring allows only one mirror for one principal. To have more than one mirror, another availability solution namely AlwaysOn High Availability should be used which supports up to four replicas for one principal. In SQL Server 2012 Books Online it is specified that Database Mirroring will be deprecated. This means in the next release (SQL Server 2014), it is supported but will not be available in a later release of SQL Server. So, Microsoft recommends to use AlwaysOn High Availability.

Requirements for Database Mirroring

Before setting up database mirroring, the following conditions should be satisfied:

• The Principal and Mirror (collectively called Partners) should be of same version and edition of SQL Server.
• The Witness should be of same version and can be of any edition. Even an SQL Express can serve as Witness as it is just a monitoring one.
• Principal, Mirror and Witness should be configured on three different instances.
• The partner databases should be in Full Recovery Model.
• There should be enough disk space on Mirror server for Mirror database.
• The latest backup of Principal database should be restored on Mirror Server WITH NO RECOVERY because this allows inserting transaction log records into the database.
• The log backup of Principal database should be taken and restored to the newly restored Mirror database WITH NO RECOVERY.
• The Principal and Mirror databases should be with same name.

Failovers in Database Mirroring

There are three types of failovers in Mirroring,

Automatic - When Principal is down, the mirror becomes Principal automatically. This is done by Witness.

Manual - When Witness is not configured and Principal is down, Database Administrator has to make the mirror as Principal manually.

Forced - When Principal is down, the mirror is forcibly made the Principal with some data loss.

Automatic and Manual Failovers do not incur data loss and are supported in Synchronous Database Mirroring whereas Forced Failover causes data loss and is supported in Asynchronous Database Mirroring.

Operating Modes of Database Mirroring

There are two operating modes of Mirroring which impacts the application safety and performance. 

• High Safety Mode (Synchronous Database Mirroring)
• High Performance Mode (Asynchronous Database Mirroring)

Let us know what these modes are:

High Safety Mode (Synchronous Database Mirroring)

As said earlier, Principal sends transaction log records to the Mirror constantly. In High Safety Mode, when a transaction is made on Principal, it doesn't commit the transaction immediately. It writes the log to its disk and sends it to the mirror database. Now principal waits until mirror writes the log to its disk and sends an acknowledgement to Principal. After receiving the acknowledgement, principal commits the transaction to the client.

This mode assures full safety for data and keeps both principal and mirror databases in a synchronized state but increases transaction time. This mode requires an optional instance configured, called Witness. When witness is configured, it performs automatic failover from principal to mirror in case of any failure in principal. Even manual failure can be done when witness is present. If a witness is not configured then the database administrator has to perform manual failover in case of any failure in principal.

If Witness is not configured and Principal is down then the Mirror is suspended. In such cases, a Forced Failover can be done to the Mirror with some data loss.

High Performance Mode (Asynchronous Database Mirroring)

In High Performance Mode, when a transaction is made on Principal, it sends the log record to Mirror and commits it immediately without waiting for any acknowledgment from Mirror. There may exist a lag between principal and mirror in writing log records to their disks. When there are a lot of transactions made on principal over a time, it may increase traffic over the netwrok between principal and mirror.

When there is any failure in principal, a forced manual failover should be done to mirror. As said above, if there is a delay in mirror writing all the log records received from principal then failover incurs the loss of that data and brings the mirror in available state. In this mode, Witness should not be configured as there will be issues regarding quorum. 

I'll write about impact of witness in both the modes in later posts of this series.

For introduction about Database Mirroring, read the official documentation of Microsoft,

http://msdn.microsoft.com/en-us/library/ms189852.aspx

The next post of this series is available here,

http://techie-friendly.blogspot.in/2014/08/database-mirroring-in-sql-server_17.html