- Posted by redglue
- On March 6, 2017
- 0 Comments
- availability groups, basig availability groups, high availability, sql server, standard edition, upgrade
A good news came along with SQL Server 2016: finally we have the famous AlwaysOn Availability Groups (AG) working on Standard edition! But life is not a bed of roses… and obviously AG in a lower edition then Enterprise would bring some drawbacks.
Now the AlwaysOn Availability Groups feature is split into two different “categories”:
- Basic Availability Groups (BAG): The newborn that came with SQL Server 2016 and is support only in the Standard Edition.
- Advanced Availability Groups (AAG): The one the we have since SQL Server 2012, but now even more powerful. AAG is only supported in the Enterprise edition of SQL Server.
Comparing AAG and BAG
While AAG is a complete High Availability (HA), Disaster Recovery (DR) and Load Balancing (LB) feature, the BAG is a kind of revamped Database Mirroring (DBM), bringing some limitations:
- Only two replicas are allowed.
- The secondary replica cannot be opened for reading.
- Only one database per group is allowed.
- We need to configure a Windows Server Failover Cluster in order to be be able to configure it.
This list may discourage anyone… Blindly looking to all those limitations we could easily stick on Database Mirroring, but…
The dead of Database Mirroring is close
Yes, that’s true. This didn’t happen before, because there were no similar offer to replace the DBM, but now we have…
Knowing this, what kind of advantage do we have in migrate to the BAG? This is simple: the improved underlying engine. The main pillars of the AlwaysOn Availability Groups are the “older” DBM and the Windows Failover Cluster., and both were improved. Focusing on the Availability Groups, we have an improved transport for the log records, optimization for distributed AGs, a revamped Redo Thread, etc… In fact, the BAG is not as easy to configure as the Database Mirroring was, but it brings good advantages.
How can I upgrade from Basic Availability Groups to Advanced Availability Groups
So far, there’s no direct method to migrate from a BAG to an AAG. First thing to take into account is that a BAG is only available in a SQL Server Standard Edition (not in the Enterprise) and an AAG is only available in the Enterprise edition. This said, here are the overall steps to move from a BAG to an AAG:
- Temporarily disable the backups for the targeted database.
- Destroy the current group (BAG).
- Please, do not drop the database!! 🙂
- Leave the secondary database as is.
- Upgrade SQL Server from Standard to Enterprise edition.
- Create another group involving the same replicas/database and choose de option “Join Only” in the synchronization preference.
- Complete the wizard and the AAG will be created.
Currently, this is the only way to upgrade from a BAG to an AAG. As Enterprise edition does not support BAGs, we need to destroy the group before an edition upgrade and then re-create the AG.