Step-by-step Configuration of SQL Server Read-Scale Always On Availability Group on Windows for Disaster Recovery

By: Edwin Sarmiento   |   Updated: 2021-07-01   |   Comments   |   Related: More > Availability Groups

Problem

We’ve been using SQL Server Database Mirroring for our database disaster recovery strategy. As we’re preparing to migrate to SQL Server 2019, we were told that we can no longer use Database Mirroring because it has been deprecated. What can we use to replace Database Mirroring for our disaster recovery strategy?

Solution

SQL Server 2016 introduced Basic Availability Groups as an alternative to Database Mirroring when running on Standard Edition. This tip walks you through the process of setting up and implementing SQL Server 2016 Basic Availability Groups. But if the goal is disaster recovery, the automatic failover with automatic client redirection feature that Basic Availability Groups provide would be unnecessary. This is where read-scale Availability Groups come in.

Read-scale Availability Groups were introduced in SQL Server 2017. While the goal of providing read-scale Availability Groups was to offload reporting and analytics workloads to read-only replicas, it’s a great alternative to replace Database Mirroring as a disaster recovery strategy.

NOTE: A common misconception is to deploy read-scale Availability Groups as a high availability strategy. Do not do this. Read-scale Availability Groups do not have failure detection and automatic failover capabilities because they do not leverage a cluster resource manager like the Windows Server Failover Cluster (WSFC) or Linux Pacemaker. The Microsoft documentation explicitly mentions this.

In the example provided below, two SQL Server instances will be used for the read-scale Availability Group. The primary replica (TDPRD031) is in the production data center while the secondary replica (TDDR031) is in the disaster recovery data center. The configuration is similar to the high-performance mode in Database Mirroring with the mirror server configured in asynchronous commit mode.

To configure read-scale Availability Groups as a disaster recovery strategy,

Enable the SQL Server Always On Availability Groups feature (on all replicas)

1. Open SQL Server Configuration Manager.
2. Select SQL Server Services, double-click the SQL Server (MSSQLSERVER) service to open the Properties dialog box.
3. Click the Always On Availability Groups tab and check the Enable Always On Availability Groups checkbox and click OK. This will prompt you to restart the SQL Server service.

There are several things to keep in mind in the Properties dialog box. In previous versions of SQL Server, this used to be the Always On High Availability tab. Since you can configure a read-scale Availability Group with no WSFC, it cannot be considered as a high availability solution. Also, you can check the Enable Always On Availability Groups checkbox even without a WSFC, something that you cannot do in previous versions.

Alternatively, you can use the Enable-SqlAlwaysOn PowerShell cmdlet to enable the Always On Availability Groups feature on all of the replicas. The -Force parameter will restart the SQL Server service without prompting for confirmation.

Enable-SqlAlwaysOn -ServerInstance INSTANCENAME -Force 

Configure read-scale Always On Availability Groups

Launch the New Availability Group Wizard in SQL Server Management Studio to create the read-scale Availability Group.

1. From within Object Explorer, expand the Always On High Availability node and the Availability Groups node
2. Right-click the Availability Group node and select New Availability Group Wizard. This opens the New Availability Group Wizard dialog box.

1. In the Specify Availability Group Options dialog box, type the name of the Availability Group in the Availability group name: textbox.
2. In the Cluster type: drop-down list, select NONE. Notice the values available in the list – EXTERNAL and NONE. SSMS detected that the operating system does not have a cluster resource manager available and did not provide the option Windows Server Failover Clustering. The following cluster types available are:
   • Windows Server Failover Cluster. The cluster resource manager for Windows Server operating systems
   • EXTERNAL. For Linux operating systems via integration with Pacemaker, a Linux cluster resource manager
   • NONE. Can be used for either Windows or Linux and does not provide support for high availability

1. In the Select Databases dialog box, select the databases that you want to include in your read-scale Availability Group. Click Next.

1. In the Specify Replicas dialog box,
   • In the Replicas tab, click the Add Replica button to add the SQL Server instances that you want to configure as replicas.

Notice the Failover Mode column. Since you do not have a cluster resource manager, you will not be able to change this option. The only Failover Mode available is Manual.

Since the goal is disaster recovery, the Availability Mode is set to Asynchronous commit. Also, there is no need to configure a listener name. You would need the listener name if the goal is to offload reporting and analytics workloads to read-only replicas. I will cover how to configure the listener name for read-scale Availability Groups in a future tip.

• In the Endpoints tab, review the Availability Group endpoints and make sure that the replicas can communicate with each other via the endpoints.

1. In the Select Initial Data Synchronization dialog box, you can choose to either use the new Automatic Seeding feature, the usual Full database and log backup option, Join only option, or Skip the initial data synchronization option. This example uses Automatic Seeding since the databases are relatively small. Refer to this tip for more information about Automatic Seeding. Click Next.

1. In the Validation dialog box, make sure that all checks return successful results. Ignore the warning regarding the listener configuration since it is not being configured. Click Next.

1. In the Summary dialog box, verify that all the configuration settings are correct. Click Finish to create the read-scale Availability Group.

1. Click Close after the wizard completes the creation of the read-scale Availability Group.

You can view the Availability Group dashboard for the state and configuration information.

As this is a disaster recovery solution, you need to properly plan for failing over to the secondary replica. In a future tip, I will cover how to perform maintenance on a read-scale Availability Group like installing updates and performing a failover in case the primary replica becomes unavailable.

Next Steps

About the author