When using multi-instance failover clustering (used to be called active-active) in SQL Server 2005 and 2008, I'd like to know what you configure the min and max server memory to.
For a two-node multi-instance cluster (with two instances, one on each node), the best-practice is to configure min server memory to 40-45% on each instance so that when the failover occurs the two instances don't end up with radically unbalanced amounts of memory, because the new instance may apply undue memory pressure to the already-running instance.
Although this is a best practice, I'd like to hear from people who have this situation - what do you configure the memory settings for the various instances? Do you make them equal or do you prioritize by the workload running on each instance? Do you make use of WSRM? Do you dynamically alter the memory settings based on post-failover cluster configuration?
Thanks!
PS I don't need info on how memory management works, or why the best practices exist - I know all that - I'm interested in what people are doing for real.
PPS Note, I'm not interested in SQL 2000 - it's memory manager didn't respond to that kind of memory pressure so best practice was to set max server memory to 50% each instance. Yuk.
Just did this for a client. They had 16 Gigs on each node, with each instance using 8 Gigs. One instance was starving for more RAM, so we upped it to 12, with alerting setup to watch for a SQL restart and send out a message. In the event of a failover due to hardware failing they'd manually lower it until the hardware was fixed.
We have a few multi-instance clusters in our environment and it is different for us depending upon the purpose of the instance. In our prod-prod clusters we have 32Gb on each node. We reserve 4 for non-SQL (O/S, tape backup processes, anti-virus, etc.) and then halve the RAM that is left (32-4 = 28/2 = 14Gb per node.) These are the max, we set a min of 8Gb for each.
In our prod-test clusters we bias towards prod (obviously). Prod receives a max setting of 20Gb, Test has 8Gb. Mins remain the same at 8Gb.
These values may be adjusted if we are testing actively and want to ensure our setting match between test and prod. We schedule out such changes in advance.
We are also sure to grant lock pages in memory, though do not enable awe as these are 64-bit boxes all around.
In our case, we adjust memory automatically (based on an script) and based on memory consumption threshold we have for all instances. That way memory is used properly.