Basic Availability Groups: Affordable High Availability with Key Limitations
In SQL Server 2022, Basic Availability Groups provide a limited, cost-effective solution for high availability and disaster recovery in the Standard Edition. However, they have several limitations when compared to a standard Availability Group in the Enterprise Edition.
Although Basic AGs were introduced before SQL Server 2022, we’ll focus strictly on the latest version.
Let’s review the requirements and limitations.
Requirements and Limitations
SQL Server Edition and Version:
- Standard Edition: Both the primary and secondary replicas must be running SQL Server 2022 Standard Edition. Basic Availability Groups are unavailable in the Express, Web, or Enterprise editions.
- Same Version: Ideally, all replicas should be running the same build of SQL Server 2022.
Verify the version by running SELECT @@VERSION. In the screenshot below, you can see which update has been applied and the SQL Server version and edition.
Windows Server Failover Clustering (WSFC):
- Cluster Configuration: A Windows Server Failover Cluster must be configured. All nodes participating in the Basic Availability Group must be members of the same WSFC.
- Operating System: The servers must be running a Windows Server version that supports failover clustering (e.g., Windows Server 2019 or 2022 recommended).
- Domain Membership: All nodes must be members of the same Active Directory domain or trusted domains.
In our example, we have a two-node WSFC cluster using Windows Server 2022 Standard Edition.
Network Requirements:
- Connectivity: Reliable network connections between the replicas are essential.
- Name Resolution: A proper DNS setup for name resolution between nodes is preferred.
- Static IPs: It’s highly recommended all replicas utilize static IP addresses vs. DHCP-assigned IPs.
Database Requirements:
- Full Recovery Model: The database to be included in the Basic Availability Group must use the Full recovery model.
- Single Database Support: Basic Availability Groups support only one user database per group.
- Initialization: The database must be initialized on the secondary replica using a full backup and transaction log backups. Automatic seeding can be used as well.
Replica Limitations:
- Number of Replicas: Only two replicas are supported. One primary and one secondary.
- Secondary Replica Limitations
- Non-Readable: The secondary replica is not accessible for read operations.
- No backups: You cannot perform backups on the secondary replica.
- Synchronous Commit: To support automatic failover, Availability Mode must be set to synchronous commit. This is also a requirement for standard Availability Groups.
Licensing:
- Proper Licensing: Ensure that all SQL Server instances are properly licensed under Microsoft’s licensing terms for high-availability setups. See Microsoft’s licensing documentation for full details (SQL Server 2022—Pricing | Microsoft).
Permissions and Roles:
- Service Accounts: To reduce complexity, it’s recommended to use the same SQL Server service account on both replicas. Consider using group managed service accounts for further simplification.
- Firewall Settings: Configure Windows Firewall to allow communication between the replicas on the required ports (default is TCP port 5022).
Endpoint Configuration:
- Database Mirroring Endpoint: This must be configured on each replica for data synchronization. If using the wizard in SSMS, the endpoint is created and configured.
Quorum Configuration:
- Proper quorum configuration is essential to maintain cluster health and support automatic failover. This is done within the WSFC.
Summary of Limitations
- Single Database Limitation: Only one database can be included in a Basic Availability Group.
- Limited to Two Replicas: Only one primary and one secondary replica are allowed.
- No Read-Scale Capabilities: Unlike Availability Groups in Enterprise Edition, Basic Availability Groups do not support readable secondary replicas.
- No Backup Offloading: Backups cannot be performed on the secondary replica.
Conclusion
Basic Availability Groups can fill the gap if your environment doesn’t support a WSFC with the shared disk. However, due to their limitation of supporting only one database, ongoing management of multiple Basic AGs may not be ideal. For example, I’d still use a failover clustered instance if any of the following were true:
- SQL Server Standard Edition is used.
- Not spanning multiple data centers.
- The environment allows for the use of shared disks between machines.
Want to work with The SERO Group?
Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.