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!