(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

When you deploy a new SQL Server Availability Group, the synchronization traffic between all replicas is routed by default always through your default network card. In today’s blog posting I want to show you how you can configure your SQL Server Availability Group, so that the network traffic goes through a dedicated different network card.

Routing your Availability Group Traffic

When you use the Availability Group Wizard of SQL Server Management Studio you can define during the creation of your Availability Group through through which IP address, and therefore through which network card your Availability Group traffic will be routed.

If you specify here the IP address of another network card, the Availability Group traffic will be routed through the other network card – easy isn’t it? In my home lab (based on VMware vSphere), I have a dedicated 10Gbit network (besides the 1Gbit network), and my best practice is always to create VMs with 2 network cards.

The first network card is bound to the 1Gbit network, and the second one is bound to the 10Gbit network and is used for the Availability Group traffic. Therefore it’s also quite easy to show very easily how much network traffic an Availability Group generates.

If you prefer to deploy your Availability Group through T-SQL Code, you can also specify through the property ENDPOINT_URL a custom IP address:

CREATE AVAILABILITY GROUP [SQLpassion_AG]
WITH
(
   AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
   DB_FAILOVER = OFF,
   DTC_SUPPORT = NONE
)
FOR DATABASE [test]
REPLICA ON N'TEST1-VM' WITH
(
   ENDPOINT_URL = N'TCP://10.10.1.11:5022',
   FAILOVER_MODE = MANUAL,
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
   BACKUP_PRIORITY = 50,
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
N'TEST2-VM' WITH
(
   ENDPOINT_URL = N'TCP://10.10.1.12:5022',
   FAILOVER_MODE = MANUAL,
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
   BACKUP_PRIORITY = 50,
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
N'TEST3-VM' WITH
(
   ENDPOINT_URL = N'TCP://10.10.1.13:5022', 
   FAILOVER_MODE = MANUAL, 
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
   BACKUP_PRIORITY = 50,
   SEEDING_MODE = AUTOMATIC,
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
)
GO

Summary

As you have seen in this blog posting, SQL Server Availability Groups makes it very easy for us to route the network traffic through a dedicated network card. As a general rule of thumb I always recommend during my various consulting engagements to route the network traffic of Availability Groups through a (fast) dedicated network card.

Thanks for your time,

-Klaus