Introduction

Sometimes we face the scenario in an enterprise environment that the database in SQL Server Always On Availability Group (AOAG) has high concurrency read and write access from application servers. If we keep using the one network interface card for both network traffic of database connections from application servers and database mirroring between AOAG replicas, the network performance will be pretty poor. This article will look at adding a second NIC to the SQL Server to segregate the traffic.

Before reading my article you must master how to build a common AOAG at first.

Solution

Add a second NIC on each replica in SQL Server Always On Availability Group and assign IP addresses for them. These IP addresses should be in a different subnet compared to the first NIC. The subnet for the database mirroring endpoint does not need to connect to the domain network, and we also need to configure the static route for the IP address on second NIC.

In this case, all database mirroring network traffic will go through the second NIC which will greatly save the bandwidth usage on the first NIC, so the network performance between database in AOAG and application servers will get improve.

Architecture

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

Configuration

Refer to my previous article "How to setup multi-subnets Azure SQL VM AG group based WSFC (Windows Server 2019) ", as most configuration steps are the same. I will show you the different configuration steps below:

Before setting up WSFC, you need to change the static route on the SQL Servers, here is the IP configuration on vmsqltest01:

and we should use the following CMD command to update the static route:

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

Here you can see the updated route table:

Note: This step is necessary, if you don't update the static route on your SQL Server manually, it will cause a network issue because your SQL Server outbound network traffic doesn't know through which NIC to go. We make this setting to ensure only the database mirroring network traffic will go through the second NIC!

When you configure the endpoints during the creation of the Availability Group, please do not use the default setting (FQDN as Endpoint URL), the following snapshot is the default endpoint URL setting:

Please use the second NIC IP address instead of FQDN to set the Endpoint URL like the following snapshot:

Then you can do the remaining tasks as normal.

When you finished all tasks for building the AOAG, you can find the TCP connection in the Resource monitor:

that means the communication on the port by the second NIC between replica is succeeded.

Summary

Network performance is always the key point in AOAG, adding the second NIC for AOAG to separate business and DB mirroring network traffic is a good way to reduce the network pressure of database mirroring for high concurrency scenarios. Especially it is very suitable for on-premise SQL Servers, it can fully utilize the performance of physical network cards, and it is easy to configure.