SQL Server 2000 Clustering Tips

Tip: If you are going to implement replication from a SQL Server 7.0 or 2000 clustered server, and the SQL Server cluster will participate as a Publisher and a Distributor, use a file share located on the cluster disk resource as the snapshot folder. This way, replication will failover when SQL Server fails over.

Explanation: When you configure a clustered SQL Server as a Distributor, SQL Server needs to have access to a snapshot folder as a temporary holding place during the replication process. In order to ensure that replication still works when failover occurs, this folder must be located on a shared folder on the cluster’s shared disk resource. If it is not, then when failover occurs, replication may stop working.

You will have to manually create the required folder on the shared disk resource, and additionally create the necessary share with appropriate permissions. In addition, you will have to configure the shared folder using Cluster administrator as a clustered shared folder.

If you don’t want to do the above, one option is to not make the clustered SQL Server a Distributor, but only a Publisher, and locate the Distributor on a non-clustered SQL Server. This way, the snapshot folder will exit on a non-clustered SQL Server, negating the above advice.

Version: 7.0, 2000

Date Added: 5-31-2001


Tip: You have heard this advice before, but it is so important I am repeating it here, be sure all of your clustering hardware is on the Windows 2000 Advanced Server Clustering Hardware Compatibility List (HCL).

Explanation: I can’t overemphasize the importance of ensuring your hardware is supported by Windows 2000 Advanced Server clustering. If it isn’t, not only will you have trouble getting support from Microsoft, the nature of the problems you probably will have will be excruciating painful and hard to troubleshoot. I know, I have been there.

What makes it difficult to ensure that all of your hardware is on the HCL is Microsoft’s poor organization of the HCL itself. It is often very difficult, and time consuming, to locate all the hardware on the list to see if it has been certified.

A further complication is that Microsoft certifies hardware two different ways. First, it certifies some hardware by specific part. Second, in other cases, hardware is certified as a total system (a specific combination of parts). And in some cases, hardware certified one way is not certified another way. I recommend that you try to find hardware that is certified as a total system, that way, you can ensure that the total system you are purchasing has been tested as a total system by Microsoft.

Other problems with the HCL is that it is often out of date, sometimes parts that are still currently available (but are older models) are not listed in the HCL, and sometimes one variation of a part is certified, but a slightly different variation of the same part is not certified. So be very careful when comparing part numbers to the HCL to be sure you and the HCL are talking about the exact same part, not some obscure variation.

Version: 7.0, 2000

Date Added: 6-4-2001


Tip: To ensure failover will work when needed, it is important to schedule manual failovers periodically in order to test if failover is really working.

Explanation: Just because your cluster has not had any problems you are aware of doesn’t mean that there are no problems. Periodically, at least every 60 days, you need to manually failover your cluster, and fail it back, to ensure failover is working as expected. This helps to ensure failover is working should it ever be needed for a real server failure.

Version: 7.0, 2000

Date Added: 6-6-2001


Tip: Don’t run the regrebld utility, which are used to rebuild SQL Server’s registry, on a clustered SQL Server.

Explanation: The SQL Server clustering wizard alters the registry entries of SQL Server when it is run. If you try to rebuild the registry while SQL Server is clustered, it will remove important registry entries, and most likely, SQL Server will stop functioning.

Version: 7.0, 2000

Date Added: 6-6-2001


Tip: Although SQL Server 2000 supports up to 16 instances of SQL Server, running more than one instance on any one node in a cluster will degrade performance.

Explanation: Installing, configuring, managing and performance tuning just one instance of SQL Server 2000 in a cluster is challenging enough. Don’t temp fate by running more than one instance of SQL Server 2000 per node in a cluster. If you do, not only will administrative headaches go up, but performance will suffer. Each instance of SQL Server has to share common hardware resources, increasing the likelihood of performance bottlenecks.

Version: 2000

Date Added: 6-26-2001



No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |