Contact us

Madeira Data Solutions

An island of calm for your business 

Database Mirroring Isn’t Going Anywhere

Written By: Matan Yungman 24/09/2015

AlwaysOn Availability Groups is a great technology that centralizes the management of High Availability, Disaster Recovery and Scale-Out. However, in many cases, the good old Database Mirroring is just good enough (and in some cases, better):

Database Mirroring

Database Mirroring can work in Standard Edition

Database Mirroring is supported in both Enterprise Edition (asynchronous + synchronous modes) and Standard Edition (synchronous mode). Up till SQL Server 2016, Availability Groups is supported only in Enterprise Edition. Starting SQL Server 2016, Availability Groups is supported in Standard Edition, but with a limited functionality – one secondary, one database per Availability Group, and that database is not readable, unless you take a snapshot of it.

Database Mirroring can work without a domain

Because AlwaysOn Availability Groups works on top of Windows Cluster, it requires working in a domain. Database Mirroring, however, allows working in domain-less environments using certificates, in addition to the option of working in a domain.

Update:

It was pointed out to me that starting Windows Server 2016 and SQL Server 2016, non-domain Availability Groups are supported. Mirroring is still easier ad supports more platforms, but definitely a good addition to the product!

Database Mirroring requires less IP addresses

With Mirroring, you need an IP address for the Principal, for the mirror, optionally for the Witness, and you’re good to go. With Availability Groups, you need an IP address for the each one of your replicas, for the cluster, and optionally for the cluster witnesses and Availability Group Listeners.

You don’t need to configure DNS records for proper reconnect after a failover

When working in a multi-subnet environment, it’s tricky to get Availability Groups to work smoothly after a failover. You need to understand what the RegisterAllProvidersIP, MultiSubnetFailover and HostRecordTTL options mean, and how to change them in the application and the cluster configuration (more in-depth information about those here).

With Database Mirroring, you need to know how to configure a failover partner in the connection string, and in most cases you’re done.

5 responses to “Database Mirroring Isn’t Going Anywhere”

  1. pmpjr says:

    2016 brings AG to standard edition even if it’s a bit nerfed)
    2016 brings non domain AG (@sqlha blog)
    Less IPs, valid point. Does it really matter though?
    Can’t argue 4th point.

    • Matan Yungman says:

      Thanks for the comment.
      Didn’t know about non domain AG. Great to see that!

      However, the configuration is still not that trivial and requires more time and effort than mirroring.
      I will update the post. Thanks again for pointing this out.

  2. Allan Hirt says:

    You do not need an IP address for a witness. Not sure where you are getting that. If you are using a file share witness as part of the quorum mechanism, sure at some point the underlying file server will be using IP under the covers but there are other witness types depending on what you are doing. WIndows Server 2016 also supports cloud witness.

    With the introduction of the domainless clusters, this gives you the DBM scenario with certificates and I would expect that not in 2016, but soon thereafter DBM will most likely go away. It may be vNext post-2016 … time will tell.

  3. Simon Liew says:

    I reckon I should mention the fact that Microsoft has announced database mirroring deprecation on SQL 2012. Good old database mirroring works as gold, but at some point in time it will disappear completely.

Leave a Reply

Your email address will not be published. Required fields are marked *