Rajendra Gupta
Distributed SQL Server Always On Availability Groups

An overview of distributed SQL Server Always On Availability Groups

August 21, 2020 by

This is the 12th article in the series of SQL Server Always On Availability Groups.

Introduction

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.

  • A failover cluster configuration
  • Availability replicas should be part of the same failover configuration
  • We should configure a SQL listener that always connects to the primary replica

This article explores the concept of the Distributed Availability Group and configures it for the demonstration purpose

SQL Server Distributed Availability Group

Suppose we have two independent failover clusters in your infrastructure. These clusters are configured with two separate SQL Server Always On availability groups. Suppose one of the clusters is in your primary site and the second cluster is in the disaster recovery site. SQL Server distributed availability group provides a solution to configure the availability groups between these clusters.

In a traditional SQL Server Always On Availability Groups, all replicas should be part of the failover cluster. We can implement a disaster recovery solution in a traditional AG, but you need to configure the DR server in the same failover cluster. You can configure an asynchronous data commit for the DR Availability group. It requires a geographically stretched failover cluster, as shown below. You need to do complex networking, firewall configurations. It also requires additional configurations such as CrossSubnetDelay, CrossSubnetThreshold using Windows PowerShell.

SQL Server Always On Availability Groups representation

You can add multiple secondary replicas that receive the data from the primary SQL replica located in the leading site. In the below image, we see two DR nodes receiving asynchronous data from the primary replica. These four nodes are part of a single geographical cluster.

Single WFSC across multiple data centres

Now, suppose you have two separate sites, and you do not want to create a stretched cluster. Both clusters are independent of each other. You can configure the distributed availability group where the primary cluster is in the DC and secondary cluster in the DR.

As shown below, we create a distributed SQL Server Availability group with below configurations:

  • You have a failover cluster in your primary site, and it has a synchronous replica between the two nodes. You configured a SQL listener to point applications to the primary replica
  • You have another failover cluster in the DR site, and it also has synchronous data commit replica in its two nodes. You also configured another SQL listener in the DR cluster
  • We created a distributed AG that connects the listener of both failover clusters hosted in the DC and DR site
  • You can configure both synchronous or asynchronous data commit for a distributed availability group. In the synchronous data commit, primary replica waits for an acknowledgement from the secondary replica to commit the transaction. In case you prefer to configure the synchronous replica, you should consider the network bandwidth and transaction workload to avoid any impact on the application connecting to the primary replica in the DC site

Distributed SQL Server Always On Availability Groups

Benefits of distributed SQL Server Always On Availability Groups

  • Disaster-recovery and Multi-site scenarios: We can create availability groups span across multiple data centers. You do not require geographical stretched failover clusters
  • Datacenter migrations: Suppose you need to migrate the servers from a data center to another. Your applications cannot afford much downtime. You need a high resilience infrastructure to move your resources. Usually, we use the backup-restore method, log shipping methods to send data across another data center

Suppose we are running SQL Server in Windows Server 2012 and we require to migrate to Windows Server 2016 without changing anything in the SQL Server. You can leverage the distributed availability group for these hardware or software movements as well.

  • Failover mechanism in the DR site: We configure a distributed availability group in the DR site having nodes in a failover cluster group and AG configuration with the listener. It provides a safeguard mechanism to your DR infrastructure as well and safeguards you in case of any server issues. You can also apply patches to DR replica nodes with minimum downtime
  • Scale-out readable replicas with distributed availability groups: In a traditional SQL Server Always On Availability Groups configuration, SQL Server 2019 allows 1 primary and up to 8 secondary replicas. In a distributed configuration, we have two availability groups, so it supports up to 16 secondary replicas
  • In the below image, we see multiple distributed groups to provide scalable read-only secondary replicas
  • We configured a distributed replica between Cluster A and Cluster B
  • Another distributed replica between Cluster B and Cluster C

distributed SQL Server Always On Availability Groups

Environment setup details

You should explore previous articles in the SQL Server Always On Series and prepare the environment as below.

Multiple failover cluster and AG

  • You should configure two failover clusters as below:
    • First Windows Failover Cluster:
      • In the first cluster, you have two instances [SQLNode1\INST1] and [SQLNode2\INST1]
      • You use synchronous data synchronization between primary and secondary SQL Server Always On Availability Group for the [SQLSHACKDEMO] database.
      • You configure a SQL Listener [SQLDCAG] to connect with the primary replica

      SQL AG listener

    • Second Windows Failover Cluster
      • In this second cluster, you have two instances [SQLAG1\INST1] and [SQLAG2\INST2]
      • You use synchronous data synchronization between primary and secondary replica for the [DBARepository] database
      • You configure a SQL Listener [SQLDR] to connect with the primary replica

      Second AG listener

  • Both Windows Failover Clusters are part of the [MyDemoSQL.COM] domain
  • You should allow firewall in both clusters to allow connections to another cluster replica
  • Your AG dashboard is healthy in both SQL Server Always On Availability Groups
    • SQLAG2019 Availability Group

      AG dashboard

    • SQLDRAG Availability Group

      AG synchronization

Details to configure a Distributed Availability Group

For the distributed AG configuration, note the following useful points.

  • It connects using the SQL Listener of both availability groups in separate clusters. In a traditional AG, listener configuration is optional. If you do not use listeners, you need to create them first before planning for the distributed AG
  • You should configure the HADR endpoint to listen to all IP addresses. You can script out the existing. Connect to the primary replica in SSMS for both clusters and navigate to Server Objects -> Endpoints -> Database Mirroring -> Hadr_endpoint. Right-click on the Hadr_endpoint and generate a create endpoint t-SQL. It should use the parameter LISTENER_IP=ALL as shown below:

    Endpoint script

  • You cannot configure a listener for the distributed availability group. In this case, your application cannot redirect connections to another cluster (primary) automatically. It requires an explicit configuration in the application connection string
  • We can configure the distributed availability group for both synchronous and asynchronous mode
  • Data movement in a distributed availability group is different than to a traditional availability group
    • Let’s assume that Cluster A is the primary replica in a distributed availability group. SQLNode1 is the primary availability group of the primary replica
    • Similarly, Cluster B is the secondary replica of the distributed availability group. SQLAG1 is the primary availability group of the secondary replica
    • The Primary replica of the secondary distributed availability group receives the transactions and forwards it to the secondary replica. This primary replica is known as the forwarder. In our current scenario, SQLAG1 is the forwarder

    AG replica forwarder

  • It does not have a mechanism to support the automatic failover between multiple clusters. A distributed availability group supports only manual failover using the FORCE_FAILOVER_ALLOW_DATA_LOSS parameter
  • We can configure a distributed availability group in below failover cluster scenarios:
    • Your both failover clusters are part of the same domain. This article explores this scenario practically
    • Your both clusters are a member of the different domains. You should have domain trust relationships to allow the database connections
    • Your one failover cluster is part of a domain while another cluster is not part of the domain. You need to use certificates to allow the DB connections
    • Both clusters are not part of the domain

Conclusion

In this article, we learned the concept of distributed SQL Server Always On Availability Groups, its features and its requirements. In the next article, we cover the practical implementation of it.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups

Rajendra Gupta
776 Views