What are SQL Server 2016 Basic Availability Groups Restrictions
With the release of SQL Server Always on Availability groups in 2012 Enterprise Edition, it became the De facto standard for High Availability and Disaster Recover Solutions. The reason why it was so successful was because of the flexibility that the solution provides. Scale-out reads, DR integration to an Azure VM, backups on secondary etc. As much as it is successful, its main limitation was its Enterprise Edition only feature restriction. Most SQL Standard deployments still relied on the older methods like Database Mirroring, Log shipping, 2 Node Clustering etc. That is something that has changed with SQL Server 2016. SQL 2016 introduces the basic availability groups. In this post, I am going to talk about what are SQL Server 2016 basic availability groups restrictions.
Basic Availability Groups Restrictions in SQL Standard Edition
- First, You can only have one secondary Non-Readable replica. Which means, you cannot do read-only queries on the secondary or take backups.
- Only versions running SQL Server 2016 RTM is supported. CTP 3.0 is also supported for non-production use.
- You will not be able to Add-Remove replicas from a basic Availability group. You will need to rebuild the Availability group completely.
- Only 1 Database per availability group is supported. You can have multiple availability groups, subject to system resource restrictions.
- There are no direct migration paths to advanced availability groups in Enterprise Edition. You will need to break it up and rebuilt it manually.
Benefits of Basic Availability Groups compared to Database Mirroring
- Both synchronous and asynchronous methods of database replication are now supported. In Database Mirroring, only synchronous with high safety is supported. This restricted DR planning over high latency WAN links.
- You can now have an asynchronous replica on an Azure IAAS VM. This enables you to have a DR site in a cost effective way.Note: You will need Software Assurance for License mobility rights.
- No more need for “Transparent Client Redirect” or Failover partner name to be mentioned in the connection string like in Database Mirroring. Using a listener, you can create a “virtual name” to which client applications can talk to. This enables a wider range of software applications which does not natively support database mirroring.
Conclusion
Database mirroring is planned to be deprecated in its future releases. For any new production deployment planning, basic availability groups need to be considered over Database mirroring for its benefits.
Here is the Microsoft Official documentation for your reading pleasure.