Set Up a SQL Server 2008 Cluster

Assuming you read my previous article, "Planning and Implementing a SQL Server Cluster," December 2008, you know the overall process of preparing for and installing a SQL Server cluster. If you intend to set up a cluster in a SQL Server 2008 environment, you may not realize that the process of installing a clustered SQL Server configuration has changed dramatically in SQL Server 2008. I'll help you navigate that process by giving a brief overview of clustering, then guiding you through the steps needed to set up clustering for a SQL Server 2008 system running under Windows Server 2008. Note that this article assumes that Server 2008 has been installed in full mode and not Server Core mode. Configuring a server for clustering in core mode is more difficult because it's all done from the command line, which is beyond the scope of this article.

How Clustering Works

Failover clustering is a fairly basic concept. A service runs on each cluster node looking to see whether the SQL Server service is running on any of the nodes (physical servers). If the SQL Server service isn't running on any of the nodes, then it is started on one of the nodes. What's actually happening under the hood during this process is a bit more complex. When you install SQL Server in a clustered configuration (SQL Server must be installed using the cluster installation tool, as there's no way to change a traditional single-server installation into a clustered installation), the services are installed on all the cluster nodes. If you open the Services applet in the Administrative Tools menu on any of the nodes, you'll see all the SQL Server services listed with a start-up type of manual. When you move the running service from one node to another, the services are stopped on the original node and started on another node within the cluster.

When the machine running SQL Server goes offline in the event of a hardware failure, the Cluster service on the passive node will detect that the SQL Server has gone offline, take ownership of the SQL Server service and other resources in the resource group (e.g., IP address, network name, disk drives), and bring the service back online. With SQL Server 2000, this process can take several seconds to several minutes, depending on how much data is in the transaction log to be rolled forward and back. SQL Server 2005 Enterprise Edition and SQL Server 2008 Enterprise Edition start up much faster because they roll forward any completed transactions, bring the database online, then roll back any completed transactions. The other editions of SQL Server 2008 and 2005 bring the database online after the transactions have been rolled backward and forward.

Clustered SQL Server 2008 servers have many uptime advantages over previous versions and standalone servers. This includes the ability to keep a SQL Server system running even after a hardware failure. A new feature in SQL Server 2008 is the ability to do rolling upgrades. This lets you patch each node of the SQL Server cluster independently of the others without taking the SQL Server down for several minutes or up to an hour, depending on how long the patch takes to install.

Hardware and Software Requirements

Clustering your SQL Server environment requires two servers as well as shared storage. Although the servers don't need to be identical, configuration is easier if they are. The servers should at least have the same number of CPUs and the same amount of RAM. It technically isn't necessary for even the CPU count and RAM to match, but matching these counts makes it easier to estimate the amount of load the backup server can take. Using matched servers also gives you the luxury of not having to worry about which server the database is running on because both servers will perform the same, provided that the backup server has no other tasks.

The storage can't be the standard RAID array that you plug into the server. Instead it must be an array designed specifically to be used in a cluster. Although Windows Server 2003 still supports using SCSI arrays for clustering, Server 2008 has removed support for using a SCSI array for clustering your servers. When using Server 2008 to cluster your SQL Server systems, you'll need to use a Fibre Channel RAID array, a Fibre Channel SAN, or an array that can present its disks over iSCSI to the servers. This can present a problem when trying to cluster under Hyper-V or VMware if you don't have a hardware-based iSCSI solution. However, you can use Rocket Division Software's StarWind iSCSI Target for Microsoft Windows, Nimbus Data Systems' MySAN, or the open source Openfiler to present local disks from a third server as iSCSI disks to your cluster. You should use caution when using a software iSCSI target as performance from these software packages will depend on the hardware behind them and the network configuration.

Like SQL Server 2005, SQL Server 2008 supports clustering using both the Standard and Enterprise editions. The Standard edition supports a two-node cluster, and the Enterprise edition supports the maximum number of nodes that the OS supports. Windows 2003 Enterprise supports up to eight nodes in the cluster. Server 2008 supports up to 16 nodes when using the x86 or x64 platforms; when using Itanium processors, Server 2008 supports up to eight nodes in the cluster.

When Server 2008 was released, included in the clustering configuration were some exciting new features that greatly increase the flexibility you can obtain when clustering services. The biggest changes are that you can now use DHCP for clustering and use IP addresses from different subnets for each side of the cluster. The ability to use IP addresses from different subnets is most useful for creating multisite clusters, called geographically distributed clusters.

Planning the Cluster

As with any other database, you'll want your data files, log files, and tempdb to be on different volumes within the server and in different RAID groups within the SAN. You don't want the high writes of tempdb and logs to interfere with the database reads. When dealing with very large databases, you might find that 26 drive letters just aren't enough. You might want to look into using mount points within your drive letter to assign additional drive space and I/O capacity to the drive. For more information about mount points, see the web-exclusive sidebar "Creating Mount Points". Note that if you'll install more than one SQL Server instance, allocate each instance its own drive letter. When clustered, each instance must have its own drives because each instance is in its own resource group and services cannot access resources in another resource group.

Also, each node will need not only an IP address but also a heartbeat IP address. A heartbeat IP address is typically a different subnet than the IP subnet that your network uses. The heartbeat IP address is a cross-connect cable going between the servers in the cluster. In a cluster with three or more nodes, a network switch will be needed because all the machines' heartbeat network adapter cards need to be on the same network. The heartbeat network is the network that the Windows Cluster service uses to see whether the other machines within the cluster are working correctly.

You'll also need an IP address to cluster the Windows OSs together, including the quorum drive and any other Windows services that need to be clustered. Also, you'll need to assign an IP address for the SQL Server, which will be used during the SQL Server installation.

You'll also need to have several computer names for your cluster. Each node needs its own name, as does the virtual server that holds the quorum and other clustered Windows services. In addition, each SQL Server instance needs its own host name because each instance has its own IP address, so host names can't be shared. In this type of situation, a good naming convention comes in handy. Using a generic name with an incrementing number works fine for standalone servers but can be challenging to manage when you start clustering. You'll need to extend the naming convention to fit clusters.

For example, try naming the hosts SQL01A and SQL01B. Then name the Windows cluster SQL01. This naming convention tells you that the SQL01A and SQL01B are the nodes and that SQL01 is the base of the cluster. Then name the SQL Server cluster SQL01V01. This name tells you that SQL01V01 is the first server on the Windows cluster. If you need to install a second instance, you'd name it SQL01V02, and so on, with each instance being a separate server in the SQL01 cluster. Table 1 lists the names of the various physical machines and the IP addresses in use in the sample cluster. The 10.3.0.0 subnet is used for the server IP addresses. The 192.168.0.0 subnet is used for the heartbeat network.

When configuring the cluster, you'll be prompted for a domain account, which will be used to monitor the cluster. Don't use your personal domain account or the SQL Server's domain account; instead set up a separate domain account for this purpose. This account will need administrative rights to all the nodes in the cluster and rights to connect to the cluster.  If this account doesn't have the rights to connect to the database, the SQL Server instance will fail back and forth from one node to another because the Cluster service will assume that the SQL Server instance has failed.

When designing your cluster, you can assign the various disks whatever drive letters you choose. It's most common to use the Q drive for the cluster's quorum drive. The quorum drive holds some shared settings between the servers in the cluster and some logging information. If you set the disk that will be the quorum to the Q Drive, the cluster setup process will automatically configure this as the cluster's quorum.

Installing the Cluster

Unlike Windows 2003, with Server 2008 you must first prepare the OS to be clustered. Server 2008 takes a minimal install approach in that none of the features are installed on the base OS after it's installed. Before you can use failover cluster, you must install support for failover clustering on all servers that will be in the cluster. To install support for failover clustering, open Server Manager by clicking Start, All Programs, Administrative Tools, Server Manager. Navigate to the Features section in the right menu. This will show you that 0 of 35 features have been installed.

If you want to use distributed transactions on your SQL Server cluster, you'll need to install Microsoft Distributed Transaction Coordinator (MSDTC) on all the cluster nodes. Your installation will be easier if you do this before configuring the cluster. To install MSDTC, open Server Manager and click the Add Role link in the Roles section. From the first menu list, select Application Server as shown in Figure 1.

If you intend to cluster the MSDTC service (which is an optional step), you'll need to assign a dedicated drive for the use of the MSDTC service and have a dedicated IP address for this use.

Click Next through the next information screens until you get to the Role Services screen, as Figure 2 shows.

Select the Distributed Transactions check box, which will automatically select the Incoming and Outgoing Remote Transactions and the WS-Atomic Transactions options. You can uncheck any unneeded roles.

The next screen will ask how what SSL certificate to use to encrypt the WS-Atomic Transactions. You can select an existing certificate or create a self-signed certificate. After selecting your preferred option, click Next, then Install to complete the installation.

After MSDTC has been installed on both cluster nodes, you'll need to manually cluster the MSDTC service. In Server Manager, click the Add Feature link in the right pane to display the feature installer. Select the Failover Clustering option, as Figure 3 shows and click Next.

If other features have already been installed, they'll be selected and grayed out. If you don't see the Failover Clustering option, be sure that you have Server 2008 Enterprise or Server 2008 Datacenter Edition installed. The lower-edition OSs don't support this feature and thus don't list it.

After checking Failover Clustering, click Next, then Install to complete the install. After the installation has finished, click Close. Once you have completed this procedure on all the cluster nodes, close Server Manager. You're now ready to configure the Windows cluster.

On the first machine, open Failover Cluster Manager by clicking Start, All Programs, Administrative Tools, Failover Cluster Manage. To use Failover Cluster Manager and access the other cluster nodes, you may need to make changes to the Windows Firewall to allow remote procedure call (RPC) access to the server. Server 2008 will add to the Windows Firewall some entries called Failover Clusters and Failover Cluster Management, which you can enable to allow this network access. By default RPC uses dynamically selected TCP ports over 1024 to connect. For the purposes of this article, it's assumed that Windows Firewall is disabled on all servers in the cluster.

After opening Failover Cluster Manager, in the right menu you'll see several options, including Validate a configuration, Create a Cluster, and Manage a Cluster. Validate a configuration will let you test a group of servers to ensure that they can be correctly clustered. Create a Cluster runs you through the wizard to create a Server 2008 cluster, and Manage a Cluster lets you manage an existing cluster. You must validate your cluster configuration before going through the Create a Cluster wizard. The cluster must successfully pass the cluster verification tests before SQL Server 2008 will successfully install on your cluster.

Verifying the Configuration

Before you can begin, you'll need to configure the shared storage and connect it to all the servers that will be part of the cluster. You'll also need to install the failover cluster feature on all the cluster nodes. It's highly recommended that all servers that will be in the cluster be at the same patch level.

From within Failover Cluster Manager, click the Validate a configuration link in the application's upper-right corner. This will present you with a welcome screen that tells you about the wizard. Click Next to display the server entry screen. On this screen you enter the names of the servers you want to verify against each other. You can enter from one to 16 names (eight on the Itanium platform); however, it's recommended that you enter all the servers that will be in the cluster. After you've typed in the server names and clicked Add for each one, as Figure 4 shows, click Next.

The third screen in the wizard asks you if you want to run all the tests or just a subset of the tests. It's recommended that you run all the tests, so make that selection and click Next. The next screen shows which tests we'll be running. After reviewing the list, click Next to begin the tests. Depending on the number of servers, the speed of your servers, and the number of tests being performed, this can take several minutes. After the testing is finished, you can view the report to identify any issues that you need to address before building the cluster.

Building the Cluster

In Failover Cluster Manager, click the Create a Cluster link in the upper-right corner to start the Create a Cluster wizard. The first screen tells you about the wizard; click Next. The second screen will look similar to the second screen in the Validate a Configuration wizard, as shown in Figure 4. Enter the names of the servers in the cluster that you want to allow SQL Server to run on and click Next.

The next screen asks you to run the validation tests again. If the cluster passes these validation tests, it's considered to be supportable by Microsoft Customer Service and Support (CSS). After running (or skipping) the tests, you're asked for the cluster name, as the screen in Figure 5 shows.

This is the name that will be created in Active Directory (AD) and will be how you reference the cluster (aka the cluster root). In this case, we'll use SQL01 as the cluster root name. This screen also asks you for the IP address on which the cluster will listen for the SQL01 name. If you're using a DHCP address, the IP Address section of this screen won't be shown and the SQL01 name will be configured to use a DHCP address.

The next screen reviews the information you've entered; clicking Next begins the process of creating the cluster. The final screen displays the results of the cluster process. It will inform you of any problems or that the cluster has been successfully configured. Assuming there were no errors, at this point the cluster is set up.

Configuring MSDTC Within a Cluster

To configure MSDTC within the cluster, open Failover Cluster Manager and connect to the cluster you just configured. Navigate through the tree, right-click Services and Applications, and select Configure an Application. Click Next on the information screen, which will display the default list of services that you can choose from, as Figure 6 shows.

Clicking Next takes you to a screen on which you enter the name and IP address that MSDTC will be hosted under. In this example, we'll use SQL01DTC for the host name and 10.3.0.6 as the IP address, as Figure 7 shows.

Click Next and select the hard drive you want to use to store the MSDTC settings files, as Figure 8 shows.

This will be a unique disk that isn't the quorum and won't be used for SQL Server. This disk doesn't need to be very large: 512MB or 1GB is more than enough space. Click Next through the confirmation page and again to complete the cluster configuration of the MSDTC service.

Installing SQL Server 2008 on a Cluster

We can now being installing SQL Server on the cluster. From the SQL Server CD or DVD, launch the installer. Install the normal prerequisite updates as prompted to, as you would for a single-server installation. After the installer brings you to the home page, click Installation in the right menu. From this page, you'd normally select the first option New SQL Server Standalone Installation to install SQL Server. However, since we're installing a clustered SQL Server, we select the second option New SQL Server Failover Installation. Doing so launches the SQL Server 2008 Clustered Server installer.

The first couple of sections of the installer are the same as the standalone SQL installer, so I won't go into any real detail on them here. The first section is the support rules check, and the second section is the license key information, license agreement, and setup support files. After you've gotten past these sections (possibly with several reboots in the middle), you get into the actual SQL Server 2008 installer.

When the actual SQL Server 2008 installer finally launches, it will show the standard Setup Support Rules page. The installer will perform a few extra checks to ensure that the cluster has been verified and that MSDTC is clustered correctly. After that, you'll see the usual screens with the product key, license agreement, and services. When installing SQL Server 2005 in a cluster, you had to tell the installer that you wanted to install the SQL Server Engine as a clustered service. You no longer have to remember to do so as the SQL Server Engine uses a different installer from the standalone installer.

After you select the features you want to install, you'll see the Instance Configuration screen, as Figure 9 shows.

This screen is similar to the Instance Configuration screen in the standalone SQL Server 2008 installer. The difference is that when installing SQL Server on a cluster you must provide the SQL Server network name that will be used to access the instance. In this example, the network name SQL01V01 is used. This name is the name that SQL Server will respond on. The cluster name SQL01 is the name of the cluster itself. This name is used to manage the cluster using the failover cluster administrator and must be a unique name on your Windows network. After filling out the SQL Server network name, instance name, and instance root directory, click Next.

The next screen ensures that the drive on which you'll install the binaries has enough disk space to hold them. The next four screens are where the bulk of the cluster configuration will be done. The first of these screens is where you set the cluster resource group. This is the logical grouping of clustered resources that will be failed over from one node of the cluster to another. In this case, the cluster resource group is named SQL01V01, as shown in Figure 10, to match the SQL Server network name that was set two screens prior.

On this screen there are two other cluster groups already defined on the cluster that aren't available for selection. The first is the Available Storage and the second is Cluster Group. These are default cluster groups that Windows creates when setting up the cluster. These cluster groups are disabled because they aren't valid to hold the SQL Server. If you had pre-created the cluster resource group that you wanted to use, it would be listed below the default groups and available in the drop-down menu in the top. As our SQL01V01 resource group isn't available, I simply typed it into the drop-down menu.

The next screen is the Cluster Disk Selection screen. Here you select which disks will be available for the SQL Server instance to use for data files, as Figure 11 shows.

In this case, we want to use cluster disks 1, 2, and 4. Cluster Disk 3 isn't available as this is the cluster's quorum disk. The names displayed are based on the labels assigned by Windows when the cluster was set up. If desired, you can change these names from within the Failover Cluster Administrator. After selecting the disks that your SQL Server instance will use, click Next.

On the next page, you configure network settings for the instance. Because this is a clustered instance of SQL Server, the instance must have its own IP address, which corresponds to the SQL Server network name that you set on the Instance Configuration screen. As you can see in Figure 12, you can select to use a DHCP-issued IP address or statically specify the IP Address and subnet mask. 

In this example, we're using DHCP for the servers as well as the virtual names on the cluster.

The ability to use DHCP for a cluster is new to Server 2008 as well as SQL Server 2008 and is available only if you cluster your SQL Server using Server 2008. If you install SQL Server 2008 on Windows 2003, you can't use DHCP for the SQL Server instance. Using DHCP allows you to not configure each server and cluster with an IP address. You can either allow the server to pull IP addresses at random from the DHCP server or configure reservations on the DHCP server so that the server will always receive the same IP address. Check with your systems administrator before using DHCP as many companies don't use DHCP within the data center.

After configuring the IP address, click Next to take you to the next screen, the Cluster Security Policy screen that Figure 13 shows.

On this screen you select whether your clustered installation will use the new Server 2008 service SIDs functionality to control the running account's access to the server or use SQL Server 2005 domain groups to do so. For simplicity of management and ease of upgrading, it's recommended that you select the default option of using service SIDs. If your Windows domain is running in Windows 2000 mixed-domain mode, you cannot select the service SIDs option. The next several screens are the same in both the standalone SQL Server installer and the clustered installer.

The next screen is the typical Server Configuration screen, where you select the domain accounts that the SQL Server and other services will run under. As with other SQL Server versions, when you install the SQL Server instance in a clustered configuration, you must configure the SQL Server service and SQL Server Agent to run under domain accounts. Local accounts and generic accounts such as the system account are not valid.

Next, on the Database Engine Configuration screen, you configure Windows or SQL Server and Windows Authentication and the various folders that the database files and log files will be stored in. When configuring the Data Directories tab on this screen, you must use paths on the disks that you configured for the cluster on the Cluster Disk Selection screen. If you do not, an error will be returned because the SQL Server instance can't use non-clustered disks for data files. If you've selected to install services other than the SQL Server, those configuration screens will be shown; however configuration of those services is beyond the scope of this article. On the next screen, Error and Usage Reporting, you specify what data is automatically sent to Microsoft.

The next screen, which Figure 14 shows, verifies that the cluster is ready for SQL Server to be installed on it.

This is a final set of checks to ensure that various last-minute pieces haven't been changed. Notice that the third check is a Windows Server 2003 FILESTREAM HotFix Check. If you're running SQL Server 2008 in a cluster on Windows 2003, you'll need to download and install the hotfix specified in the Microsoft article at support.microsoft.com/kb/937444 before you can install SQL Server 2008. Note that the installation of that hotfix requires a reboot, so you have to cancel out of the installer at this point and restart the process after installing that hotfix on all your cluster nodes.

At this point, you're ready to install SQL Server 2008 on your cluster. Click Next and review your settings, then click Install to begin the installation. Once your installation is done on this node, you're ready to install SQL Server on the other cluster nodes. SQL Server 2005 was the last edition of SQL Server to automatically install SQL Server on all nodes at once.  Beginning with SQL Server 2008, you must manually install and patch each cluster node individually. This process allows for less downtime as you're patching the cluster or upgrading your cluster from SQL Server 2005 to SQL Server 2008.

Adding Nodes

Installation on the other nodes is similar to installing on the first node except that you'll select the Add Node to a SQL Server failover cluster option from the initial menu. The same software requirements apply to the other nodes in the cluster, so the installer will handle the verification and installation of these components as well.

Because most of the settings are picked up from the other node(s) in the cluster that already have SQL Server installed on them, the process of adding a node to the cluster is quite a bit shorter than installing the first node The first three screens are the same as for the first node's installation: setup, support rules, product key, and license terms. The fourth screen, shown in Figure 15, lets you select the instance you want to add this node to.

If you were to expand the Features column, you'd see that the components to be installed are SQLEngine, SQLEngine/Replication, and SQLEngine/FullText, which are the three clusterable components you selected for the prior node. If you want to install SQL Server Integration Services (SSIS) on the cluster, you must install it separately from the SQL Server Engine as a standalone installation. Currently only the SQL Server Engine and the SQL Server Analysis Services (SSAS) Engine are cluster aware. After selecting the instance to install on this node, click Next.

On the next screen, Service Accounts in Figure 16, you need to enter the password for the domain accounts that will be running the clustered services.

The usernames cannot be changed because the same accounts must be used by all cluster nodes to run the services.

After entering the passwords and clicking Next, you'll see the Error and Usage reporting screen. Next, the Add Node Rules screen in Figure 17 verifies that the server is ready for SQL Server to be installed and that the cluster is ready for another node to have SQL Server installed on it.

After you click Next from this screen, you can verify your settings before clicking Install to complete the installation on this node.

At this point you can use Failover Cluster Manager to move the instance from one node to the other to ensure that SQL Server is set up and able to run correctly on each node. Currently our clustered instance is running on SQL01A. You can move the instance (i.e., fail it over) to the other node by right-clicking the resource group, then selecting Move this service or application to another node, as Figure 18 shows.

After the installer has finished on both nodes, the installation will be complete. You can then connect to the SQL Server instance using the name assigned to it and given during the installation of the first node.

Reducing Downtime

Although clustering SQL Server can be expensive, when designed and configured correctly, a SQL Server cluster can provide you with a reliable uptime solution on which to host your database platform. This extra uptime lets you rest easily when hardware problems arise and outages last only a few seconds instead of minutes when Windows needs to be patched and rebooted or hours or even days if SQL Server goes offline due to a hardware failure.

Learn more from "SQL Server 2012 High Availability."