DISTRIBUTED ALWAYS ON

What is Distributed Always ON

• It is a particular type of availability group that helps access the multiple failover clusters
• SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes
• They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment.

Why Distributed Always ON

• Suppose if there are two independent failover clusters in infrastructure. These clusters are configured with two separate SQL Server Always On availability groups.
• Suppose one of the clusters is in DC and the second cluster is in DR.
• SQL Server distributed availability group provides a solution to configure the availability groups between these clusters.
• A distributed availability group can scale out readable replicas in two ways.

Advantages of Distributed Always ON

• Disaster-recovery and Multi-site scenarios
• Combined HA / DR
• Datacenter migrations
• Failover mechanism in the DR site
• Scale-out readable replicas with distributed availability groups
• Enhanced Performance

How to Configure Distributed Always ON

• If there is a failover cluster in DC site, and it has a synchronous replica between the two nodes and configured a SQL listener to point applications to the primary replica
• Likewise a failover cluster in the DR site, and it also has a synchronous data commit replica in its two nodes and configured another SQL listener in the DR cluster
• A distributed AG is created that connects the listener of both failover clusters hosted in the DC and DR site
• Configuration can be both synchronous or asynchronous data commit for a distributed availability group.

Details of Configuring Distributed Always ON Group

Let’s configure two failover clusters as below:

First Windows Failover Cluster

• In the first cluster, you have two instances [SQLNODE1\Instance1] and [SQLNODE2\Instance1]
• You use synchronous data synchronization between primary and secondary SQL Server Always On Availability Group for the [DB1] database.
• You configure a SQL Listener [SQLDCAG] to connect with the primary replica

Second Windows Failover Cluster

• In this second cluster, you have two instances [SQLAG1\Instance1] and [SQLAG2\Instance2]
• You use synchronous data synchronisation between primary and secondary replica for the [DB2] database
• You configure a SQL Listener [SQLDR] to connect with the primary replica
• Both Windows Failover Clusters are part of the same domain.
• Allow firewall in both clusters to allow connections to another cluster replica
• AG dashboard is healthy in both SQL Server Always On Availability Groups

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
• 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 
• Cannot configure a listener for the distributed availability group. In this case, application cannot redirect connections to another cluster (primary) automatically. It requires an explicit configuration in the application connection string
• 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
• It does not have a mechanism to support the automatic failover between multiple clusters. A distributed availability group supports only manual failover.