Few things in the world of the DBA create more anxiety than clustering a SQL Server, partly because clustering was difficult in SQL Server 7.0 and earlier releases. In SQL Server 2000, however, clustering is less intimidating. The six steps I outline in this article form a basic framework that you can follow to set up a clustered environment for SQL Server 2000.
Failover clustering is the most effective way to achieve high availability in a SQL Server environment. When you cluster any Windows servers, you use Microsoft Cluster service to link several servers together. With Cluster service, if a failure occurs in a crucial hardware component or in the SQL Server service, then the drives, SQL Server, and associated services will fail over to the secondary server. The entire failover is automatic and generally takes between 30 and 60 seconds. With other SQL Server high-availability solutions such as log shipping or replication, someone has to manually change the roles to the secondary server if a disaster strikes the main server. Although log shipping can provide a reliable redundancy solution, it relies on manual maintenance and can be difficult to set up.
Before you begin clustering, you need to understand that you use Windows clustering for high availability only. Clustering doesn't improve SQL Server performance because only one server works at a time—the linked servers don't process queries together. For an overview of how clustering fits into the overall SQL Server high-availability puzzle, see Michael Hotek's article "High Availability Solutions," page 20, InstantDoc ID 39986.
Basic Cluster Architecture
Figure 1 shows a simplified cluster architecture in which two Windows servers, known as nodes, share a disk array or storage area network (SAN). You can have as few as two nodes and as many as eight nodes in a cluster, depending on which versions and editions of SQL Server and Windows you're running. For example, a cluster running the 32-bit version of SQL Server can include as many as four nodes. Although some clusters can support two active nodes, the architecture that Figure 1 shows includes only one active node; the other node is passive—a standby server. Every few seconds, Cluster service runs a simple query against SQL Server to make sure that the standby server is still online. Client applications such as Enterprise Manager or an application that you develop connect to SQL Server through a virtual name or a virtual IP (VIP) address instead of through the real server name and IP address. When a failover occurs, the ownership of the VIP address moves to the standby server, so you don't have to change the connection string for your application to work.
You can choose between two types of clustering: single-instance clustering or multiple-instance clustering (known in SQL Server 7.0 as active/passive and active/active clustering, respectively). A single-instance configuration has only one SQL Server instance installed in the cluster of two or more nodes. A multiple-instance cluster has multiple SQL Server instances installed in the cluster—typically one instance on each node. When you choose to implement multiple-instance clustering, you must make sure that the server or servers participating in the cluster have enough processor power and RAM to support the workload of multiple instances of SQL Server.
In a single-instance cluster, you have to purchase a license for only the active node. The exception is when you have a per-processor SQL Server licensing agreement and you have more processors on the passive node or nodes than the active node. In such a case, you must license the additional processors. In a multiple-instance cluster, you must license all active nodes that have SQL Server installed.
Clustering Steps
To create a clustered environment for SQL Server 2000, you first need to cluster two or more Windows Server 2003, Windows 2000, or Windows NT servers. Then, you can install SQL Server 2000 on the cluster. In the example I walk through in this article, I show how to set up a two-node cluster on Win2K Enterprise Edition, but Windows 2003 Datacenter Server supports as many as four nodes, and Windows 2003 expands the number of nodes you can use to eight.
As I mentioned earlier, many DBAs and systems administrators are intimidated by the complexity of clustering. But you can put the process into perspective by breaking it into six high-level steps:
- Set up shared drives and networks to use for the cluster
- Create the cluster
- Configure the cluster
- Install SQL Server on the cluster
- Configure the cluster drives
- Install the necessary Windows and SQL Server service packs
Between each of these steps, you need to check the Windows Event Viewer to make sure no errors have cropped up. Serious problems that you might see include drive-related problems such as sharing-violation errors. After you've installed the cluster, you need to check the Windows system and application logs; sqlstpn.log, the SQL Server setup log, in which n in the filename represents a sequential number of setup attempts; and sqlclstr.log, the log of clustered SQL Servers. (The two SQL Server logs don't exist until you try to install SQL Server for the first time.) The combined information from these logs tells you what's going on in the cluster and whether any problems exist. You can also type SET CLUSTERLOG at a command prompt to see where the Cluster service cluster logs are located. Be sure to correct any errors at each step before proceeding to the next step, or the installation might fail. Now, let's walk through the six steps and see how to set up a two-node cluster for SQL Server.
1: Set Up Shared Drives and Networks
Setting up the drives for your clustered servers—and getting Windows to recognize the shared drives—is generally the most tedious part of creating a cluster because the process requires at least three reboots. To set up a shared drive, you can use either a SAN or a shared SCSI device. I usually choose a SAN because it lets me pool drive space across many servers and lets me easily add drives. After you begin the clustering process, you can't add space to an existing drive, so make sure you add plenty of space at setup time for each drive you want to cluster. Although you can add drives later, adding space to existing drives is difficult because clustering doesn't support dynamic drives (a type of drive configuration that typically allows this type of growth). To add space to an existing drive, you use the Diskpart utility, which is included in the Windows 2000 Resource Kit and is built into Windows 2003. But not all hardware vendors support the Diskpart utility, so creating adequate drive space at setup time is preferable to adding space later.
When setting up the drives on the first server (or node) in the cluster (which I call SQL2KNODE1), make sure you have the second server (SQL2KNODE2) powered off. To set up the drives on the first node, right-click My Computer, select Manage, open the Microsoft Management Console (MMC) Computer Management snap-in, and open the Disk Management tool, which you use for managing drives and disk partitions.
When you name your drives, try to use a consistent naming standard and name each drive according to its purpose. I typically choose names like those that Table 1 shows because a DBA or systems administrator can easily see what a drive's purpose is by looking at its name.
After you've formatted and labeled the drives for SQL2KNODE1, power off SQL2K- NODE1 and reboot SQL2KNODE2. Go back to the Computer Management snap-in's Disk Management tool; you'll see that all the drive letters are incorrect but the volume names are correct. Use the volume names to figure out what the drive letters should be, and reset the drive letters so that they match the letters on SQL2KNODE1. Now the drives for both servers are configured. Power off SQL2KNODE2 and power on SQL2K- NODE1.
You have several options for how to set up the cluster network from a simple single-point-of-failure configuration to a configuration that provides full redundancy. To learn about these options, see the Web-exclusive sidebar "Network Setup Options," InstantDoc ID 40038, at http://www.sqlmag.com. For this article, I chose the first option that the sidebar describes—using two network cards—for simplicity; but this configuration does leave a communications single point of failure. When you've chosen the setup you want, name each network communications connection based on its purpose. For example, I called my private communications connection LAN_PRIVATE and the public communications connection LAN_PUBLIC. Now that you've set up the shared drives and network, you're ready to create the cluster.
Prev. page
[1]
2
3
next page 
