Depricated feature: Database Mirroring

You may come across legacy SQL Server boxes from 2005 onwards that are configured using database mirroring to provide a High Availability (HA) database for an application or website (BTW - this was written in 2019 and I still see SQL 2000 out in the wild!!). I would strongly recommend moving the Distaster Recovery (DR) strategy to use Availability Groups which replaced database mirroring in SQL 2012. Microsoft always claimed that mirroring would be unsupported in future versions of SQL Server, however SQL 2017 still has mirroring. I haven't checked SQL Server 2019 but be aware of this notice on Microsoft's very own database mirroring pages.

Microsoft deprication warning

High Availability is now achieved using Availability Groups which has many advantages over mirroring. One advantage is that the Availability Groups can be configured to contain multiple user databases - using database mirroring could only mirror a single user database within a database instance. A group of user databases that can be managed as a single unit is vital when dealing with more complicated applications.

Availability Groups also allows more than just one "secondary" replica - so you can distribute your systems in a more creative way and, if you have intensive read requirements on your databases for reporting and analysis, you can off-load the read-only work onto a secondary node. Be careful with licencing here because a "Basic Availability Group" in SQL Standard Edition will not allow you to read a secondary. That said, it was never possible to read the active secondary in the earlier database mirroring process so this is another useful feature of Availability Groups albeit with a potential licensing cost.

For old-style mirroring to work with automatic failover capabilities you required a "witness server" which was usually a SQL Express instance running on a third machine keeping an eye on the primary and secondary servers. Back in the day, because of the low requirements needed by the witness server, it was likely to be running on an old recycled server/PC ignoring both the protests of the DBA and the importance of the task the witness server was undertaking!

With the improvement of clustering technologies, since Windows Server 2008 the Availability groups leverages the Windows Server clustering capabilities and no longer requires the additional awkward witness server to allow automatic failover. The Primary node will ensure that the Secondary nodes are continually up to date if configured in synchronous mode and will failover automatically if the primary node encounters a problem. If you are a DBA that had experience of mirroring on SQL 2005 I can assure you that Availability Groups are a joy to work with!

You can easily run a few test scenarios using this Microsoft Lab. It will spin up 1 DC and 3 SQL VMs, details of the configuration are provided in the tutorial. Jump onto the DC which has SQL Server Management Studio installed and follow the tutorial or just start building clusters with the wizard (see below) and play with the failover capabilities.

Example clustering wizard

There is still a place for Log Shipping in DR solutions but it is not a "High Availability" solution, which is why Mirroring was introduced in SQL 2005. Similarly, some architectures are well served using Replication for scaling out and off-loading tasks, especially when crossing over geographic and network domains. However, it may be that a readable secondary node in an Availability Group fulfils all your requirements. You have lots of options - if you want more details here is an old, but still relevant, Microsoft SQL 2012 technical paper that gives a flavour of what is possible.

Below is a summary comparing the basics of the two technologies.

Solution Failover Notes
Availability Group with synchronous Secondary node Automatic No Data Loss on Failover
Availability Group with asynchronous Secondary node Manual Can be read.
Off-load read-only tasks
Database Mirroring synchronous Automatic (if witness available
otherwise Manual)
No Data Loss on Failover
Database Mirroring asynchronous Manual  

 

<< Go back to the previous page