Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Step-by-Step Guide to Clustering Windows 2000 and SQL Server 2000

By Brian Knight, (first published: 2002/07/12)

In my last article, I spoke extensively about the theory behind clustering a Windows 2000 and SQL Server machine. This week, I’ll detail how to actually accomplish this task in a step-by-step guide. In this guide, I’ll use Windows 2000 Advanced Server with SQL Server 2000 Enterprise to create an Active/Active cluster. These two software components are the minimum requirement in a 2000 environment to cluster SQL Server 2000. If you have not read my article on Clustering SQL Server 2000 from 500 Feet, I would recommend that you do so before moving forward since this guide will use terms that I assume you know from the last article.

Common question: One of the number one questions I received after writing this article was how do you do an Active/Passive cluster. Essentially, you can follow this guide to cluster your SQL Server but stop before you add the second SQL Server. That's all it takes!

Note: As with any type of configuration and installation, there are multiple ways of doing this. I will show you towards the end of this article how I configure clustering for optimal performance. This may not be the best method of optimizing things in your environment.

Tip: Before attempting clustering, I would recommend that you install service pack 2 for Windows 2000. There are a number of bug fixes between the two service packs related to clustering.

Goal #1 – Cluster Windows 2000 Advanced Server

Before we even install SQL Server, you will want to ensure that your Windows environment is successfully installed and clustered. Although it is possible to cluster SQL Server after it is installed, it makes it much easier if you have already have this piece complete. If you already have this part done, SQL Server will automatically detect that it is in a clustered environment when you try to install it. Before you begin, make sure all of your drives you plan to share are formatted. You will also want to ensure that your network is fully configured.

  1. Create an account for Microsoft Cluster Service (MSCS) to use. This account will need to be a domain account and be an administrator. Make sure you make the password non-expiring.
  2. Stop the second node that will be  in the cluster. This is only a precaution and is generally not required.
  3. Add the Cluster Service component. You can do this by going to Control Panel, clicking on Add/Remove Programs, and then clicking on Windows Components. Click on Cluster Service as shown below. Notice that some components of Internet Information Services (IIS) are also added when you click Cluster Services. This small component of IIS is required for Cluster Services to work properly.

Note: When you install Windows 2000, you can also check this box when choosing your components to install. After Windows boots up for the first time, you’ll be able to launch the wizard by going to Control Panel and click on Add/Remove Programs. The Configure Clustering option will be highlighted.

 

  1. When you click Next, this will start the Cluster Service Configuration Wizard. Click Next to continue in the wizard. This will take you to the Hardware Configuration screen. You will need to make sure that all the equipment in your cluster is on the Hardware Compatibility List (HCL). After looking at http://www.microsoft.com/hwtest/hcl to confirm this, click I Understand and then Next.
  2. You will then need to select that this is the first node in the cluster. By doing this, you’re creating a new cluster. The next node is much easier to configure because you’ll only have to select the last option and much of the configuration is read from the first node.

 

 

  1. You will now need to provide a name for your cluster. This name will be used by many Windows tools like Performance Monitor to designate the “floating” name for the cluster. If one machine fails in your cluster, this name will move to the surviving node. I’ve chosen the name CLUSTEREXAMPLE for this article.
  2. Next, specify the account we created in Step 1. This account will be used to start the Cluster services.
  3. After clicking Next, specify each of the shared drives that you’d like to cluster. Each drive you specify is then managed by MSCS. I would recommend that you allow all the shared drives to be managed my MSCS. Otherwise you risk corruption of the drives. By default, all the drives will be selected but if you wish to add more or remove some drives you can highlight the drives and click Add or Remove. After you’ve added all the drives you need, click Next.

 

           

  

  1. Specify your quorum drive. The Quorum drive holds log files and health information about your cluster. If one node fails, the surviving node depends on the quorum drive to start the services. For simplicity, I always call my quorum drive the Q drive. It should be about 815 MB in size typically, although much of that space will go unused. After selecting the drive, click Next.
  2. You now must configure the cluster’s network. Read the message about the types of networks that can be configured and click Next. Essentially, you can configure the cluster to communicate over a private network and/or a public network. I prefer to have the heartbeat connection over a private network for security and everything else go over the public network.
  3. Configure each network card. You will be presented a screen to configure each network connection. To use the each network connection, click the Enable This Network For Cluster Use and then select the type of use it will have. For example, if you want all traffic to go out on a single network card, select All Communication (mixed network). If you only configure you cluster for one connection, you will receive a warning stating that you now have a single-point of failure. Acknowledge the warning to proceed.
  4. The last step in this node is to specify a virtual IP address for the cluster. You will also need to specify the subnet for the IP address and which connection will be used by clients to connect to the cluster.

 

 

  1. You’re now done with the first node! Not too bad, is it? Now click Finish to complete the wizard and acknowledge that the service was successfully started. No reboot is required after installing a cluster.
  2. Boot up any servers that need to be in the cluster.
  3. Repeat the same steps on the second node to launch the wizard again. This time select the Second Node option as shown in Step 5. By selecting that option and specifying a security account for Windows to use, the wizard will read the configuration from the first node and almost automatically configure itself.

