Scalability and High Availability of Microsoft SQL Server 2000 (Part 2)

SQL Server 2000 is tightly integrated with the clustering software. For example, SQL Server service can be stopped from SQL Administrative tools (like Enterprise Manager or Service Manager), rather than from the Cluster Administrator. The best example of this integration though is the setup process. Prior to launching it, make sure that each cluster member is fully operational. SQL server binaries are stored on the non-clustered drives, so make sure that there is sufficient amount of disk space on each server. In order to perform the setup, in addition to standard information (not cluster specific, such as authentication mode, components to install, or collation settings) you will also need the following:

  • Domain account with sufficient permissions on cluster nodes (needs to be a member of local Administrators group on each node) and its password
  • SQL Server (and SQL Server Agent, if not the same) service account name (needs to be a member of local Administrators group on each node) and its password

and for each instance:

  • Instance Name (with the exception of default instance, which uses the same name as the virtual server to which it belongs) of up to 16 characters in length
  • IP Address (unique on the network)
  • Port for TCP/IP sockets network library connections (default instance uses by default port 1433, while named instances have the port numbers dynamically assigned. This might need to be set manually if clients connect to the server via a firewall.)
  • Network Name of virtual server (note that this name can not be used on the network, which means that you can not create a virtual server first and then install SQL Server on it. Clustered SQL Server setup will automatically create this virtual server with associated IP address).
  • At least one and preferably two or more (so you can evenly distribute the disk utilization among more heavily utilized databases and transaction logs), physical disk resources. (Ensure that the node on which you are running the setup owns this resource).

First, you will need to install the Distributed Transaction Coordinator, which is a required component for the clustered SQL Server 2000. To accomplish this, log on to each node with an administrative account and from the Command Prompt run the program COMCLUST.EXE. You should receive confirmation stating that the Setup has successfully populated configuration information to allow MS DTC to run on the local cluster node. Remember to repeat this step on each of the nodes that will participate in the SQL Server 2000 cluster. You can verify the successful outcome of this procedure by checking the list of resources displayed in the Cluster Administrator. The list should contain MSDTC resource.

You should also stop all unnecessary services on each of the nodes participating on the cluster. This applies especially to anti-virus software. In addition, place the MSDTC resource in the offline state for the duration of the installation using Cluster Administrator.

At this point, you are ready to start the installation. Log on to the node from which you will run the setup with the administrative account. Launch the setup program from the SQL Server 2000 Enterprise Edition CD. The setup runs in the form of the wizard, which greatly simplifies the installation. The setup will detect that it is running on a cluster node and provide an option of installing a SQL Server instance on a virtual server, for which you will need to provide a name. As mentioned before, this name can not exist on the network. Next, (after providing standard licensing information and accepting licensing agreement), you will be prompted for additional information necessary to create this virtual server:

  • IP address and cluster network (this should be the public, not private, cluster network)
  • Cluster disk where data files will be placed
  • Nodes that will participate in SQL Server clustering (on the Cluster Management page)
  • Name
  • Domain
  • Password of the account with administrative rights to both nodes
  • SQL server instance (default or named)
  • setup type (typical, minimum, or custom, for which you would specify list of components to install).

Note that while the destination folder for the data files will be on the shared drive you selected earlier; the destination folder for Program Files will be on your local drive. However, running setup separately on each node is not required. Instead, all nodes that were listed as Configured on the Cluster Management page of the wizard (mentioned earlier as part of the setup process) will automatically have SQL Server binaries installed on their local drives (removing SQL server installation files from a node is also done from Cluster Management page by excluding it from Cluster Definition). You will need to type in the name, domain, and password of the account to be used for SQL Server and SQL Server Agent services. Choose between Windows and Mixed authentication mode (if you choose mixed, you will also need to provide a password for sa SQL Server login). Specify the collation settings, network libraries used for connecting to the server (Named Pipes and TCP/IP Sockets via port 1433 by default), and licensing mode (per seat or per processor). After all this information is provided, the setup will install Distributed Transaction Coordinator (unless it has not been already installed, since, as you might recall, there can be only one instance of DTC per cluster).

In order to perform configuration changes to an existing clustered SQL server, you need to use the same setup program. In this case, though, when prompted for the computer name on the initial screen of the setup wizard, make sure that the Virtual Server option is selected and type in the name of existing Virtual SQL Server. This will allow you to upgrade, remove, or add components, modify IP address information, and add or remove nodes on which SQL Server is installed.

You should follow the setup of SQL Server 2000 with an installation of the latest SQL Server service pack (as the overwhelming impact of SQL Worm indicates, this seems to be frequently neglected). Prior to running, make sure that all cluster nodes are operational. SQL Server 2000 service pack is cluster aware (just like the setup program) and will detect the presence of the Virtual Server (or servers) and offer the upgrade. You will need to provide the administrative account information needed to access each of the cluster nodes. The upgrade will automatically be applied to all of the nodes.

As you can see, the installation of the clustered SQL Server 2000 is slightly more complex than a non-clustered one, however by following steps outlined in this article you should be able to avoid any potential problems. In the next article of this series, I’ll continue exploring the topic of SQL Server scalability by looking into features of federated servers.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles