SQL Server Database Mirroring (Not Dead Yet)

SQL Server Database Mirroring is a high availability and disaster recovery feature that was added to the product with SQL Server 2005, and enabled without a trace flag in SQL Server 2005 SP1. It was enhanced in SQL Server 2008 with features like log stream compression and automatic page repair, and it is still fully supported in SQL Server 2012, even though SQL Server 2012 has the new AlwaysOn Availability Groups feature. I have had real life experience with database mirroring in mission critical environments for nearly six years. My experiences have been mostly good over the years, and DBM has saved my bacon on more than one occasion. It is far from perfect though, and I have learned a few tips and tricks over the years, through painful experience.

If you have the opportunity to upgrade to SQL Server 2012, you will want to very seriously seriously consider using AlwaysOn instead of database mirroring because of its many advantages. AlwaysOn is a much more flexible and powerful feature than database mirroring, that you can take advantage of with SQL Server 2012. If you are going to be using older versions of SQL Server for a while, database mirroring is still a very valuable tool as part of your overall HA/DR strategy. Keep in mind that database mirroring works at the database level, not the instance level. You have to mirror each user database separately, and you cannot mirror system databases. Each database can only be mirrored to one location, and the mirror copy is not accessible to your applications unless you take database snapshots of the mirror for read-only usage (which is pretty hard to manage in real life).  Your mirrored databases must use the Full recovery model at all times, and you have to be careful with DML and DDL operations that generate lots of transaction log activity (such as index builds or index maintenance) so that you don’t create a huge send queue on the principal or redo queue on the mirror.

You also have to take care of your SQL Server logins and SQL Server Agent jobs on both sides of the mirroring partnership. Finally, you have to keep in mind that there is no guarantee (out of the box) that all of your mirrored databases will failover together in the event of an automatic failover (unless the SQL Server Service is stopped on the principal). I have come up with some techniques over the years that help make database mirroring a somewhat better solution than it is straight out of the box, which I will be sharing in future posts.

Synchronous database mirroring (also known as high-safety mode) uses a two phase commit model for all write operations. A write operation is first written to the transaction log file on the principal copy of the database. Then the information for that transaction is read from the transaction log file on the principal, compressed (in SQL Server 2008 and above), and then sent over the network to the redo queue on the mirror server. Then it is written to the transaction log file on the mirror copy of the database. Finally, an acknowledgement of this is sent to the application that generated the write operation in the first place. All of this is necessary to guarantee that the two copies of the database are always 100% synchronized, and that no data loss is possible in the event of a database failover. It is also necessary to enable automatic failover, which also requires a separate witness instance.

Asynchronous  database mirroring (also known as high-performance mode) simply records all write operations to the transaction log of the principal copy of the database, and acknowledges the completion to the application that generated the write operation. Then, immediately afterwards, the information for that transaction is read from the transaction log file on the principal, compressed (in SQL Server 2008 and above), and then sent over the network to the redo queue on the mirror server. Then it is written to the transaction log file on the mirror copy of the database. In most cases (unless you are having network problems or I/O problems on the mirror), the databases will always be synchronized, but there is no guarantee of this. It is possible to have a send queue on the principal or a redo queue on mirror that could hold transactions that have been committed on the principal, but not yet committed on the mirror.  You must have SQL Server Enterprise Edition in order to use asynchronous database mirroring.

Because of the basic design and differences in how they work, it is impossible for synchronous database mirroring to be 100% as fast as asynchronous database mirroring for write operations. The performance penalty should only be a few milliseconds, depending on things like the network latency between the principal and mirror servers, and I/O latency on the log drive of the mirror server. With synchronous database mirroring, the geographical distance between the principal and mirror will affect your network latency. Read operation performance is not affected by database mirroring.

At any rate, I will be talking more about database mirroring in the near future!

This entry was posted in Microsoft, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and tagged . Bookmark the permalink.

6 Responses to SQL Server Database Mirroring (Not Dead Yet)

  1. Brian R says:

    You neglected to point out that in synchronous mirroring, problems on the mirror become problems on your principal. The write performance you get is equal to the worse of the two, minus a bit for communications overhead.

    I imagine that over the long term that’s true of asynchronous mirroring too, but there you at least have a buffer that can smooth out transient problems or overloads on the mirror.

    I only mention it because I spent a few weeks cursing this fact around the beginning of this year.

  2. phobosq says:

    Two more points:
    1. In high availability scenario with automatic failover, it is commonly believed that it’s witness who decides upon failover, but it’s a partner who agrees it with a witness.
    2. Additional thing to remember – apart from jobs and logins – are Database Mail profiles.

  3. Pingback: Something for the Weekend – SQL Server Links 09/03/12

  4. Pingback: Database mirroring pseudorandom ramblings « Yet another SQL Server DBA…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s