Goal #2 – Configure the Cluster

 By installing the cluster on both nodes, you have also created a new tool called the Cluster Administrator. With this tool, you can configure your cluster for optimal use. You can run the tool like you do most other tools in Windows 2000, by going to Control Panel and selecting Administrative Tools. When you first run Cluster Administrator, you’ll notice that the cluster is configured awkwardly. By default, each shared disk you assigned earlier resides in its own group under Groups.

I prefer to keep similar resources together. Drives that I see that will be primarily used by a SQL Server on Server1 will be grouped together and likewise for Server2’s drives. With that in mind, I create two new groups: Server1 and Server2. I then place the drives from each independent group into one of the two groups. You’ll also notice that there is a Cluster Group where the quorum drive resides. Leave this group alone.

In Cluster Administrator, if you get lost, right-click. For example to move the drive to one of the two groups right click on the resource and click Move to New Group. Then, specify which group you’d like to move to. Once you have all the drives except the quorum drive moved to one of the two new groups, delete the empty groups. You can then test a failover by right clicking on the group and selecting Move Group. You will then see the resources fail, go to Online Pending, then to Online on the new server. 

You may also want to configure the primary owner of each drive. You can do this by right-clicking on the resource and clicking Properties. Which group you place your resources in is very important. A resource can only act as a dependent on another resource that is in the same cluster group.

 

Goal #3 – Install SQL Server

So far, we’ve only clustered the drives but nothing more. Let’s make this a little interesting by adding some added resources like MS DTC and SQL Server. In installing SQL Server there are only a few additional steps that you may not be used to. Before we begin, make sure each of your nodes is online.  

  1. Install MS DTC separately. To install the clustered version of MS DTC, go to a command line and type comclust (it will be in your path). You will see the MS DTC resource created as shown below. This resource is located in the Cluster Group in Cluster Administrator. Perform the same action on the any other nodes in the cluster as well.  

 

  1. Start installing SQL Server 2000 as you would normally. The first unusual screen you will see is where you normally specify if you’re going to install locally or remotely. You now have an added option of a Virtual Server.

 

 

  1. After you step through the license agreement, you will be asked to specify an IP address and subnet for your virtual SQL Server name. Type the virtual IP address and the subnet will automatically fill itself in for you. Next, select the network connection to use then click Add. That will transfer the data to the bottom box.
  2. You will then need to specify the drives where the data files will be placed as shown below. You will only see drives that the node owns. If there is a drive that you don’t see in this list, go back to Cluster Administrator and move the group over to the server where you’re installing SQL Server. When click Back to go back a screen in the installation wizard then Next to refresh the list of drives. There is a bug in the installation wizard that you will need to watch carefully here. If you specify a drive here, the wizard pretty much ignores the drive selection and you will need to manually re-select the drive in a moment.

 

 

  1. In the Cluster Definition screen, you will need to specify which nodes SQL Server will be installed on.
  2. You then need to specify an administrative account for the cluster. Again, I would use the clustacct that we established in Step 1 of Goal 1.
  3. Specify an instance name. If this is the first SQL Server in the cluster, then you can make it the default instance. If this is the second instance in your cluster, you must use an instance name and a different virtual server name a few steps ago. There are special notes about this in my previous article.
  4. Confirm the directories and type of installation. Again, there is a bug in the wizard that will require you to possibly change the Data Files option again. The wizard will default to the first drive in the group so if you wish to specify a new drive, change it here.
  5. Continue the installation as you would normally in SQL Server. The rest of the installation is exactly like the normal installation path. The only difference is when SQL Server goes to copy the files, you cannot see the progress because it’s copying the files and configuring both nodes. This process may take up to 15 minutes so be patient. Don’t assume that nothing is happening on the server.
  6. Reboot both servers. Reboot the server where you installed SQL Server from first, followed by the second node.

