We recently set up a SQL 2012 HADR solution that took advantage of Availability Groups (AG’s). In this instance, we utilized a multi-subnet cluster to allow for the primary replica to live in our primary data center, while having the secondary replica in another data center.
Note: In this setup, only asynchronous replicas are allowed.
On each of the servers in the cluster, we have 3 network connections.
Public: All user traffic will flow through this connection
Private: For internal cluster communication
Replication: All AG traffic will be routed through this
After those dedicated connections are setup, database mirroring endpoints are used to receive connections other instances in the AG. You can read more about database endpoints here. Usually, when the endpoints are created in an AG, the following script is executed without specifying a specific IP to use.
--Replica1
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO
--Replica2
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO
Without specifying an IP that the endpoint is listening on, the listener will accept a connection on any valid IP. But, if we want to make sure our AG and user traffic are segregated, we must specify the IP of the Replication network connection. So the script would look like so:
--Replica1
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.89)) --<-- Your Replication IP here
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO
--Replica2
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.20.89)) --<-- Your Replication IP here
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO
You can reference the BOL article for creating an endpoint here
NOTE: Equally important when using the CREATE AVAILABILITY GROUP statement is to specify the replication IP in the ENDPOINT_URL section. By default, when scripting the AG, the FQDN will show in that section.
CREATE AVAILABILITY GROUP MyAG
FOR DATABASE MyDB1, MyDB2
REPLICA ON
'COMPUTER01' WITH
( ENDPOINT_URL = 'TCP://10.10.10.89:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL ),
'COMPUTER02' WITH
( ENDPOINT_URL = 'TCP://10.10.20.89:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL );
You can read up on the CREATE AVAILABILITY GROUP statement here.
After this done, and you’ve completed the rest of the AG setup, replication traffic will be routed through your Replication network. A quick and easy test of this is to open up Windows Task Manager and watch the traffic on the three different Ethernet connections.
Note the adapter name for each connection.
To test this open up an SSMS connection to the AG listener and send a large amount of DML statements to a database within the AG. If you setup the endpoint with the default LISTENER_IP = ALL, you’ll most likely see a high amount of send and receive traffic on Public interface. But, if you’ve setup the endpoint to listen on the Replication IP, you’ll see a large amount of receive traffic on the Public interface (DML) and a high amount of send traffic on the Replication interface (sending the changes to the secondary replica).
