I am looking for some recommendations for memory sizing and options for a SQL 2000 Cluster. This is a two node cluster built on Windows 2003 ENT SP1 (x86). Both the nodes have the following hardware:

- 4 x Dual Core AMD Processors

- 16 GB Memory

- EMC Shared Disk

We are running six SQL 2000 instances and don't expect each of these instances to use more than 1.7 GB of memory. All these instances are going to support BizTalk 2004 Databases. I already have /PAE enabled on the nodes. I am looking for the following answers:

- Do I need to enable AWE on all the instances even if the instances Currently, we don't have that enabled and we have seen some issues regarding excessive paging even when there is physical memory available. The DBAs think that we don't need to enable AWE. I am bit confused on this one.

- We normally run 3 instances on each node and would like size the cluster in such a way that it can take six instances in case of a node failure

Any input will be highly appreciated.

Re: Memory Sizing for a SQL 2000 Cluster - Windows 2003 Enterprise SP1

Ajmer Dhariwal

About 1.6 - 1.7 GB is the default max SQL can use for its buffer cache anyway, without having /3GB or AWE enabled (theoretical limit is 2GB). I wouldn't advise enabling /3GB as this will prevent the OS from seeing more than 16GB of memory, should you choose to add more.

So if you're happy with SQL not using more than 1.7 GB, then you don't need to do anything, which is why your dbas may be saying they don't need to enable AWE.

If you're getting performance issues and you want to use more memory, than you will need to enable AWE, at which point you must limit memory usage on each instance that is AWE enabled, otherwise 'unlimited' instances will gobble up all memory, and starve the other instances of memory:

In a failover situation you'll have 6 servers sharing 14 GB (leave at least 2 GB for the OS) so you'll need to make a decision (based on user reqs/SLAs etc) on whether your typical max server memory settings are based on an optimum scenario (3 servers sharing 14GB) or the failover scenario.

It's not just memory you need to be looking at. You'll typically have 3 instances sharing 8 processors (assuming hyperthreading is not enabled). You'll need to ensure they're not competing for the same CPUs, so explore CPU affinity as well.

Re: Memory Sizing for a SQL 2000 Cluster - Windows 2003 Enterprise SP1


I have done some more work with the DBA team today and we have seen some memory pressures on one of the busier instances. We are now going to up the memory in the nodes to 32 GB and then set the max/min memory on each instance with AWE enabled.

Thanks for your feedback on this.

Re: Memory Sizing for a SQL 2000 Cluster - Windows 2003 Enterprise SP1


What necessitates multiple instances Also, what type of cluster are you running Active/Active or Active/Passive

Regardless, other than the instances required for your cluster why not simply use multiple databases on a single instance and take real advantage of the RAM I can understand if you need a DEV & Test environment and this is just a box for that purpose. But in production what necessitates the need for multiple (6 in your case) instances

I ask as I am curious Obviously, there may be certain circumstances where you might need multiple instances but this would not be the norm nor is it a recommended configuration for a production database server. I have seen many well managed SQL servers (managed by highly skilled DBA's, not the make pretend SQL Server DBA's) that run over 300-400 databsaes or more. Some in cluster configurations and with less RAM than you have and some with high speed disks (fiber) that support millions upon millions of transactions daily.

So again, why the need for multiple instances as opposed to multiple databases on the same instance

Re: Memory Sizing for a SQL 2000 Cluster - Windows 2003 Enterprise SP1


Hi there!

if you are splitting up the instances 3 each on each node then you should set the memery for each SQL Instance to < 2gb. You do not need to set AWE if you are staying less than 3 gb for each instance. Also, set the the min memory to 0 and max memory to 2gb for each instance.

Hope this helps.