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!