Configure Availability Group to Communicate on a Dedicated Network

  • [アーティクル]

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));

Comments

  • Anonymous
    June 08, 2015
    This worked great for me. Thanks!

  • Anonymous
    July 13, 2015
    This worked great for me as well.

  • Anonymous
    January 25, 2017
    So is the Listener created with the IPs 10.10.1.20 and 10.10.1.21?

  • Anonymous
    May 24, 2017
    In total, how many NIC should we have? Three?publicPrivateReplication

  • Anonymous
    August 03, 2017
    Do the private networks require a gateway? My scenario includes a private network only between two nodes, and therefore, I didn't setup a gateway. Thanks for your help.