Database Mirroring 2008 style

Database Mirroring was introduced in SQL Server 2005. It was one of the main features that excited people when reading about the new release. Here was a capability for synchronizing a database across two servers providing automatic failover that did not need the Windows Cluster Service nor the expensive hardware and software that goes along with it. Yes, the “poor person’s” Failover Cluster at the database level. This feature has been enhanced in SQL Server 2008.

Unfortunately, because of issues uncovered by customers late in the Yukon Beta program, the feature was deemed to be “unsupported” when the RTM version was finally released Nov 7th 2005. The only way to test out Database Mirroring at that time was to set a Trace Flag (1400) at SQL Server startup but Microsoft clearly stated that it was not supported for production systems. We used that Trace Flag in our training that we delivered just-in-time the day the product was released. However, we had to wait on Service Pack 1 before we could officially setup a Database Mirroring session and have it “supported”. The automatic failover feature was provided by a third SQL Server instance, in addition to the Principal and the Mirror, which was called the Witness. When the Principal was having trouble, the Witness would initiate a Failover and the Mirror would become the new Principal. The old Principal would then become the new Mirror when it came back up again and resynchronized. There was even a Transparent Client Redirection feature using the SQL Native Client and ADO.NET 2.0. The connection string would allow the entry of two servers. If the Primary server was not available, the client would try the Secondary server and not skip a beat. This operating mode is named “High Safety with Automatic Failover” or “synchronous”. The key characteristic of this mode is that the synchronous processing would require distributed transactions between Principal and Mirror which guarantees no loss of data during a failover. The downside of this strategy is the potential performance bottlenecks as the Principal is waiting on the Mirror before the databases can commit in unison (we used to refer to this as the ”two phase commit”). As an alternative, Microsoft also gives us “High Performance” or “asynchronous” mode. This removes the performance overhead of distributed transactions but exposes the potential loss of the latest transactions that committed on the Principal but never made it to the Mirror before a failover. This mode also gives up the automatic failover feature but manual failover is still available. The one disappointing aspect of the Database Mirroring architecture was that the Mirror was not usable as a database until failover. So it’s great for data protection but not for load-balancing. True, you could create a database snapshot of the Mirror database but that would be a point-in-time read-only copy. If you are after load-balancing you might look at Replication or Log Shipping as alternative solutions but you would be giving up the automatic failover.
I have tested out Database Mirroring in SQL Server 2008, and on the surface it works much like SQL Server 2005 SP1. The user interface is almost identical. The pros and cons are still the same. However, “under the hood” SQL Server works much more efficiently to try and negate those potential performance issues. There is now built-in compression of stream data, write-ahead processing for log records being sent to the Mirror, improved use of log send buffers on the Principal, and Page read-ahead processing during the undo phase of a failover. There is also an automatic recovery feature where corrupted data pages are repaired by the system by requesting a fresh copy from the partner whenever a data page becomes unreadable. All this adds up to a more robust and usable Database Mirroring feature.

I usually advise caution with major new features as they take time to reach maturity (i.e. fix bugs/issues) and Database Mirroring was definitely in that category, but now with Katmai, it may be the time to re-evaluate. Of course, some heavy duty integration testing and performance benchmarks will give some answers.

After all, it’s included in our SQL Server licensing so why not give it a whirl?
Cheers
Brian