We have two servers (SQL-ATL01, SQL-ATL02) that make up a Failover Cluster, each running as part of a SQL Server High Availability Group (HAG). Each server has two network cards. One is a 10Gbit card that is directly connected to the other server and is used for Synchronizing the HAG on a 192.168.99.x subnet. The other is a 1Gbit card that is used to connect the DB servers to a switch to communicate with the application servers on a 10.0.0.x subnet. The Listener is pointed to the 192.168.99.x subnet.
We want to add a third server (SQL-NYC01) in another physical location to the cluster and run it as an Async replica part of the HAG, but the VPN only routes traffic on the subnet on the 1Gbit network.
Is there any way to set up the Failover Cluster and High Availability Group to tell it:
- Send synchronous replica traffic for SQL-ATL01 <–> SQL-ATL02 over 192.168.99.x
- Send asynchronous replica traffic for (SQL-ATL01, SQL-ATL02) <–> SQL-NYC01 over 10.0.0.x
Or do we have to have all replica traffic going in and out on the same IP address/subnet?
Best Answer
Yes.
All AG replication traffic goes over TCP/IP connections made to the database mirroring endpoint on the instance hosting the primary replica.
The mirroring endpoint is, by default, listening on all IP addresses. If you configure the mirroring endpoint to listen on only one of a server's IPs, then when you create the AG you must register the ENDPOINT_URL using that IP address or a hostname that resolves to only that IP address.
But, in your desired scenario you must have the ATL servers' database mirroring endpoints listening on both IP interfaces (or else NY wouldn't be able to connect). So if you currently have the ATL nodes joined to the AG with their ENDPOINT_URL set to the 192.x IP addresses, you'll have to change that to add the NY node.
But you can ensure that the ATL-ATL traffic goes over the 192 network by using host file entries on the ATL servers, as @Tony Hinkle suggests.