Configuring a Dedicated Network for SQL Server Always On Availability Groups Data Replication Traffic

By: Edwin Sarmiento   |   Updated: 2017-09-21   |   Comments (8)   |   Related: > Availability Groups

Problem

Our network engineers would like to configure a dedicated network for SQL Server Always On Availability Group data replication traffic. They would like to guarantee Quality of Service (QoS) so it doesn’t get affected by other traffic in the network. The server administrators configured the Windows Server Failover Cluster (WSFC) to have a dedicated private network. Can we utilize that for the Availability Group data replication traffic?

Solution

In a previous tip on SQL Server 2012 Multi-Subnet Cluster Part 1, I highlighted the importance of having a dedicated network for internal cluster (also known as heartbeat or private) communication. While Windows Server 2008 and higher server operating systems allowed you to build a WSFC with a single network adapter, I don’t recommend it. Your goal for building a WSFC is high availability and resiliency. I would add Quality of Service (QoS) as another reason. While this may be specifically for the network administrators, it affects how SQL Server deals with meeting recovery objectives and service level agreements for highly available databases. I know some server administrators would argue that you can have multiple network adapters that are configured in a teamed pair to address high availability and resiliency. The operating system might see it as a single network adapter although, in reality, you have several. I would go beyond the concept of configuring network adapters in a teamed pair and provide redundancy in terms of an additional network with its own switches and routers. Because it doesn’t make any sense to provide high availability and resiliency on the network adapters when they are all plugged in to the same network switch – you still have a single point of failure.

Let’s say you’ve configured a dedicated network adapter with its own network devices for the WSFC private communication traffic. You certainly don’t want just the WSFC private communication traffic to be the only traffic going thru that network. Besides, the WSFC private communication packets are very lightweight (about 134 bytes) and will use all the available network paths to the different nodes of the WSFC. You can leverage the dedicated WSFC private communication network for the SQL Server Availability Group data replication traffic. Just make sure that the amount of transaction log records generated on your primary replica databases will not overly saturate the network or you run the risk of redirecting the WSFC private communication traffic to the production network or, even worse, cause unexpected outages.

Depending on how your network is designed, you can have several network adapters for different purposes. In my previous life as a data center engineer, we standardized on deploying servers with at least four (4) network adapters – one for management (connecting via Remote Desktop), one for monitoring, one for backup/DR and one for production. Each of the network adapters are accessed via a DNS alias to minimize user error. And these are just for standalone servers - WSFCs have more.

NOTE: Virtual machines in a hypervisor have different network configuration depending on the workload. If you are running SQL Server Availability Groups in a hypervisor, consult your VM administrators to verify if the WSFC nodes do have a dedicated physical (not virtual) network adapters.

Configure SQL Server Always On Availability Groups To Use Dedicated Network for Data Replication

I’m using a 2-node WSFC with two (2) standalone SQL Server instances as replicas for this example. Below are the TCP/IP configuration of the servers.

A public IP address will be used for the Availability Group listener name while the private IP addresses will be used for the Availability Group data replication traffic. The screenshots below show the network configuration of the servers. The network adapters have been renamed accordingly – both on the operating system and the WSFC.

Creating the Availability Group using a dedicated network adapter for the data replication traffic is no different from creating it using the wizard as outlined in this tip. However, you need to configure the endpoint first before you use the New Availability Group Wizard.

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

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

To use the private IP address for the Availability Group data replication traffic, the LISTENER_IP parameter of the CREATE ENDPOINT command does not specify a value of ALL. Rather, it lists the private IP address of the replica. This tells the Availability Group to direct the data replication traffic specifically to this IP address.

When you use the New Availability Group Wizard, you can modify the Endpoint URL fields under the Endpoints tab when you specify the replicas.

Note that these are the IP addresses you specified in the LISTENER_IP parameter when you created the corresponding endpoints. You can also use fully qualified domain names by adding entries in the HOSTS file of each WSFC node. For example,

10.0.1.113   WSFC-DC2-NODE1.TESTDOMAIN.local

10.0.1.114   WSFC-DC2-NODE1.TESTDOMAIN.local

Since this is a private network, it is unlikely that it will have access to a DNS server. 

Notice that the Availability Group listener name uses an IP address in the public network.

Proceed to create the Availability Group using the provided information.

Let’s verify that the Availability Group is indeed redirecting the data replication traffic to the private network. I have two Availability Groups on two different WSFC (but with the same configuration) – one is configured to use the private network while the other isn’t. I ran the same workload on the same databases for both.

The screenshot on the left is for the Availability Group that does not utilize the private network adapter for the data replication traffic while the one on the right does. Notice the difference in the amount of traffic going thru the private network adapter between the two.

To confirm that I am indeed getting the same amount of transactions for the databases on both Availability Groups, I also captured appropriate PerfMon counters.

This also confirms that the Availability Group data replication traffic is the same for both yet only the Availability Group on the right uses the dedicated private network.

Configuring a dedicated network for SQL Server AlwaysOn Availability Group data replication traffic can help guarantee Quality of Service (QoS) so that you can meet your database recovery objectives and service level agreements. Work with your network engineers and server administrators on how to properly configure the network topology to make it work.

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
• Read about Configuring Windows failover Cluster Networks

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