How to Set Up Basic Availability Groups in SQL Server 2016
AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition. This new functionality will replace database mirroring, which is a deprecated feature. Basic Availability Groups are managed and created in the same way like the traditional Enterprise AlwaysOn Availability Group, but there are more limitations which are almost equally when you should run Database Mirroring on a Standard Edition. With this blog post I want to show how easy it is to create a Basic Availability Group and what limitations you can expect.
The prerequisites are the same as for a traditional Enterprise AlwaysOn Availability Group. You still need to Create a Windows Failover Cluster and Enable AlwaysOn Availability Groups on each instance. More information can be found on Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups
Setup a New Basic Availability Group
You can still use the “New Availability Group Wizard” in the management studio to create your basic Availability Group. Of course, T-SQL and PowerShell can also be used.
Specify the name of the Availability that you want to create.
Please also note the new option “Database level Health Detection”. You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This was not possible in previous editions of SQL Server.
Select the database that you want to add to the Availability Group. One of the limitations of Basic HA is that you’re only allowed to add 1 database per group. The wizard will return an error when you try to add more than 1 database into the group.
When you specify the replicas for your Availability Group, you will notice 2 other limitations:
- Limit of two replicas (primary and secondary). After you’ve added the secondary replica, the “Add Replica” button will automatically be disabled.
- No read access on secondary replica. The wizard only has the “No” option, so you can’t configure a readable secondary.
Please note that you can configure Synchronous or Asynchronous commit mode. This is a difference compared to database mirroring where you could only use synchronous commit mode in SQL Server Standard Edition. With Basic HA, we allow asynchronous commit mode, to be able to create your secondary replica in Azure. This allows you to create even a basic disaster recovery solution with SQL Server Standard Edition!
The endpoint configuration is still needed and there is no difference compared to the traditional Enterprise Availability Groups
Basic HA doesn’t allow you to use a readable secondary, which means that there are no backups possible on the secondary replica. You will notice that the Backup Preferences are completely disabled
Basic HA also supports the usage of an Availability Group Listener. Creating the listener is the same procedure as the traditional Enterprise listener. The Basic HA group is limited to 1 listener.
The next step is setting up the data synchronization between the 2 replicas. Again, there is no difference here between Standard and Enterprise Edition
With SQL Sever 2016, you could also use the new option Direct Seeding. This method will seed the secondary replica over the network. It does not require you to backup and restore a copy of the primary database on the replica. However, this is currently not available in the wizard but only with T-SQL.
After you finish the wizard, you have setup your first Basic HA Availability Group.