Skip to content

Insight and analysis of technology and business strategy

SQL server 2016 - AlwaysOn basic availability group

date icon
Tags: ,

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.
Hemantgiri Goswami Photo Hemantgiri Goswami

"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"

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!
dba-cloud-services
Upcoming-Events-banner