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

By:   |   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.

NODE 1 NODE 2
OS: Windows Server 2016 OS: Windows Server 2016
Hostname: WSFC-DC2-NODE1 Hostname: WSFC-DC2-NODE2
IP Address (Public): 192.168.0.113/24 IP Address (Public): 192.168.0.114/24
IP Address (Private): 10.0.1.113/16 IP Address (Private): 10.0.1.114/16
Availability Group Listener Name: AG_DC2_LISTENER  
Availability Group Listener IP Address: 192.168.0.116/24  

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.

network connections
failover cluster manager heart beat configuration
failover cluster manager LAN configuration

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.

specify replicas for the new Availability Group

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. 

specify replicas with an Availability Group Listener

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.

Ethernet Performance in Windows Task Manager

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.

Performance Monitor Counters for SQL Server Availability Replica

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


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



Comments For This Article




Tuesday, October 17, 2023 - 11:26:09 PM - Edwin M Sarmiento Back To Top (91677)
> it is like telling us we should use the same link for cluster communication instead of multiple links...

Yes, the Microsoft documentation does say that.

However, Microsoft does not know your network infrastructure. They don't know whether you are running physical machines or VMs in a private/public cloud. For example, this statement,

> If the network link to this secondary replica fails (even intermittently)...

The tip emphasizes that this setup is to guarantee QoS. Having an HA setup like an Availability Group already assumes redundancy. Having a network link that fails would violate this assumption.

Microsoft's recommendation should never supersede the business needs unless it renders the config unsupported. It's a recommendation, not a "this is unsupported if you don't do this" statement

Tuesday, October 17, 2023 - 5:35:39 AM - goh Back To Top (91674)
Hi,

I read this from Microsoft doc
"We strongly recommend that you use the same network links for communications between WSFC nodes and communications between availability replicas. Using separate network links can cause unexpected behaviors if some of links fail (even intermittently).

"
it is like telling us we should use the same link for cluster communication instead of multiple links

Friday, February 10, 2023 - 2:00:14 PM - Edwin M Sarmiento Back To Top (90922)
Sky,

This is mostly applicable to physical machines. With VM, unless you are absolutely sure that your vNICs are directly mapped to physical NICs and that they are providing additional bandwidth beyond the teaming capabilities, this is just an additional complexity. Keep your configuration as simple as you possibly can.

Thursday, February 9, 2023 - 5:42:16 PM - Sky Back To Top (90904)
Question, in VMs we use virtual network cards, adding one more network card makes difference?

Thursday, February 7, 2019 - 12:31:38 PM - bass_player Back To Top (78985)

Javeed,

If you're on SQL Server 2016 or higher, you could implement Distrbuted Availability Groups

https://www.mssqltips.com/sqlservertip/5053/setup-and-implement-sql-server-2016-always-on-distributed-availability-groups/


Thursday, February 7, 2019 - 8:33:48 AM - Javed Khan Back To Top (78982)

 Hi Edwin,

I have DR server also in another domain. How will this dedicated connection set up also replicate the Always On traffic to DR replica via public IP?

Javed


Thursday, October 26, 2017 - 3:26:49 PM - bass_player Back To Top (68835)

DVP,

 

This would actually qualify as a "consulting" question and would require more than a simple response.

 

There are a couple of different points here, all pertaining to networking.

 

1) Dedicated network adapters. In a single data center deployment, the network adapters assigned for Cluster only traffic just needs to be connected to a single switch so there won't be a need for a default gateway value. Having a default gateway value assignned in the network adapter will affect how the Windows Server Failover Cluster (WSFC) directs traffic across all of the nodes. In a multi-data center deployment, you will need to assign a default gateway value to all of the dedicated network adapters to properly route traffic across all of the WSFC nodes - from the primary data center to the DR data center.

 

2) IP address and port number combination. You can only have a single IP-and-port combination per machine. If you already used one IP address with port 5022 for one of the Availability Group data replication traffic, you will need another IP-and-port combination. You can use the same IP address but with a different port number or different IP address but same port number. If you have multiple SQL Server instances in the same machine, you would need to have the same number of IP-and-port combination per instance for your Availability Group endpoints.

 

The number of separate IP addresses that you need for your setup really depend on the number of SQL Server instances per machine/replica and how the DR secondary replica is configured. Do both SQL Server 2014 instances on the primary replica use the same SQL Server 2014 instance as a secondary replica on the DR? Or do they both have their own pair of SQL Server 2014 primary-and-secondary replicas?

 

Also, how is the WAN link  bandwidth like between your primary data center and your DR data center? It wouldn't matter if you can configure dedicated network adapters for your Availability Group data replication traffic if your WAN link ends up becoming your bottleneck. 


Thursday, October 26, 2017 - 2:26:57 PM - DVP Back To Top (68832)

Hi  Edwin :

Thanks for the wonderful article. ( I always read anything your write on this site)

I am trying out this on my setup. But I have a 4 instnace setup 2012/14/14/16 on 3 modes. The third node is my DR.

I have set up 2 AGs for each instance so that half of the DBs are primary on node 1 and the other half on node 2. ( in sync read only mode for secondary )

So , I need 8 ( 4 x 2)  separate IPs for this setup  and another 4 for the DR subnet ? ?

Any help or hint is sincerely appreciated.

Regards

DVP

 

 















get free sql tips
agree to terms