SQL Server 2005 Failover Clustering - Memory Allocation

Question

SQL Server 2005 Failover Clustering - Memory Allocation

distill

SSC Enthusiast

Points: 181
More actions
June 10, 2009 at 4:01 pm

#210837

Hi,
We have decided to add a new node to our existing SQL 2005 cluster running on Windows Server 2008 to reallocate resources and split services from the other Active node.
in out 2 node Active Active cluster each node acts as a failover to the other (ie node 1 runs SQL database services which can be failed over to node 2, node 2 runs Analysis Services which can be failed over to node1)
I have been researching how to reset the memory allocation to the services running in case of failover and really have just drawn blanks. I have read the white paper on SQL 2005 Failover clustering and optimising performance for failover clustering which both recommend setting a min/max memory usage.
Using min/max setting would work my question is; When you set a min/max setting for the passive instance (when running in normal operation) does this instance always use the max memory allocated to it or will it use the minimum memory until it is active?
In this way i hope to better utilise the memory on the Active servers as the uptime is 99.4% so it doesn't really make sense to allocate a large amount of memory too a passive instance which is only going to be used 0.6% of the time
Thanks for your input
Regards,
Tristan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

distill (6/10/2009)
Using min/max setting would work my question is; When you set a min/max setting for the passive instance (when running in normal operation) does this instance always use the max memory allocated to it or will it use the minimum memory until it is active?

Hey Tristan,

The simple answer is that, in general, SQL Server will not automatically commit memory to either the min or max settings on start-up. The server will commit memory for the buffer pool as it is required to service user queries. Once the min setting is reached through normal activity, the server will not release memory below this setting.

The complex answer depends on which edition you are using, whether it is 32 or 64 bit, and whether AWE and/or lock pages in memory is set. There is also a trace flag which can modify the behaviour.

The simple answer stands however.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mobasha SSCertifiable Points: 5334 More actions · Answer 2

can u point for the drowbacks when configuring AWE and min, max memory in active active cluster...

what will happen in case of failover?

..>>..

MobashA

distill SSC Enthusiast Points: 181 More actions · Answer 3

Thanks Paul, since your post i looked into (at great length) a memory configuration that suited our setup.

x64 bit Enterprise edition of SQL Server 2005 and Windows Server 2008

Lock Pages in Memory for the SQL Service account

Multiple instances installed on each node, memory configured to allow failover but at a degraded service in failover situation

Thanks for the hints

Paul White SSC Guru Points: 150467 More actions · Answer 4

You're welcome 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

pramod173 SSC Journeyman Points: 79 More actions · Answer 5

Hi,

I have a 2 node SQL Cluster setup and have Active/Active mode. Basically one instance on each node. The OS is Windows Server 2003 64 Bit Enterprise Edition. And SQL Server 2005 Enterprise Edition. The max memory on each node is 48 GB. I want to know the best way to configure the memory. Want to know what does AWE signifies and should I set up the min and max memory limits. If yes then what will be the best values keeping in mind the failover and the total memory in each node.

Thanks.