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) HADR feature 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).
  • The storage at both ends can be different (availability groups are storage/vendor agnostic).
  • 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 (but you can snapshot the primary database with SP1, or log ship it to somewhere else – you cannot however snapshot the secondary database, even with SP2).
  • When deploying multiple BAGs you can balance database workloads across the two replicas (ie. Active/Active) – this may require additional SQL Server licensing.
  • You must copy any logins required for the primary replica db to the secondary replica (just like you need to for AAG) – this can be a scheduled/automated task with the right script.

So, it’s mostly good news. If you want to provide a HADR solution for say 12 databases across 2 data centres (especially data centres that do not have shared storage with cross-site 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/stretched vLAN) or 24 IP addresses if using a multi-subnet network configuration. 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 as your application will only be able to access one database (unless you either deploy MSDTC or forgo connecting to the BAG Listener and connect directly to the primary server – in which case you’ll lose HADR capability and application connection recovery in the event of a failover).

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? (Oct 2018 update: as of SQL Server 2019 CTP Microsoft have still not removed the Database Mirroring feature).

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/destination 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. Or you could potentially replicate the mirrored database from/to the same server and have real-time data access without the frequent downtime caused by the snapshot/restore mechanism. And like BAGs, Database Mirroring allows you to have an automated failover process to a fully synchronised database too (requires a witness server), so no difference there (except you need a 3rd server, which incurs more Windows Server and SQL Server licensing expense).

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 manual/scheduled attach process.

So, at the moment, BAGs can be a replacement for the trusted Database Mirroring facility, but it depends on your requirements.

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/destination 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 (typically requiring a significant expense, depending on your Enterprise Agreement with Microsoft – if you have one).


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