SQL server 2016 - AlwaysOn basic availability group
Recently I was playing with the SQL Server 2016 CTP 2 and noticed that there was a new feature introduced - Basic Availability Group (BAG) for AlwaysOn. This feature is supported in SQL Server 2016 Standard or higher edition. With this feature we can create a fail-over solution for a single database, that means, if you want to add one or more database(s) it is not permitted. While you are doing a setup using the Basic Availability Group you will notice that you do not see an option that says "Readable Secondary" to "Yes"; this is because of the fact that Basic Availability Group replaces the Database Mirroring (DBM). And that means the secondary database can not be used to off-load the reporting traffic unlike you do in the SQL Server 2012 Enterprise edition.
This made me wonder. Why had MS made an improvement in the High Availability feature by adding
BAG when it was already there? I re-read the feature lists and I found my answer -
Standard edition
SQL Server 2012 does not have support for AlwaysOn Availability groups. Thus MS has added a support in the SQL Server 2016, and, it now allows a Single Database per Basic Availability Group. This also means that you can create multiple Basic Availability Groups, as per requirement. I can say this, because at the time of writing this I could create at least two such groups. Though, at this moment, I am unaware of how many such groups can be created. To conclude, let me list the features and limitations for the Basic Availability Group (BAG) that I can think of at this moment.
Features:
- Supports Standard Edition and Higher.
- You can create BAG for single database.
- You can create multiple (At least two, while I am writing this post) BAG.
Limitations:
- Can have only one database.
- Limited to Primary and Secondary, meaning two replica only.
- No readable Replica, that means NO to offloading reporting to a secondary replica,
- Can not off-load backups as well.
SQL Server Database Consulting Services
Ready to optimize your SQL Server Database for the future?
Share this
Share this
More resources
Learn more about Pythian by reading the following blogs and articles.
How to build a multi-domain cluster with AlwaysOn availability groups - SQL Server 2016
SQL server:opening errorlog:Failed to retrieve data for this request

MSDTC and configuration details for cluster/Always On
Ready to unlock value from your data?
With Pythian, you can accomplish your data transformation goals and more.