In an enterprise environment, it is common to have a SQL Server Always On Availability Group (AOAG) with high concurrency read and write access from application servers. However, using a single network interface card (NIC) for both database connections and database mirroring can lead to poor network performance. In this article, we will explore a solution to this problem by adding a second NIC to the SQL Server to segregate the traffic.

Solution

The solution involves adding a second NIC on each replica in the SQL Server Always On Availability Group and assigning IP addresses to them. These IP addresses should be in a different subnet compared to the first NIC. Additionally, the subnet for the database mirroring endpoint does not need to connect to the domain network, and a static route for the IP address on the second NIC needs to be configured. By doing this, all database mirroring network traffic will go through the second NIC, significantly improving the network performance between the database in AOAG and the application servers.

Architecture

In our showcase, we added the second NIC (“Network51 0”) for each AOAG replica and assigned IP addresses to them (11.11.10.15/24 for vmsqltest01 and 11.11.10.16/24 for vmsqltest02). Here is a diagram of this architecture:

Configuration

Before setting up the Windows Server Failover Cluster (WSFC), it is necessary to change the static route on the SQL Servers. Here is the IP configuration on vmsqltest01:

To update the static route, use the following CMD command:

route delete 0.0.0.0 11.11.10.1
route add -p 11.11.10.0 mask 255.255.255.0 11.11.10.1 metric 3

Make sure to update the static route on all SQL Servers in the AOAG. This step is crucial as it ensures that only the database mirroring network traffic will go through the second NIC.

During the creation of the Availability Group, it is important to configure the endpoints correctly. Instead of using the default setting (FQDN as Endpoint URL), use the second NIC IP address as the Endpoint URL. This ensures that the communication between replicas occurs through the second NIC.

Once all the necessary tasks for building the AOAG are completed, you can verify the TCP connection in the Resource Monitor. This indicates that the communication on the port by the second NIC between replicas is successful.

Summary

Network performance is a crucial aspect of SQL Server Always On Availability Group. By adding a second NIC to segregate business and database mirroring network traffic, the network pressure of database mirroring for high concurrency scenarios can be significantly reduced. This solution is particularly suitable for on-premise SQL Servers as it fully utilizes the performance of physical network cards and is easy to configure.