Configure Availability Group to Communicate on a Dedicated Network

  • Article

SQL Server 2012 AlwaysOn Availability Groups uses a database mirroring endpoint for each SQL Server instance that hosts an availability group replica. The endpoint is used to communicate between the primary and the secondary replicas of the availability group.

If a second NIC is installed on the primary replica and secondary replica of an availability group, that availability group can be configured to replicate mirrored changes over the private network, isolating the availability group traffic. This cannot be configured completely in SQL Server Management Studio, therefore, this blog describes this process. 

 Scenario

Say you have the following two SQL Severs defined on two networks and wish to dedicate the private network (10.10.1.x) for availability group communication between SQLNODE1 (Primary) to SQLNODE2 (Secondary).

SQLNODE1
Public = 10.8.0.20
Private = 10.10.1.20

SQLNODE2
Public = 10.8.0.21
Private = 10.10.1.21

Configure endpoints to listen on private nic 

First, the endpoints on SQLNODE1 and SQLNODE2 must be configured to listen for traffic on port 5022 and network 10.10.1.x:

 :CONNECT SQLNODE1
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.1.20))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
 
 
:CONNECT SQLNODE2
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.1.21))
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO

Create availability group to connect to private nics

 When creating your availability group, specify the IP addresses in network 10.10.2.x to connect to each server:

:CONNECT SQLNODE1
CREATE AVAILABILITY GROUP [AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [AGDB]
REPLICA ON N'SQLNODE1' WITH (ENDPOINT_URL = N'TCP://10.10.1.20:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
       N'SQLNODE2' WITH (ENDPOINT_URL = N'TCP://10.10.1.21:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));