I have looked around in the dba stack exchange and have not being able to find an answer to this one, so here it goes my concern. Hopefully somebody has done some research on it and can provide an authoritative answer.
We have an Active/Active SQL Server cluster running on 2 nodes. SQLA is the default node for one instance with the other (SQLB) as a fail-over and viceversa (SQLB default node for the other instance with SQLA as a fail-over). These servers are running on physical boxes with 64GB of RAM
We have to configure "Max Server Memory" and there are 2 theories around here
- First Theory: Follow Jonathan Kehayias pretty much universally accepted approach that, in a nutshell, goes like this "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM." This will leave each box with a Max Server Memory setting of 53 GB
- Second Theory: We need to cap Max Server Memory to a value that will allow for the secondary node to take care of the other instance if a failover occurs. This limits each instance to much smaller value (around 30 GB so you have 30 GB x 2 + 4GB for the OS = 64GB)
On a first look, the first theory would be desireable as it gives each instance a way larger memory space, but some people (me included) are not sure what happens if you fail over and then you have 2 instances on the same box, each with a Max Server Memory of 53 GB of RAM.
Do you know what is the way to go with this scenario? is any of these two approaches right? or do you have valid reasons that a third approach should be used instead? do you know of any serious blog-post or white-paper to back it up with evidence?
Thank you all!
Best Answer
Please note this is called multi instance cluster not active active actually. Your concern is correct about what would happen when both nodes are on same node, in that case you need a dynamic script would identify a failover and adjust SQL Server max server memory accordingly.
Let us focus on how to set max server memory correctly when both nodes are running on respective nodes for that please see my answer on SE thread What is Sensible way to calculate max server memory.
Now for scenario when failover happens and both instances are on same node. You have to use scripts from follwoing resources.
Use PowerShell Script via startup Agent Job to balance Memory between two instances on a cluster on a Failover
A similar thread talking about how to adjust memory for Multi Instance cluster
Now when both instances are on same node you would have to evenly distribute memory leaving enough for OS. In that scenario 29 G for each instance and 6 G for OS is what I would say would be good value. Because you have 2 instances running leaving few more gigs for OS would actually help.
Also note you should immediately come out of this situation as both SQL Server would be extremely slow.