Clustering SQL7 on Windows 2000

By Bill Graziano on 29 July 2001 | Tags: Clusters

I recently had the chance to install my first SQL Server 7.0 cluster. I thought I'd share some of the resources I found and lessons I learned.

Installing SQL Server on a cluster was much easier than I thought it would be. I hear it's even easier in SQL Server 2000. In it's current form a cluster is all about fault tolerance and doesn't have any load balancing. In essence you create a virtual server (or servers) that can run on any physical node in the cluster. Windows 2000 Advanced Server supports two physical nodes per cluster and Windows 2000 DataCenter Server supports four node physical cluster.

In our case we installed two virtual SQL Servers in an active/active configuration. Each of these virtual servers has it's own name and IP address and is accessed just like any other SQL Server. Each of the virtual servers can run on either of the physical nodes in the cluster.

SQL-Server-Performance.com has a good article titled An Introduction to SQL Server Clustering. It provides a good overview of clustering and how SQL Server behaves in that environment.

The Cluster

The first step is installing the cluster. This is detailed in the Step-by-Step Guide to Installing Cluster Service on Microsoft's web site. It walks you through installing the cluster key stroke by key stroke.

We found it easier if the Quorom drive was on a physical disk all by itself. We needed to run the Compaq utility to reconfigure the array to make this happen. This let us put two separate drives on the same array. The Compaq software called these logical drives and Windows 2000 saw them as separate physical disks. Later in the process we were able to create a cluster group that included the Quorom drive, MSDTC, the cluster name and cluster IP address. This group was separate from our two SQL Server groups.

SQL Server

This actually turned out to be easiest part of the process. You install SQL Server on each node and then use the Failover Wizard to "migrate" the installations to the cluster. All the gotchas are well documented by the articles I'll link to in this article.

Some Links

Brad has a great resource page on clustering at SQL-Server-Performance.com. Just about ever resource I found on clustering came from this page. It's the first place to look for links to good clustering resources.

How to Install SQL Server 7.0, Enterprise Edition on Microsoft Cluster Server: Step by Step Instructions is a white paper written by an MCS consultant and goes into great detail on installing SQL Server. It covers an active/passive installation but gives you enough information to install an active/active configuration. If you already know how to install SQL Server then much of it is review. They also have a Word version of this article.

Order of Installation for SQL Server 7.0 Clustering Setup is a product support document providing additional information about the process.

Another Microsoft support document is Microsoft Cluster Service Installation Resources. It provides links to a variety of other information about clustering including numerous SQL Server links.

SQLServerCentral.com has two good articles on clustering SQL Server 2000: Clustering SQL Server 2000 from 500 Feet and the Step-by-Step Guide to Clustering Windows 2000 and SQL Server 2000. I was studying these articles in depth until the client decided to use SQL7 rather than SQL2000. Free registration is required for both articles.

SQLTeam.com also has a section on clustering. We'll link to various articles on clustering as we find them or you submit them.

Notes from the Field

I sent out an email to a few people that I know run a cluster in production and have done so for a while. Here's the information I got back from JohnDeere:

I have found the biggest shortfall to be the lack of monitoring tools for Microsoft Cluster Services. There is no built in notification if a node fails, but at least on Win2K you can install the cluster admin tool on your local workstation so you don't have to go to the server room. Terminal services does not work in the cluster.

Be sure your backup strategy will work in a cluster and be sure it will work for Sql server. Our original strategy was Compaq's virtual replicator. It does not work in a cluster and I was never sure how these snapshots would work for SQL server. We switched to a Spectra 10000 4 drive AIT backup unit.

If your cluster is not fully redundant make sure you have the spare parts in stock or on a quick delivery time frame. Our cluster is not redundant but Compaq has all the parts on a 4 hour delivery time. (SAN controllers, SAN Switch, fiber cable, disk drives We have one hot spare but none on the shelf) The basic idea is to survive a CPU fault but if any of the shared disk pieces fail and you have no redundancy the whole cluster is down. (Management really does not like this)

Be sure to mention only start and stop services from the cluster administrator. Never start or stop SQL or its related services from enterprise manager, Sql service control or the services applet from control panel. The only way I have been able to recover from this is a reboot of both nodes on the cluster.

One of the biggest issues on our cluster is disk contention. You have 2 servers fighting for time on a single physical disk cabinet. In my cluster if I start 2 backups at the same time. The time to backup doubles or If we start a major import operation on node1 the performance of node2 suffers. (graz: I'm lucky. We have two physical disk cabinets.)

I would also mention hardware sizing (# of CPU's & RAM). Make sure one of your servers has enough horsepower to run both instances of SQL Server in the event of a fail over. In the 7 world this is a little different but in 2000 you are running 2 separate instances of mssql.exe and its associated programs.

If you cluster 2000 you must name the instances and change the port from 1433 on one of the instances. If you don't have MDAC 2.6 then you must alias the non-1433 server via client network utility