7

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?

Improve this question
1
  • You may be able to do this by populating the servers' hosts files with entries to resolve the other nodes' names to the IP address you want them to use. Not sure, but shouldn't be too hard to test.
    – Tony Hinkle
    Commented May 25, 2018 at 17:03

1 Answer 1

Reset to default
3

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

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.

Improve this answer
2
  • If it's only listening on one IP, isn't that only for data that is received? What if you want to send data over a specific NIC to force it to be routed a certain way. Do you need to create a route locally for that?
    – Alen
    Commented May 25, 2018 at 19:18
  • When the primary sends packets to the secondary they will be addressed to the IP address that the secondary established the TCP connection from. In Windows that's based on the sender's route table (blogs.technet.microsoft.com/networking/2009/04/24/…). So, so long as each of your NICs is on a different subnet you normally don't have to manage the routes. If you have multiple IPs on the same subnet, then you will have to set specific routes.
    – David Browne - Microsoft
    Commented May 25, 2018 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.