I'm currently setting up AlwaysOn SQL Server cluster. We have 3 DR sites:
London, Amsterdam, and Amazon, with London and Amsterdam synchronous, and Amazon asynchronous.
I have a couple of questions:
- I want to use read-only routing and the London to Amsterdam network latency is 8ms. Is that good for read-only routing or should I make another synchronous node in London, which is going to be the primary replica, and change Amsterdam to asynchronous?
- Does the secondary replica have to have the same amount of memory as the primary?
Best Answer
If you have 8ms latency between synchronous nodes, then you'll add 8ms to the commit of every transaction. So whether or not that is OK depends on whether that extra 8 ms is acceptable.
For the second question, no, there is no technical requirement that nodes have the same amount of memory. If you have 64 GB on the primary but you know that it can still operate adequately with 32 GB, then you can certainly run the secondaries with 32 GB.
One issue that I might be concerned about is that if you lose your network connections to Amsterdam and Amazon, then you'll have no HA during that time period. Based on my experience you may have a total of one day per year where that happens. This is why the typical HADR setup is two synchronous nodes in the primary datacenter, and one or more asynchronous nodes at remote sites.