SQL Server 2000 is a robust and complicated product, especially when you are using it in a clustered setup. Properly deploying and managing it requires a reliable testing environment, but that can be costly. An economical alternative is to build a clustered SQL 2000 testing environment using VMware.

I’m going to walk you through the process of installing SQL Server in a VMware configuration. I’ll begin by explaining how SQL Server works with clustering. Then, I’ll give you a tutorial on the installation process.

VMware preparation

To get the most out of this article, you should read these previous articles on setting up a testing and clustering environment with VMware:

Designing a SQL Server cluster
When you design a SQL Server cluster, you can choose one of the following configurations:

  • Active/active configuration
  • Active/passive configuration

Let’s take a look at the differences between these two.

Active/active configuration
An active/active configuration (Figure A) allows you to have multiple instances of SQL Server running on both nodes of a two-way cluster. If one of the SQL Servers in the cluster fails, the failed instances of SQL Server will automatically fail over to the other server. This means that both instances of SQL Server will be running on one physical server, instead of two separate servers. An active/active cluster can have up to four nodes and supports up to 16 named instances.

Figure A

Active/passive configuration
An active/passive configuration allows you to have a single instance of SQL Server running on one of the physical servers in the cluster. The other nodes in the cluster are in standby mode until a failure on the active node or a manual failover during maintenance occurs. Only one SQL Server 2000 virtual server is installed on an active/passive SQL Server cluster environment.

Named Instances
One of the coolest features of SQL Server 2000 is its ability to use named instances, which allow you to run multiple installs of SQL Server on the same computer. With SQL Server 2000, you have two types of named instances:

  • Default instance
  • Named instance

Default instance
The default instance is basically the standard install of SQL Server. Prior to SQL Server 2000, when you installed SQL Server 7.0 or earlier, you were installing the default instance. You can have only one default instance per server. For example, if you have a two-node cluster, and you install the default instance on node 1, you must install a named instance of SQL Server 2000 on node 2 for clustering to work properly. Remember: Only one default instance per server. If you installed a default instance on node 1 and a default instance on node 2, a failover wouldn’t work because two default instances can’t reside on the same server. (A cluster acts as a single virtual server.)

Named instance
The named instance uses the initial name of the default instance plus a secondary SQL Server name. For example, suppose that I want to create a default instance called SQL2K and a named instance called SQLTECH. I would initially install my default instance with the name of SQL2K, but the named instance would look like SQL2K\SQLTECH. Each installation of a named instance gets it own system and user databases, SQL Server Agent, and registry keys.

Configuring MS DTC
Before installing SQL Server 2000 on your cluster, you must install MS DTC on all nodes. A utility is included in the clustering installation that allows you to install this quickly. Simply open a command prompt and type comclust.exe, as I’ve done in Figure B. This will add the MS DTC resource to the Quorum group.

Figure B

Once you have run the comclust.exe command on all nodes, you can verify that the MS DTC resource was created by looking at the cluster group on each node. Next, verify that you have a MS DTC resource in there and that it shows a status of Online.

Installing SQL 2000 in a VMware Cluster
Now you’re ready to install SQL Server 2000. The first step is to insert the SQL Server Enterprise Edition CD. When the first screen appears, choose SQL Server 2000 Components.

Author’s note

To install SQL Server clustering, you need to have SQL Server Enterprise Edition or Developer Edition.

Next, choose Install Database Server. The SQL Server installation will begin with a Welcome Screen. Click Next to continue. Assuming you already have server clustering installed, you’ll have the option of naming your Virtual SQL Server. Simply create a name and choose the Virtual Server option (Figure C). Click Next.

Figure C

Click Next and then enter your name and company. Click Next again, read your licensing agreement, and click Yes to accept the terms and continue to the screen shown in Figure D. Here, you will supply a virtual IP address, choose your (Internet) network connection, and click Add. Then, choose an internal network connection, supply another IP address, and click Add. Your internal connection is used for your clustering heartbeat.

Figure D

