Your advice on Always On design please…
Imagine a scenario where you have 2 datacentres in different countries. You want to be able to run at full capacity in either datacentre. in each datacentre you wish to offload backup and read-only queries to a readable secondary when that datacentre is active.
In an ideal world you would like to have 4 servers in total, two in datacentre 'A' and two in Datacentre 'B', you wish to reduce the amount of traffic across the wan link so would prefer to limit the replication traffic to one stream of asynchronous traffic. You also want to have automatic fail-over in the active datacentre wherever that might be. In effect, you would like a Windows cluster with all 4 servers in it but have it subdivided by groups into two separate groups, one per site, running synchronous commit within the site yet asynchronous commit between the sites due to WAN latency.
Can a readable secondary on the second site act as the source for synchronous commit to anther node on that site?
If all replication has to stem from the primary then I suppose I am left with writing a PowerShell script to set Availability and fail-over modes for the available replicas in the event of a fail-over between sites.
Is there an easy solution that I'm missing?
Cheers
Best Answer
In traditional Always On Availability Groups, all replication traffic comes from the primary replica. If you have two replicas in a remote data center, they will both be updated from the primary replica, period. You can't do primary-to-secondary-to-secondary.
SQL Server 2016 introduced Distributed Availability Groups, which might satisfy your design goal. However, this is going to be much more complex, much less documented, and I wouldn't recommend doing this for your first AG.