SQL server 2016 – AlwaysOn basic availability group

Posted in: DBA Lounge, Microsoft SQL Server, Technical Track

BAG2Recently 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.

BAG6

 

 

 

 

 

 

 

 

 

 

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.

BAG4

 

 

 

 

 

 

 

BAG5

 

 

 

 

Discover more about our expertise in SQL Server.

email

Interested in working with Hemantgiri? Schedule a tech call.

About the Author

Data Platform Consultant
I am a Database Administrator by profession, and a student at a university called life by heart. I am passionate about SQL Server, photography, reading and sharing. Currently, I'm Data Platform Consultant @Pythian. I have been a Microsoft SQL Server MVP for four years, and a published author of the book - SQL Server 2008 High Availability. Keep in touch with me on twitter @hemantgirig

2 Comments. Leave new

i use mssql 2016 std, but wizard don’t show [check basic availability group] in Specify name.
why don’t i show that check boxes?

Reply

Hi
Can you create more than 3 basic groups? (i think the limit is 3, but hard to confirm without testing – the internet has no clarity on this)
Thanks Merlin

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *