One of my clients came up with the requirement to isolate the replication traffic from the public network. The advantage of using a separate network for replication is reduced latency and replication can still run without any latency issues during times of heavy network traffic issued by other applications or backup activities. In this article, I will show a procedure to setup a dedicated network for Availability Group communication

SQL Server 2012 AlwaysOn Availability Groups require a database mirroring endpoint for each SQL Server instance that will be hosting an availability group replica and/or database mirroring session. We have two nodes SQL1 and SQL2 in a Windows failover cluster. Each node has a standalone SQL Serverinstance installed and configured with an Always On AG. Each node also has a separate virtual network adapter for public communication, a virtual network adapter for WSFC communication, and a virtual network adapter that we’ll dedicate to availability group communication.

In the below screenshot, the Node1 (SQL1) Availability Group dedicated NIC IP address is 172.16.35.10. The Node2 (SQL2) Availability Group dedicated NIC IP address is 172.16.35.11.

Reconfigure Communication to a Dedicated Network

Step 1:

Identify the endpoint name configured for Always On replication. By default, “Hadr_endpoint” is the endpoint name. In below screenshot, we see this. 

Step 2:

Connect to each replica in the Always On group, drop the existing endpoint and recreate the endpoint with the specific dedicated network address

:CONNECT SQL1

DROP ENDPOINT [Hadr_endpoint];
GO
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (172.16.35.10))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
--configure endpoint security
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
END
GO
USE [master];
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Test\srv.sql];
GO

:CONNECT SQL2

DROP ENDPOINT [Hadr_endpoint];
GO
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (172.16.35.11))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
--configure endpoint security
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
END
GO
USE [master];
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [test\srv.sql];
GO

Step 3:

Modify the Availability Group endpoints configuration on all replicas.

ALTER AVAILABILITY GROUP LOGSHIPAON
MODIFY REPLICA ON 'SQL1'
WITH
(
    ENDPOINT_URL = N'TCP:// 172.16.35.10:5022'
);
GO
ALTER AVAILABILITY GROUP LOGSHIPAON
MODIFY REPLICA ON 'SQL2'
WITH
(
    ENDPOINT_URL = N'TCP:// 172.16.35.11:5022'
);
GO

Step 4:

Now you can see that the Availability Group communication traffic is configured and flowing on the dedicated NIC. In the below screenshot, the endpoint URL is configured on the dedicated NIC address.

In the below screenshot, REPL is my dedicated availability group NIC and has replication traffic flowing.

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin