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

By:   |   Updated: 2021-07-01   |   Comments (11)   |   Related: > 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.

important note

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.
sql server configuration manager

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.
new availability group wizard
  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
availability group wizard
  1. In the Select Databases dialog box, select the databases that you want to include in your read-scale Availability Group. Click Next.
availability group wizard
  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.

availability group wizard

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.
availability group wizard
  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.
availability group wizard
  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.
availability group wizard
  1. In the Summary dialog box, verify that all the configuration settings are correct. Click Finish to create the read-scale Availability Group.
availability group wizard
  1. Click Close after the wizard completes the creation of the read-scale Availability Group.
availability group wizard

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

availability group dashboard

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-07-01

Comments For This Article




Friday, March 29, 2024 - 5:45:53 PM - Edwin M Sarmiento Back To Top (92137)
KL,

Refer this this tip for additional information

Configure a Read-Scale Always On Availability Group on Windows for Offloading Reporting and Analytical Workloads

https://www.mssqltips.com/sqlservertip/6925/read-scale-always-on-availability-group-to-offloading-reporting/

Friday, March 29, 2024 - 5:44:12 PM - Edwin M Sarmiento Back To Top (92136)
KL,

As mentioned in Step #6

"You would need the listener name if the goal is to offload reporting and analytics workloads to read-only replicas..."

Wednesday, March 27, 2024 - 4:33:57 PM - KL Back To Top (92132)
Hi..we want to use the secondary instance as a reporting DB...so read only.

Do we need a listner ?...we dont care about DR...we just want to reports to point to the secondary instance that is being updated via AG without a cluster

Thursday, June 23, 2022 - 5:55:31 PM - Edwin M Sarmiento Back To Top (90192)
Rajendiran,

I don't understand your question. This tip does not have anything that tell you to use certificates

Thursday, June 23, 2022 - 6:40:41 AM - Rajendiran Murugesan Back To Top (90188)
Hello Everyone,
I have one quick query to Configure on MS SQL server Read-Scale Availability Always ON Group .
without CREATE A MASTER KEY AND CERTIFICATES can we configure it ? I could not see any Step to do it .
Please confirm it .

Friday, January 14, 2022 - 3:38:40 PM - Edwin M Sarmiento Back To Top (89666)
Larry,

On Standard Edition, only Basic Availability Group is supported as described here
https://www.mssqltips.com/sqlservertip/4980/setup-and-implement-sql-server-2016-always-on-basic-availability-groups/

Friday, January 14, 2022 - 10:50:49 AM - Larry Back To Top (89664)
We are running the standard edition. We are hoping to have the primary server replicate the databases over to the secondary server. While we realize this is not a full failover HA solution in the event of hardware failure on the primary we would switch to the secondary with minimal transaction loss.

Thursday, January 13, 2022 - 6:47:56 PM - Edwin M Sarmiento Back To Top (89659)
Larry,

What edition of SQL Server 2019 are you running? And what goal are you trying to accomplish?

Thursday, January 13, 2022 - 12:22:33 PM - Larry Back To Top (89658)
When I get to the part of specifying the replicas and add the second server the only availability mode that is allowed is Configuration only which does not include data. Looking at the Microsoft documentation it appears the only way around this is a cluster. This is SQL Server 2019 in case things might have changed.

Hoping there is a solution as a cluster is serious overkill for our needs.

Monday, August 9, 2021 - 7:21:34 PM - bass_player Back To Top (89107)
Cody,

That's great to hear. This is how important knowing and understanding the real goal is. Focus on the goal, not on the tool.

Friday, July 30, 2021 - 11:06:46 AM - Cody Back To Top (89075)
We use read-scale AGs for our DR, and it works great. We don't need an AG for HA or automatic failover purposes. It will always be performed by someone kicking off a Runbook for a planned or unplanned failover. It works perfectly for our use case.














get free sql tips
agree to terms