Set Up AlwaysOn Basic Availability Groups in SQL Server 2016 in Standard Edition OR SQL Server Mirroring in SQL Server 2016 in Standard Edition .

 

For Always on Perrequisite Refer Document

Prerequisite For AlwaysOn Basic Availability Groups in SQL Server 2016 in Standard Edition OR SQL Server Mirroring in SQL Server 2016 in Standard Edition .

 

Specify the name of the Availability that you want to create.

Please also note the new option “Database level Health Detection”. In SQL SERVER 2016 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.

Rajeev Shastri
Author: Rajeev Shastri

Database Administrator With experience on SQL SERVER,Oracle and NOSQL DB

Leave a Reply

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