In the Cluster Disk Selection screen, shown in Figure E, choose the disk on your disk array that will have SQL Server 2000 installed. My disk array is an example of an active/passive installation. I will be installing SQL Server 2000 on Disk Z. If I had an active/active configuration, I would have an additional disk on which to install a named instance of SQL Server 2000.

Figure E

After you choose your Cluster Disk Selection, you can define your cluster nodes in the screen shown in Figure F. As you can see, in this example I have a two-clustered node, so I have two clusters available.

Figure F

Click Next to advance to the Remote Information screen, where you’ll enter the appropriate credentials to connect to the nodes within the cluster. Simply enter a username that has administrative access to both machines. For a cluster to work in a domain, you must create a domain user account. During the installation of clustering, this account is given local administrator rights to each node as well. You can either use the account created when you installed clustering or specify a Domain Admin account with local administrative rights to each node of the cluster.

After you enter the appropriate credentials, you can choose whether this installation of SQL Server 2000 will be the default instance or a named instance. Remember, you can have only one default instance per server. If this will be your default instance, simply select the Default check box and click Next. If this will be a named instance, type the instance name and click Next.

Once you choose your instance, you can choose a Typical, Minimum, or Custom installation (Figure G).

Figure G

  • Choose Typical Installation if you want the generic install of SQL Server.
  • Choose Minimum installation if you have only a small amount of disk space.
  • Choose Custom installation if you want to define your options. For the purposes of this article, I chose to do a custom installation.

The next screen allows you to select your options. You can scroll through the Components list (Figure H) and add the ones you want. Most of the time you will accept the default options unless you want code examples.

Figure H

Click Next to continue. Now, you will need to provide SQL Server with an account to run the MSSQL Service and the SQLServerAgent service. This must be a domain account that has administrator access. Next, you will choose the appropriate Authentication method:

  • Choose Windows Authentication mode if you want to assign users to the database from within Active Directory. If you choose this method, you have to create users on the domain before you give them access to SQL Server 2000.
  • Choose mixed mode if you want to assign SQL Server logins outside of the Active Directory.

Now, choose your Collation Settings (Figure I) and default Network Libraries (Figure J).

Figure I

Figure J

You can keep the defaults or choose the collation and network libraries that are applicable to your needs. At this point, SQL Server is ready to begin installing on your cluster but will first prompt you to select a SQL Server Licensing mode (Figure K). Choose Per Seat if you have a Client Access License (CAL) for each device that connects to your SQL Server. Choose Processor License if you prefer licensing each processor on your SQL Server.

Figure K

After choosing the appropriate mode, click Continue to begin the installation. The next window informs you that this installation will take a few minutes. Typically, this installation takes longer than a few minutes, so be patient. When the installation is complete, click Finish to complete the process.

Verifying and testing the installation
Now that you have completed the installation, you can test to verify that it’s been configured properly. Your first step is to open Cluster Administrator and make sure that the following resources have been created:

  • SQL IP Address
  • SQL Network Name
  • SQL Server
  • SQL Server Agent
  • SQL Server Fulltext

Then, you can test the failover to each node by moving the SQL Group or right-clicking on a SQL Server resource and initiating a failover by clicking Take Offline (Figure L). You must initiate the failover three times before it will automatically fail over.

Figure L

Installing on multiple nodes
If you are running SQL Server 2000 in an active/active configuration, you must install SQL Server 2000 on the second node once you finish installing SQL Server 2000 on the first node. However, you must create a named instance of SQL Server on the second node because SQL Server is allowed to have only one default instance per server (and remember that a cluster is seen as a single virtual server).

Also, during the installation, you will be required to choose a disk on the disk array to install the named instance of SQL Server 2000. Choose a disk that is different from the first disk you used to install SQL Server 2000 on the first node. You cannot share the same disk when configuring an active/active configuration.

Test away
You should now have a working clustering environment for all of your testing needs. Moving forward, you can also use many of these same principles to add more shared disks to cluster IIS, File and Print Services, and Exchange Server 2000 in your testing environment.

