Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer
    
So they were setting a max memory on 2005? –  Paul Randal Jun 17 '09 at 3:18
    
Yeah, they had max at 8 for each instance (actually 7 I think to leave room for Windows). –  mrdenny Jun 17 '09 at 3:50

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.

share|improve this answer
    
But that means that each instance can't use more than 14 out of 28GB on a node even when there's no failover? Or do you run multiple instances/node even when there's no failover? Thanks Tim –  Paul Randal Jun 17 '09 at 4:03
    
Should have clarified. These are our settings during a failover situation. We utilize the 28 on each node when running 1:1 instance:node. –  Tim Ford Jun 25 '09 at 15:52

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.

share|improve this answer
    
How exactly? If OP can't easily duplicate what you do, you are just opening for more questions. –  vonbrand Mar 6 '13 at 0:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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