We have a two-node SQL Server AlwaysOn Availability Group cluster configured in one of our datacenters, Datacenter A, and for disaster recovery we are looking to expand this to two additional nodes in Datacenter B. The two nodes in Datacenter A are configured for synchronous replication; our plan is to configure the replicas in Datacenter B for async replication. The two datacenters have different networks, but there is cross-site routing (and some sort of dedicated link -- it appears to have good bandwidth and at least decent latency).

It seems broadly that this configuration is supported (also see the description of the StackExchange database configuration), but we're a little unsure of the particulars of the network configuration.

  1. Do the DR nodes need to be joined to the existing WSFC, or will they form their own WSFC? We currently have a fileshare witness in the cluster for HA automatic failover (hosted on a separate, non-database server in Datacenter A). If the DR nodes join the existing WSFC, do they also need to see the fileshare witness in Datacenter A? We don't necessarily want the DR nodes to have any quorum votes as we will only ever manually fail over. Should we add a fileshare witness hosted in Datacenter B, and does that then need to be reachable by all nodes in the cluster, including those in Datacenter A? If the DR nodes join the existing WSFC, will the WSFC then have one cluster IP from each network? Note that we don't want inter-network routing within the cluster; if a client tries to connect to a cluster address from Network B while the primary replica is currently in Network A, we want that connection to fail; we don't want any of the nodes in Datacenter A to have addresses bound from Network B. Or at least, this is my understanding, we had some concerns from our network team about introducing routing loops if nodes are binding non-local addresses.

  2. As I understand it, the Availability Group Listener will have one IP address from each network; our application then specifies both IPs in the connection string and includes MultiSubnetFailover=True; it is then basically up to the client to determine which network is accepting connections (similar to the older, simpler database mirroring configuration in which the client connection string would specify the failover partner in case the primary IP didn't respond). Is this correct? And clients in each network would be able to connect to nodes in both its local network and the remote network? (In this case it might make sense to alter the connection strings for all clients so that for each client its "local" AG Listener IP is listed first)

  3. At some point in the distant future, we may wish to swap the roles of the datacenters; that is, Datacenter B becomes the primary and Datacenter A becomes the DR site. In that situation, we would plan to reconfigure the Datacenter B nodes to have synchronous replication, add a fileshare witness in Datacenter B (to enable automatic failover, if we haven't already), and then alter the replicas in Datacenter A to be asynchronous. Is this reasonable to do with this configuration, and can all of these operations be done fully online (or at least with minimal downtime)?

  • our application then specifies both IPs in the connection string You do not need to do this. Use the listener name and multisubnetfailover=true, let the client driver choose who responds first. Commented Dec 9, 2016 at 21:03

1 Answer 1

  1. Yes the DR nodes need to be joined to the existing cluster. SQL Server 2016 has Distributed Availability Groups, which would a cluster at DR separate from the existing one, but you are on SQL Server 2014. The DR nodes should be able to see the fileshare witness. You can adjust quorum votes so that the DR nodes don't have a say in if quorum is down. I do add a fileshare in DR for when we failover to it, but it's not part of the cluster until the failover occurs.
  2. The connection string would specify the AG listener and would not specify the two IPs. MultiSubnetFailover=True takes care of it by connecting to the IP that returned success first.
  3. Yes it is very reasonable to swap roles. The last 3 companies I worked for had DR sites that we'd failover to 1-2 times per year, running production at DR for a few days and up to 3 weeks. Failovers are an offline event. They take about 15 seconds on AGs.

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.