SQL Server Standard: understanding Basic Availability Groups (BAG)
Categories:
Basic Availability Groups (BAG) give SQL Server Standard edition users high availability similar to the Always On AG of the Enterprise edition, but with significant limitations:
- One group per instance.
- One database per group.
- No reads on the secondary replica.
- No backups on the secondary.
- Two nodes maximum, primary and secondary.
For a long time, using Always On Availability Groups required an Enterprise licence, leaving Standard edition users with Database Mirroring. Since SQL Server 2016, Microsoft has offered Basic Availability Groups (BAG) as the replacement for mirroring in the Standard edition.
What is a Basic Availability Group?
A Basic Availability Group is a cut-down version of an Always On Availability Group. It replicates a database from one SQL Server instance to another, providing failover in case of a software or hardware failure.
Like the full version, it relies on Windows Server Failover Clustering (WSFC), but it is deliberately constrained to fit the limitations of the Standard edition.
The limitations
This is where it hurts. To avoid cannibalising the Enterprise edition, Microsoft imposed significant restrictions.
One database per group
This is the most constraining limit. In a Standard group you can only put one database. If your application uses three interdependent databases, you have to create three separate BAGs.
Two nodes maximum
A BAG supports only two replicas, one primary and one secondary. You cannot add a third node for disaster recovery in another region.
No reads on the secondary
The secondary replica is passive. You cannot use it to offload your Power BI reports or your backups.
No backups on the secondary
Unlike full availability groups, you cannot run your backups — full, log or differential — on the secondary node to spare the primary’s resources.
Basic AG vs Enterprise AG
| Feature | Basic AG (Standard) | Always On AG (Enterprise) |
|---|---|---|
| Databases per group | 1 | Unlimited |
| Number of replicas | 2 (1 P + 1 S) | Up to 9 |
| Readable secondary | No | Yes |
| Backups on the secondary | No | Yes |
| ADFS / GMSA support | Yes | Yes |
Managing failover with BAGs
As we have seen, the Standard edition forces one availability group per database. If you have a great many databases on your server and want them all in availability groups, that leaves you with a great many groups to manage.
Create a single listener, on one availability group, and connect all your applications to that listener.
When one group fails over, every group has to fail over at the same time, so that the listener follows and all the databases end up active on the same node.
The risk is a partial split brain where, after an incident, database A fails over to node 2 while database B stays on node 1. Your application, caught between two servers, stops working.
Manual failover
For planned maintenance, do not fail the databases over one by one through the SSMS graphical interface. Use PowerShell with the dbatools module, the reference toolkit for SQL Server administrators.
One command line fails every group over from one node to the other, cleanly:
# Fail every BAG over from node 1 to node 2
Get-DbaAvailabilityGroup -SqlInstance "SQL-NODE-01" |
Invoke-DbaAgFailover -TargetReplica "SQL-NODE-02"