Scalability and High Availability of Microsoft SQL Server 2000 (Part 2)
February 27, 2003
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:
and for each instance:
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:
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.