Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

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


By:   |   Last Updated: 2017-09-21   |   Comments (2)   |   Related Tips: More > 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


Last Updated: 2017-09-21


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

 

 


Learn more about SQL Server tools