Cluster memory issue

I'm currently running 2 SQl Instance as an active/active
cluster.
Both servers are identical running windows 2000 Advance
server.
SQL Server Enterprise 2000 with settings exactly the same
on both servers,
8GB of memory, both servers are new builds, Windows sees
all the memory, boot.ini as /3gb /pae switches and SQL's
are awe configured.
Instance 1 running as default virtual server on node 1
Max Server memory 7406
SQL runs using 7.23GB of memory.
Instance 2 running as named instance of virtual server on
node 2
Max Server memory 7406
SQL runs using only 3.73 GB as max
How can I get node 2 to also use 7.23GB.
But I'm getting excessive paging so would use more memory
if it could get it.
First, you must use Performance monitor to get true memory settings. Task
manager will not report correct settings. If you are paging, you need to
back down on the max memory setting. I would recommend 6.5 GB for an 8GB
system as a starting point. You can adjust upwards until the system begins
to page then back down slightly. The server needs some memory for the OS
and some for non-sql apps (like the login console). Also, you really should
run these systems at about 3.5GB each so in case one fails over, you can
hold both on the same server. Right now, you may not be able to
successfully fail over since neither system has enough free memory to start
the other instance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray" <anonymous@.discussions.microsoft.com> wrote in message
news:27e1401c46401$a942d1d0$a501280a@.phx.gbl...
> I'm currently running 2 SQl Instance as an active/active
> cluster.
> Both servers are identical running windows 2000 Advance
> server.
> SQL Server Enterprise 2000 with settings exactly the same
> on both servers,
> 8GB of memory, both servers are new builds, Windows sees
> all the memory, boot.ini as /3gb /pae switches and SQL's
> are awe configured.
> Instance 1 running as default virtual server on node 1
> Max Server memory 7406
> SQL runs using 7.23GB of memory.
> Instance 2 running as named instance of virtual server on
> node 2
> Max Server memory 7406
> SQL runs using only 3.73 GB as max
> How can I get node 2 to also use 7.23GB.
> But I'm getting excessive paging so would use more memory
> if it could get it.
>
|||I agree with Geoff. The main reason that you are using SQL Server Failover Clustering is that High Availability is your highest priority. So, you do not want to compromise that. In this scenario, it is recommended
that each instance of SQL Server on either node use upto 3.5GB of memory.
Here is an example from " Microsoft SQL Server 2000 High Availability (MS Press) ISBN 0-7356-1920-4 " that will make this easier to understand.
Consider this example: you have a two-node cluster with three SQL Server instances. Instance 1, which currently resides on Node 1, has 7 GB of memory configured using AWE. Node 2 houses Instance 2, which
has 5 GB of memory allocated with AWE, and one instance that is not using any advanced options and is currently using 1.5 GB of memory. Each node individually has a total of 8 GB of physical memory. A
problem occurs on Node 1, causing a failover. The instance tries to restart on Node 2, but it cannot. You now have a failed set of SQL Server resources that cannot come online, causing an availability problem.
Why? Well, to use AWE, you need to guarantee that the memory you told SQL Server to use will be there. A failover is basically a stop and start on another server. In this case, you were already using 6.5 of the
available 8 GB of memory. 6.5 + 7 does not equal 8, and it does not matter how big your page file is. Your instance will not grab the memory for AWE. It might, however, start up, but it will only grab the amount of
memory that it can up to 2 GB . Now you are risking memory starvation of the operating system, and this can affect all instances.
So how do you prevent this? By planning, these are the types of scenarios you need to play out in your head when proposing the number of instances on a cluster. Your management might see a two-node cluster
and say, "Hey, why is this second server doing nothing? Get something on there!", leaving you in the state of the previous example. The way you can balance memory in this case would be to give two
instances that need a fixed amount of memory 3 GB each and let the other one be dynamic (or set it to 1 GB or 1.5 GB to allow room for the operating system).If you cannot live with this, you will need another cluster
or set of servers to handle this workload. The recommendation was basically to halve the memory,which might lead some to believe that you are wasting resources, but again, is performance or availability your
goal? By definition, if you are reading this book and implementing things like a cluster, you are probably saying that availability is your highest priority. If you never have to failover and you left the instances at 7
GB, 5 GB, and dynamic, things would work great. But once a failover happened, all bets would be off.
Additional Information
=======================
-- In your case, you do not need to use /3GB. AWE and /PAE should be sufficient.
-- When starting with both the /PAE and the /3GB switches, the system may not start
http://support.microsoft.com/?kbid=817566
HOW TO: Configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/?kbid=274750
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/SQL/techinf...vercluster.asp
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Microsoft Webcasts
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
Q243218 INF: Installation Order for SQL 2000 Enterprise Edition
http://support.microsoft.com/?kbid=243218
Q260758 - INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?kbid=260758
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx