1

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:

  1. 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?
  2. Does the secondary replica have to have the same amount of memory as the primary?
  • The answer to your second question depends on how much performance penalty you are willing to suffer in the event you failover to it. If you need equivalent performance in the event of a failover, then it needs to have the same amount of memory. If it's only doing read-only requests, then probably the same, but it's usage pattern will be different (depends on your read-only workload). – Jonathan Fite Apr 10 '17 at 12:28
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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