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 of 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:

1. Supports Standard Edition and Higher.
2. You can create BAG for single database.
3. You can create multiple (At least two, while I am writing this post) BAG.

Limitations:

1. Can have only one database.
2. Limited to Primary and Secondary, meaning two replica only.
3. No readable Replica, that means NO to offloading reporting to a secondary replica,
4. Can not off-load backups as well.

Discover more about our expertise in SQL Server.