Is SQL 2016 Basic Availability Groups a replacement for Database Mirroring?

Share with your social network

In my opinion, the quick answer is “not quite” – not even with the release of SQL 2016 SP1.

Let me explain …

SQL Server 2016 was released to manufacture in June 2016. With it came the highly anticipated Basic Availability Group (BAG) in the Standard Edition, with ‘regular’ availability groups (first found in SQL 2012) being renamed to Advanced Availability Groups (AAG) – which still requires Enterprise Edition.

SQL Version SQL Edition Feature Max Replicas
2012 Enterprise Edition AlwaysOn Availability Groups 5 (1 Primary, 4 Secondary, 3 can be in ‘Sync Commit’ mode)
2014 Enterprise Edition AlwaysOn Availability Groups 9 (1 Primary, 8 Secondary, 3 can be in ‘Sync Commit’ mode)
2016 Enterprise Edition Advanced Availability Groups 9 (1 Primary, 8 Secondary, 3 can be in ‘Sync Commit’ mode)
2016 Standard Edition Basic Availability Groups 2 (1 Primary, 1 Secondary, 2 can be in ‘Sync Commit’ mode)

By way of a quick reminder, this is what you can and cannot do with Standard Edition BAGs:

  • Requires a 2-node Windows Server Failover Cluster (WSFC)
  • You are restricted to two replicas (primary & secondary)
  • You do not need shared storage (so a BAG can span 2 data centres if you like)
  • Primary and Secondary replicas can exist in different domains
  • You can create a virtually unlimited number of BAGs
  • You can only have one database per BAG
  • You can set the secondary up as a sync or async replica (dependent on your bandwidth and latency)
  • You can automatically (or manually) fail over to the secondary
  • The secondary database is not readable
  • You cannot snapshot the secondary database (even with SP1)
  • You can snapshot the primary database (with SP1)
  • When deploying multiple BAGs you can balance database workloads across the two replicas (ie. Active/Active)
  • You must copy any logins required for the primary replica db to the secondary replica (just like you need to for AAG)

So, it’s mostly good news. If you want to provide a HADR solution for say 12 databases across 2 data centres (that do not have shared storage with block synchronisation), then it can be done quite easily (you’ll need 12x BAGs). Of course you’re going to need 12 IP addresses (one per BAG listener if using a vLAN). Beware however … if an off-the-shelf application can only use one connection string to access several databases, then a BAG deployment will not work for you.

Basic Availability Groups is, I believe, Microsoft’s first step toward replacing the Database Mirroring function as noted by Microsoft in their SQL 2012 online library While they stated “This feature will be removed in a future version of Microsoft SQL Server”, Database Mirroring survived SQL 2014, and is still alive and fully functional in SQL 2016 too. So it ain’t dead yet – perhaps SQL 2017 will sound the death toll?

BAGS are, in my opinion, not a complete functional replacement for Database Mirroring just yet (but they do come very close). Why? Because with mirroring you can at least snapshot the database on the mirror server (requires SQL 2016 SE SP1), thereby giving yourself a read-only database away from the primary replica/workload to serve other applications (eg. report generator). Set up a scheduled job and you could take regular snapshots of the mirrored database – sure, you’re not getting a real-time data flow with your snapshot, but it is still a useful ability to have. And like BAGs, Database Mirroring allows you to have an automated failover process to a fully synchronised database too, so no difference there.

Yes, SQL 2016 SP1 gives us the ability to perform snapshots within a BAG (whereas with the RTM release you could not), but the snapshot can only be done on the primary replica – you still cannot take a snapshot of the secondary database in an effort to get around the lack of a read-only secondary db as provided by AAGs in the Enterprise Edition of the product. Of course, if you’re seeking to have an offline db for say, reporting, then sure, take a snapshot of the db on the primary replica and copy it to a network share (away from the cluster) for access by the reporting application. If your primary replica is heavily loaded or has large databases, then it may not perform optimally during the snapshot-and-copy-to-network-share activity. While snapshotting is useful, it still does not give us an immediately available online db like an AAG does. If you’re snapshotting a db every hour say, then you still have to attach the db every hour, which will cause a small outage window to any application trying to connect to it during the attach process.

So, at the moment, BAGs is not a replacement for the trusted Database Mirroring facility IMHO. Give me a readable secondary (or ability to snapshot it at least) and I’ll be a happy chappy. If Microsoft removes the Database Mirroring feature then we’ll all have no alternative but to use BAGs (or FCI) for our HA strategies.

Quick Summary – SQL 20126 Standard Edition SP1 – DB Mirror vs BAG
* BAG supports sync and async connection flows
* DB Mirroring supports sync (high safety) connection flow only (need EE for async)
* BAG and DB Mirroring both support automated failover to a sync db (RPO=0)
* You can only mirror a single db (but you can have multiple mirrors)
* You can only BAG a single db (but you can have multiple BAGs)
The only advantage a mirror has over a db is the fact that you can snapshot the mirror db – you cannot snapshot the secondary db in a BAG. However, a BAG does allow an async replica (great for DR without loading down the primary db), whereas Database Mirroring will only support async data movement under Enterprise Edition.

Cheers,
Mark

PS/ Microsoft released Service Pack 1 (SP1) in November 2016, and with it came a whole host of goodies. In particular, a number of functions only found in the more expensive Enterprise Edition made their way into Standard Edition (view complete list of SQL 2016 SP1 changes).

PPS/ Microsoft released SP1 CU1 on 18 January 2017 (build number 13.0.4411.0) and it contains 56 important updates (view complete list of SQL 2016 SP1 CU1 updates).

Leave a Reply