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?