After rebooting the servers you can install SQL Server on the second node. Install it exactly the same way but you’ll need to assign another virtual SQL Server name and IP address. You’ll also need to use and instance name for the second node. There are special notes in my previous article about using an instance name. I would recommend rebooting one more time after you’ve installed the second instance, although it is not required. After that, you're ready to install Service Pack 1 for SQL Server 2000.

 

Goal #4 – Configuring the Cluster for SQL Server

SQL Server is now running on both instances. If you’ve installed two instances of SQL Server (one on each node), then you’re running in Active/Active mode. You’re now ready to go to Cluster Administrator and perform the final configuration steps for SQL Server. By default, if you’re using multiple shared drives, SQL Server in a cluster will not be optimally configured.

To configure the resources properly, open Cluster Administrator and go to your first server’s group that you installed SQL Server on. If you’re running multiple data drives for a SQL Server, they will not be accessible until you make the SQL Server service a dependency on these drives. In other words, you can’t place backups or any data files on the M drive until the SQL Server service is set as dependency to the M drive. You wouldn’t want the SQL Server service starting until all your drives are ready. To make the SQL Server service dependent on the drives, you must first fail the service by right-clicking on the SQL Server service in Cluster Manager (shown below) and select Failover. You can then right-click on the SQL Server service here and select properties.

You will notice by going to the Dependencies tab that the service is only dependent on the primary data drive and the virtual SQL Server name. To add additional resources click Modify.

 

As you can see below, I’ve made the SQL Server service additionally dependent on the M and N drive by selecting the drives then clicking the à button. After you’ve added your resources click OK.

 

You will then need to restart the SQL Server service by right-clicking on the service and selecting Start. Now, all the drives you specified will be available to Enterprise Manager and T-SQL. To complete the configuration, you can perform the same action on the second node if necessary. You’ve now completed the basic clustering of SQL Server and Windows 2000! For additional help on this topic, you can post questions in the In The Enterprise forum. In future articles, I will discuss how to cluster Analysis Services, which is not nearly as simple as clustering SQL Server. 

 

 

Total article views: 152886 | Views in the last 30 days: 41
 
Related Articles
FORUM

findout the drive volumes which are clustered in a server

findout the drive volumes which are clustered in a server

FORUM

Installation of SQL Server 2005 on cluster returns error

Installation of SQL Server 2005 on cluster returns error

ARTICLE

Pre Check List for 64 BIT SQL Server 2008 Cluster Installation

My tips for cluster installation with Windows 2008 and SQL 2008 on a 64 BIT server which is clustere...

FORUM

Can we create database on clustered drives in sql server 2000 and sql server 2005

Can we create database on clustered drives in sql server 2000 and sql server 2005

FORUM

ERROR: INSTALL SQL SERVER 2000 on Cluster...

ERROR: INSTALL SQL SERVER 2000 on Cluster...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones