Configure a Dedicated Network Adapter for SQL Server Always On Distributed Availability Groups Data Replication Traffic

By: Edwin Sarmiento   |   Updated: 2018-11-30   |   Comments (5)   |   Related: > Availability Groups

Problem

We are exploring SQL Server Always On Distributed Availability Group as our disaster recovery (DR) strategy. We have multiple network connections to our DR data center and would like to utilize one of them specifically for the SQL Server Always On Availability Group data replication traffic. Our network engineers would like to guarantee Quality of Service (QoS) so it doesn’t get affected by other traffic in the network.  How do I configure a dedicated network adapter for SQL Server Always On Distributed Availability Groups data replication traffic?

Solution

In a previous tip on Configuring a Dedicated Network for SQL Server Always On Availability Groups Data Replication Traffic, you have seen how to configure a Windows Server Failover Cluster (WSFC) with dedicated network adapters that can be used for SQL Server Always On Availability Group data replication traffic. You’ve also seen in a previous tip on Setup and Implement SQL Server 2016 Always On Distributed Availability Groups that they involve two (2) WSFCs.

However, configuring a dedicated network adapter for SQL Server Always On Distributed Availability Groups data replication traffic is a bit challenging. That’s because, most of the time, the dedicated network subnets are not exposed to other network services such as the DNS.  In the previous tip, an Availability Group listener name that leverages the DNS service was used to direct data replication traffic from the primary Availability Group to the secondary Availability Group. For dedicated network subnets, network engineers either need to add a static route to the routing tables of network devices to properly route traffic in the network or deploy a dedicated DNS server specifically just for that subnet. Both approaches require additional management and operations overhead that your network engineers will not be happy about. The goal is to minimize the amount of changes done on the network side for ease of management and troubleshooting in the future.

Configure a Dedicated Network Adapter for SQL Server Always On Distributed Availability Groups Data Replication Traffic

A network diagram is provided below to better describe the architecture.

There are two WSFCs, each one hosts a traditional Availability Group. Only the public network adapters have access to the DNS service. The private network subnets (10.0.0.0/8 and 10.0.1.0/16) will be used for the SQL Server Always On Distributed Availability Groups data replication traffic. Below are the TCP/IP configuration of the servers.

Here’s a high-level overview of the steps for your reference.

1. Create the endpoints on the replicas of the primary Availability Group
2. Create the primary Availability Group (AG_DC1) with a corresponding listener name (AG_DC1_LISTENER)
3. Create the endpoints on the replicas of the secondary Availability Group
4. Create the secondary Availability Group (AG_DC2) with a corresponding listener name (AG_DC2_LISTENER)
5. Create the Virtual IP Addresses for Distributed Availability Group Data Replication Traffic
6. Create Distributed Availability Group (DistAG_DC1_DC2) on the primary Availability Group (AG_DC1)
7. Join the secondary Availability Group (AG_DC2) to the Distributed Availability Group

Before proceeding with the configuration, make sure that each server can access the other servers on all network subnets. A simple PING and TELNET tests can be used for verification.

NOTE: Use SQLCMD mode when running the T-SQL scripts to make sure you are connected to the correct SQL Server instance.

Step #1: Create endpoint on all the replicas in the primary Availability Group

Use the T-SQL script below to create the endpoint on all of the replicas in the primary Availability Group and to grant CONNECT permissions to the SQL Server service account. Be sure that the endpoint is listening on all IP addresses.

--Create endpoint on all Availability Group replicas
--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRDAG01
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO 

--Run this on the secondary replica of the primary Availability Group
:CONNECT TDPRDAG02
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

:CONNECT TDPRDAG01
--Create login and grant CONNECT permissions to the SQL Server service account
USE master
GO

CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;
GO

GRANT CONNECT ON ENDPOINT::Hadr_endpoint 
TO [TESTDOMAIN\sqlservice];  
GO

:CONNECT TDPRDAG02
--Create login and grant CONNECT permissions to the SQL Server service account
USE master
GO

CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;
GO

GRANT CONNECT ON ENDPOINT::Hadr_endpoint 
TO [TESTDOMAIN\sqlservice];  
GO   

Unlike in this previous tip where a specific IP address was used in the LISTENER_IP parameter of the CREATE ENDPOINT command, you want all of the replicas in the primary Availability Group to listen to all IP addresses.

Step #2: Create the primary Availability Group (AG_DC1) with a corresponding listener name (AG_DC1_LISTENER)

Use the T-SQL script below to create the primary Availability Group AG_DC1 and join the secondary replica to it. Be sure you are connected to the SQL Server instance that you want to configure as primary replica of the primary Availability Group.

--Create primary Availability Group on first failover cluster with replicas and listener
--Run this on the primary replica of the primary Availability Group
:CONNECT TDPRDAG01
CREATE AVAILABILITY GROUP [AG_DC1] 
FOR  
REPLICA ON
N'TDPRDAG01' WITH
(
   ENDPOINT_URL = N'TCP://10.0.0.11:5022',
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
),  
N'TDPRDAG02' WITH
(  ENDPOINT_URL = N'TCP://10.0.0.12:5022',  
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
)
LISTENER 'AG_DC1_LISTENER'
(
   WITH IP ( ('172.16.0.13','255.255.0.0') ) ,
   PORT = 1433
);  
GO 

--Join the secondary replicas to the primary Availability Group
--Run this on the secondary replicas of the primary Availability Group 
:CONNECT TDPRDAG02
ALTER AVAILABILITY GROUP [AG_DC1] JOIN

--Allow the Availability Group to create databases on behalf of the primary replica
ALTER AVAILABILITY GROUP [AG_DC1] GRANT CREATE ANY DATABASE
GO

Note that the ENDPOINT_URL values on both Availability Group replicas use the private IP address (10.0.0.0/8) and not a fully qualified domain name (FQDN). That’s because a FQDN requires name resolution to resolve the corresponding IP address. Since the private IP addresses are not exposed to the DNS service, you need to explicitly define them to the Availability Group for the replica endpoints.

The Availability Group listener name AG_DC1_LISTENER uses a public IP address since a DNS server is available for name resolution. Plus, this is what the client applications use to connect to the databases in the Availability Group.

At this point, you can add databases to the Availability Group and verify that the data replication traffic is using the private network adapters.

Below is a screenshot of the secondary replica of the primary Availability Group utilizing the private network adapter (10.0.0.12) for the data replication traffic.

Step #3: Create the endpoints on the replicas of the secondary Availability Group

Use the T-SQL script below to create the endpoint on all of the replicas in the secondary Availability Group and to grant CONNECT permissions to the SQL Server service account. Be sure that the endpoint is listening on all IP addresses.

--Create endpoint on all Availability Group replicas
--Run this on the primary replica of the secondary Availability Group
:CONNECT TDDRAG01
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)  
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO 

--Run this on the secondary replica of the secondary Availability Group
:CONNECT TDDRAG02
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

:CONNECT TDDRAG01
--Create login and grant CONNECT permissions to the SQL Server service account
USE master
GO

CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;
GO

GRANT CONNECT ON ENDPOINT::Hadr_endpoint 
TO [TESTDOMAIN\sqlservice];  
GO

:CONNECT TDDRAG02
--Create login and grant CONNECT permissions to the SQL Server service account
USE master
GO

CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;
GO

GRANT CONNECT ON ENDPOINT::Hadr_endpoint 
TO [TESTDOMAIN\sqlservice];  
GO

Similar to the primary Availability Group, you want all of the replicas in the secondary Availability Group to listen to all IP addresses.

Step #4: Create the secondary Availability Group (AG_DC2) with a corresponding listener name (AG_DC2_LISTENER)

Use the T-SQL script below to create the secondary Availability Group AG_DC2 and join the secondary replica to it. Be sure you are connected to the SQL Server instance that you want to configure as primary replica of the secondary Availability Group.

--Create secondary Availability Group on second failover cluster with replicas and listener
--Run this on the primary replica of the primary Availability Group
:CONNECT TDDRAG01
CREATE AVAILABILITY GROUP [AG_DC2] 
FOR  
REPLICA ON
N'TDDRAG01' WITH
(
   ENDPOINT_URL = N'TCP://10.0.1.11:5022', 
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
),  
N'TDDRAG02' WITH
(  ENDPOINT_URL = N'TCP://10.0.1.12:5022',  
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
)
LISTENER 'AG_DC2_LISTENER'
(
   WITH IP ( ('192.168.0.13','255.255.255.0') ) ,
   PORT = 1433
);  
GO 

--Join the secondary replicas to the secondary Availability Group
--Run this on the secondary replicas of the secondary Availability Group 
:CONNECT TDDRAG02
ALTER AVAILABILITY GROUP [AG_DC2] JOIN

--Allow the Availability Group to create databases on behalf of the primary replica
ALTER AVAILABILITY GROUP [AG_DC2] GRANT CREATE ANY DATABASE
GO  

Similar to the primary Availability Group, the ENDPOINT_URL values on both Availability Group replicas use the private IP address (10.0.1.0/16) and not a fully qualified domain name (FQDN). That’s because a FQDN requires name resolution to resolve the corresponding IP address. Since the private IP addresses are not exposed to the DNS service, you need to explicitly define them to the Availability Group for the replica endpoints.

The Availability Group listener name AG_DC2_LISTENER uses a public IP address since a DNS server is available for name resolution.

In the previous tip, a listener name was used to connect the primary Availability Group to the secondary Availability Group because the servers use DNS name resolution. Because you want to use a dedicated network adapter for the data replication traffic, a listener name that uses a FQDN will not make sense.

While you don’t necessarily need a listener name for the secondary Availability Group since there is only one read-write copy of the databases in an Availability Group, it is included as part of the creation of the secondary Availability Group so you don’t have to do it when you invoke DR and failover the Distributed Availability Group to the DR data center.

NOTE: You won’t yet be able to verify that the private network adapters are being used for the data replication traffic on the secondary Availability Group. That’s because there is no traffic coming from the primary Availability Group just yet until the Distributed Availability Group has been created. If you want to verify that the configuration does use the private network adapters for the Availability Group data replication traffic, add a dummy database to the secondary Availability Group and monitor the traffic on the private network adapter.

Below is a screenshot of the secondary replica of the secondary Availability Group utilizing the private network adapter (10.0.1.12) for the data replication traffic.

Once verified, remove the database from the secondary Availability Group prior to configuring the Distributed Availability Group.

Verify that you now have two Availability Groups.

Step #5: Create the Virtual IP Addresses for Distributed Availability Group Data Replication Traffic

NOTE: While this step is not in any of the official Microsoft documentation regarding Distributed Availability Groups, this is what allows redirection of the Availability Group data replication traffic to the private network adapters. And because the configuration of the Distributed Availability Group is still uses a valid TCP endpoint, it is considered supported.

An Availability Group listener name creates a corresponding virtual IP address and - if the WSFC is joined to an Active Directory domain – a virtual computer object. Active Directory, then, triggers the creation of a DNS entry with the corresponding virtual IP address. This is what allows client applications to find the Availability Group in the network thru the DNS name.

If there is no available DNS server, the client applications need to be able to find the servers in the network using an IP address instead of a DNS name.

Think of Availability Group secondary replicas as client applications while the primary replica is the server. When you define the ENDPOINT_URL parameter, you are telling SQL Server that an Availability Group replica can be found on the network via a TCP endpoint – a combination of a FQDN (or an IP address) and a port number. If a FQDN is used, an Availability Group secondary replica will query the DNS server to identify the corresponding IP address of the primary replica. Once found, it can initiate a connection to the primary replica.

Without a DNS server, the only way for the Availability Group secondary replicas to find the primary replica is to use an IP address instead of a FQDN in the ENDPOINT_URL parameter (unless you want to use local hosts files which are very difficult to maintain). Hence, the use of an IP address in the ENDPOINT_URL parameter values in Step #2 and Step #4.

Even more challenging is the fact that you now need to deal with multiple WSFCs. The primary replica of the primary Availability Group can run on any of the nodes in the WSFC. This means that the secondary Availability Group needs to be able to connect to the primary Availability Group via the private IP address regardless of which WSFC node it is running on top of. The same is true when the secondary Availability Group becomes the primary Availability Group in a DR situation and you initiate failover of the Distributed Availability Group to the DR data center.

This is where the additional virtual IP address comes in. You create an additional virtual IP address in the existing Availability Group clustered role using the private IP subnet.  And since the virtual IP address is a floating IP address that moves together with the Availability Group, the secondary Availability Group can be redirected to the primary Availability Group when using the virtual IP address in the ENDPOINT_URL parameter.

To create the additional virtual IP address in the primary Availability Group using Failover Cluster Manager,

1. Select the role that contains the primary Availability Group. In this example, it’s AD_DC1.
2. Right-click on the role and select Add Resource > More Resources > IP Address. This will create a new virtual IP address in the role.

3. Double-click the IP Address: <not configured> resource to open the Properties dialog box. Don’t worry about the resource being offline.

4. In the IP address: <not configured> Properties dialog box, in the General tab,
   1. Enter a descriptive name in the Name text box
   2. Click the drop-down list in the Network: field and choose the IP subnet of the private IP address.
   3. In the Address: text box of the Static IP address option, enter the virtual IP address.
   4. Click Apply. This will configure the virtual IP address for the private IP subnet.

5. Once the virtual IP address has been configured, right-click and select Bring Online.

Verify that the newly created virtual IP address is accessible via the private network, log in to the replicas of the secondary Availability Group and perform a simple PING test.

To create the additional virtual IP address in the primary Availability Group via PowerShell, use the Add-ClusterResource cmdlet with “IP Address” as the ResourceType.

Add-ClusterResource -Name VIP_AG_Replication -ResourceType "IP Address" -Group AG_DC1 -Cluster TDPRDAG10 

Once the virtual IP address resource has been created, you can proceed to assign the IP address. Use the Set-ClusterParameter PowerShell cmdlet to assign the following parameters:

• Address: 10.0.0.13
• SubnetMask: 255.0.0.0
• Network: the name of the WSFC network adapter assigned for the IP address

Get-ClusterResource “VIP_AG_Replication” -Cluster TDPRDAG10 | Set-ClusterParameter -Multiple @{"Address"="10.0.0.13";”SubnetMask”="255.0.0.0";"Network"="Heartbeat_DC1"}   

Finally, you can bring it online using Start-ClusterResource cmdlet.

Start-ClusterResource “VIP_AG_Replication” -Cluster TDPRDAG10 

NOTE: There is no need to configure cluster resource dependency for this virtual IP address. In a typical Availability Group configuration, the IP address mapped to the ENDPOINT_URL is always available. During a failover, the WSFC will first bring all of the cluster resources online that do not have any resource dependencies. In this configuration, the WSFC will bring the virtual IP addresses – both for the Availability Group listener name and this new virtual IP address – first before bringing the Availability Group online.

Repeat the whole process to create the corresponding virtual IP address for the private network subnet on the WSFC of the secondary Availability Group.

Step #6: Create Distributed Availability Group (DistAG_DC1_DC2) on the primary Availability Group (AG_DC1)

Once the secondary Availability Group and the virtual IP addresses for the private network subnet have been created, you can now proceed to create the Distributed Availability Group. Use the T-SQL script below to create the Distributed Availability Group DistAG_DC1_DC2. Be sure you are connected to the SQL Server instance that you want to configure as primary replica of the primary Availability Group.

:CONNECT TDPRDAG01
--Create Distributed Availability Group
--Run this on the primary replica of the primary Availability Group
CREATE AVAILABILITY GROUP [DistAG_DC1_DC2]  
WITH (DISTRIBUTED)   
AVAILABILITY GROUP ON  
'AG_DC1' WITH    
(   
   LISTENER_URL = 'TCP://10.0.0.13:5022',    
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
),   
'AG_DC2' WITH    
(   
   LISTENER_URL = 'TCP://10.0.1.13:5022',   
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
);    
GO    

Similar to the replicas of the primary and secondary Availability Groups ENDPOINT_URL values, the LISTENER_URL values on both primary and secondary Availability Groups use the private IP addresses defined in the virtual IP addresses (10.0.0.0/8 and 10.0.1.0/16, respectively) and not FQDNs.

Step #7: Join the secondary Availability Group (AG_DC2) to the Distributed Availability Group

Once the Distributed Availability Group has been created, you can now proceed to join the secondary Availability Group. Use the T-SQL script below to join the Availability Group AD_DC2 to the Distributed Availability Group DistAG_DC1_DC2. Be sure you are connected to the SQL Server instance that you want to configure as primary replica of the secondary Availability Group.

--Join the second Availability Group to the Distributed Availability Group
--Run this on the primary replica of the secondary Availability Group
:CONNECT TDDRAG01
ALTER AVAILABILITY GROUP [DistAG_DC1_DC2]   
JOIN   
AVAILABILITY GROUP ON  
'AG_DC1' WITH     
(   
   LISTENER_URL = 'TCP://10.0.0.13:5022',    
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
),   
'AG_DC2' WITH  
(   
   LISTENER_URL = 'TCP://10.0.1.13:5022', 
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
);    
GO

Review the status of the primary and secondary Availability Groups and verify that the databases are SYNCHRONIZED.

To verify that the Distributed Availability Group data replication is using the dedicated network adapter for the private network subnet, run transactions on the database in the primary Availability Group and monitor the private network adapter of the primary replica of the secondary Availability Group (the primary replica of the secondary Availability Group also hosts the virtual IP address of the private network subnet). Below is a screenshot of the primary replica of the secondary Availability Group utilizing the virtual IP address of the private network adapter (10.0.1.13) for the data replication traffic.

Next Steps

• Review the previous tips on SQL Server AlwaysOn Availability Group Configuration
  • SQL Server AlwaysOn Availability Groups - Part 1 configuration
  • SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup
  • Configuring a Dedicated Network for SQL Server Always On Availability Groups Data Replication Traffic
  • Setup and Implement SQL Server 2016 Always On Distributed Availability Groups
• Read about SQL Server 2016 Distributed Availability Groups from the Microsoft Documentation page

About the author

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