SQL 2000 Active/Active Cluster memory best practices

Question

SQL 2000 Active/Active Cluster memory best practices

justinvw

SSC Rookie

Points: 26
More actions
August 3, 2008 at 6:50 pm

#92283

Good day,
I need some assistance with a SQL 2000 Active /Active clustering Question.
Current Environment:
2 X HP RX4640 Itanium Servers
Windows 2003 Enterprise 64Bit edition
SQL 2000 Enterprise 64bit edition
16Gb memory each
SQL Databases resides on HP SAN (Database ± 240gb)
SQL Memory set to Dynamically configure (Min 0 and max 14022Mb)
Cluster config Active/Active
Total memory at 15.5Gb (SQL + OS)
My Question...
Is this the best practice configuration for this scenario?
We have had in the past problems with failover onto one node due to apparently memory problems (just joined the company so i don't know the exact error, but basically it did not failover as expected)
Regards
Justin
Enterprise Engineer

Viewing 4 posts - 1 through 3 (of 3 total)

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

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 1

you need to set the "max server memory" in sp_configure for each side. You must account for a fail-over. If an instance on one side is already using say - 14gig, the failed instance can only get 2gig after it fails (less paging). so, set the max memory with the idea that both instances will run on the same side at the same time. i would recommend 7gig max on each side.

Tim White

justinvw SSC Rookie Points: 26 More actions · Answer 2

Thanks Tim..

I agree with your suggestion after doing some more reading and finding the following MS best practice

http://msdn.microsoft.com/en-us/library/aa274529(SQL.80).aspx

However our third-party application specialist wants us to test a startup procedure to handle changing memory allowances in the event of fail-over on our Test SQL Nodes and possibly on our PROD Clusters... See recommendation below

#######################

We can configure a "startup procedure" on each OLTP SQL instance. This is a stored procedure which will fire automatically whenever either instance starts up. The logic in this procedure will change the memory allowances on both servers so that the combined maximum memory assigned to both instances is only 14GB (leaving 2GB for Windows after fail-over). For example, we might configure the i.PM OLTP instance to have 9GB and the CDR instance to have 5GB of RAM (this would be more appropriate than 7GB each from what I've seen of the Page Life Expectancy figures).

However, we don’t want the instances to be restricted to less than the full amount of memory during non-fail-over times so whenever we restart either instance and we ARE NOT in a fail-over state, we must then wait a minute or two (allowing time for the startup procedure to execute) and then manually readjust the memory on BOTH INSTANCES back to a maximum of 14GB each. Otherwise the servers will not be using the full amount of available memory during normal operation.

#########################

I don't really agree with the stored procedure scenario as i would rather have stability on a SQL Cluster in favor of performance. Does the stored procedure look like it will work. I will probably test it in a couple of weeks on our SQL Test cluster.

Regards

Justin

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 3

hmmm, interesting idea. I'd be very interesteed to know how the startup script determines what side of the cluster it's on and whether or not another instance is already running on the same side.

Tim White