WARNING: Windows 2000 and SQL Server 2000 clustering is an advanced topic, and you should have a good understanding of clustering before using any of these tips.
Don’t implement any of these tips into a production environment until you have successfully tested them in a test environment. Note that most of these tips are specific to SQL Server 2000.
Tip: Don’t turn off or on the mssqlserver, sqlserveragent, or the msdtc services off or on using any other tool other than Cluster Administrator, Enterprise Manager or Service Manager when SQL Server has been clustered. That means don’t use Services or other methods to turn these services off or on.
Explanation: If you don’t follow this rule, you could corrupt your SQL Server cluster installation and have to reinstall it.
In addition, be sure that “Autostart SQL Server,” “Autostart SQL Server Agent,” and “Autostart MSDTC” are all unselected in the “General” tab of the SQL Server “Properties” screen, and that “Auto restart SQL Server if it stops unexpectedly” and “Auto restart SQL Server Agent if it stops unexpectedly” are both unselected in the “Advanced” tab of the SQL Server Agent “Properties” screen.
Version: 2000 (Not SQL Server 7.0. See 7.0 cluster tips page for more info.)
Date Updated: 7-30-2002
Tip: For optimum performance when clustering SQL Server 2000, configure an active/passive failover configuration, not an active/active failover configuration.
Explanation: In an active/passive failover configuration, there is only one instance of SQL Server running on the cluster. So if a failover should occur, the secondary server will take over once the primary server becomes unavailable. But in an active/active failover configuration, there are two instances of SQL Server running, one on each node. If one of the nodes should fail, then the remaining node will have to run two instances of SQL Server, not just one. As you can imagine, a SQL Server running two instances of SQL Server, instead just one, will experience performance degradation.
Of course, if you are running an active/active failover configuration, and each instance of SQL Server is not overly taxed, and if any single physical SQL Server can handle both instances of SQL Server, then an active/active failover configuration may be OK. But I doubt that you will see many real world examples of very expensive active/active failover configurations running little used SQL Server applications.
Date Added: 5-31-2001
Tip: If you are clustering SQL Server 2000, and are using the Windows 2000 Address Windowing Extensions (AWE) in order to support 3GB or more of RAM in each node of the cluster, then each node of the cluster must have the exact same amount or RAM.
Explanation: If each node in the cluster does not have the exact amount of RAM, during a failover, SQL Server may fail to start on the failover server.
Date Added: 5-31-2001
Tip: If you are running SQL Server in an active/active cluster, you may want to manually configure how much memory each instance of SQL Server can use, so that should a failover occur, both instances of SQL Server will have the ability to grab the amount of RAM it needs to run efficiently.
Explanation: Here’s why. If node A and node B both have 2GB of RAM each, and each instance of SQL Server is allowed to use all the RAM in the server (dynamic memory is used), then if node A should fail, and that instance of SQL Server fails over to node B, the failed over instance may not have access to all the RAM it needs to run adequately because the instance of SQL Server running on node B is using all of it. The instance on SQL Server running on node B will not automatically give up RAM to the other instance in a timely manner. It will give it up eventually, but not quickly, which means that the failed over instance may run very slow for awhile.
Also, if you have manually specified in each instance that SQL Server has access to all 2GB of RAM (not dynamic memory allocation), should a failover occur, then the failed over instance will not be able to get any of the RAM it needs, and the failed over instance won’t event start.
To prevent either one of these scenarios, the ideal solution in this case would be to manually allocate 1GB to each server instance. This way, should a failover occur, then both instances will have access to 1GB of RAM to run, and performance immediately after failover won’t suffer much.
But you say, don’t these options be waste an entire 1GB of RAM in each server? And the answer is yes. This extra available 1GB in each server will not be used, except under failover conditions. This is just one of the drawbacks of using an active/active SQL Server cluster.
Version: 7.0, 2000
Date Added: 12-12-2003
Tip: If you are running multiple instances of SQL Server 2000 on a SQL Server cluster, be sure that the total amount of memory allocated to each instance of SQL Server is less than the total amount of physical RAM in the server.
Explanation: Typically, when running multiple instances of SQL Server 2000 on clustered servers, you use the Total Max Server Memory server setting to tell each instance of SQL Server how much RAM it can use. The total of this setting, for each instance of SQL Server, must be less than the total amount of physical RAM in the server. If it is not, at the very worst, one or more of the instances of SQL Server will not start, and at the very best, the server will experience heavy paging and performance will degrade significantly.
Date Added: 5-31-2001