Upgrading a SQL Server 2005 Cluster to a SQL Server 2008

In my previous articles, Planning and Implementing a SQL Server Cluster and Set Up a SQL Server 2008 Cluster, I showed you how to install SQL Server 2008 and SQL Server 2005 in a cluster. In this article, I’ll combine these two installations as I show you how to upgrade an existing SQL Server 2005 cluster to a SQL Server 2008 installation. As we learned when building a SQL Server 2008 cluster in “Set Up a SQL Server 2008 Cluster,” the SQL Server installer has changed quite a bit since SQL Server 2005 was released. These same changes affect the upgrade process as well. To start this process off, I’ll review some of the basic concepts of clustering, and then I’ll guide you through an upgrade of a SQL Server 2005 active/passive cluster to SQL Server 2008.

You might want to consider upgrading your SQL Server 2005 cluster to take advantage of SQL Server 2008’s ability to perform rolling upgrades in the future when patching your cluster. With SQL Server 2005 and earlier, patching a cluster requires the instance to be down while the service pack is deployed. With SQL Server 2008, you patch each node separately, so the only downtime is when you’re moving the instance from one node to another via the typical failover features of Windows clustering.

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, it’s 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 because you can’t change a traditional single-server installation to 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.

If 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 machine 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 2008 Enterprise Edition and SQL Server 2005 Enterprise Edition start up much faster because they roll forward any completed transactions, bring the database online, and then roll back any completed transactions. The other editions of SQL Server 2008 and SQL Server 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. These advantages include the ability to keep a SQL Server system running even after a hardware failure. SQL Server 2008 also gives you the ability to do rolling upgrades, which lets you patch each node of the SQL Server cluster independently of the others without taking the SQL Server system 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 and 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 isn't technically 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 used for the SQL Server databases can't be the standard RAID array that you plug into the server. Instead, it must be an array designed specifically for use in a cluster. Although Windows Server 2003 supports using SCSI arrays for clustering, Windows Server 2008 doesn’t support using SCSI arrays 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 iSCSI SAN. 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 StarWind Software’s 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 because the performance of these software packages will depend on the hardware behind them and the network configuration.

Like SQL Server 2005, SQL Server 2008 Standard Edition and Enterprise Edition both support clustering. 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 Edition supports up to eight nodes in the cluster. Server 2008 Datacenter Edition supports up to 16 nodes when using the x86 or x64 platforms; when using Itanium processors, Server 2008 Datacenter Edition supports up to eight nodes in the cluster.

Server 2008 includes some exciting new features in the clustering configuration that greatly increase the flexibility you can obtain when clustering services. You can now use DHCP for clustering and 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 Upgrade

When you’re building a new SQL Server cluster, you have to lay out all your hard disk drives in advance. However, when you’re upgrading a SQL Server cluster, the hard disk drives are already laid out (when the SQL Server 2005 instance was installed). For the purpose of this article, I’ll be using a Windows cluster that has three disks assigned to it: The E drive will hold the data files, the L drive will hold the log files, and the T drive will hold the tempdb database. (Your configuration might vary.)

Also, I’ll be using a cluster very similar to the one that I described in the article Planning and Implementing a SQL Server Cluster. The nodes of the cluster will be called SQL01A and SQL01B. The cluster root name will be SQL01, and the SQL Server instance will be running under the name SQL01V01. Both nodes of the cluster will be running Windows 2003 Enterprise Edition because a SQL Server 2005 cluster probably isn’t running on Server 2008. If your SQL Server 2005 cluster is running under Server 2008, your upgrade process will be very similar, with any differences noted as I walk through the process. As for the IP addresses, the SQL01A node will use the public IP address 10.3.0.31 and the SQL01B node will use the public IP address 10.3.0.32. The cluster root SQL01 will use the public IP address 10.3.0.33, and SQL01V01 will use the public IP address 10.3.0.34.

To begin the installation, first ensure that the SQL Server instances are fully patched and all required Windows 2003 hotfixes are installed. When upgrading a SQL Server 2005 cluster, a rolling upgrade approach is taken, similar to the way a new SQL Server 2008 cluster is installed. You’ll upgrade the active node first, then the passive node. You upgrade the cluster in this way not so that you can fail the instance between the SQL Server 2008 node and the SQL Server 2005 node (in fact, you can’t do this), but so that the installer on the first node doesn’t need permission to run the installer on the second node. After you’ve upgraded the first node, don’t attempt to fail the cluster over to the second node until the second node has been upgraded because the second node still has the SQL Server 2005 version on it, so it won’t be able to read the SQL Server 2008 data files.

Upgrading the Cluster

When you launch the SQL Server 2008 setup on an already installed SQL Server 2005 cluster, you’ll be prompted to install the usual set of Windows and .NET updates, which the SQL Server 2008 installer would install for any Windows 2003 installation.

When upgrading a SQL Server 2005 cluster to a SQL Server 2008 cluster, the installer will do its best to keep the instance online for as long as possible during the upgrade. To do this, when you begin the upgrade process, start on a passive node. If you attempt to start by upgrading the active node, the installer will prompt you to fail over the SQL Server instance to another node before proceeding.

After launching the installer, you’ll be presented with the typical SQL Server Installation Center, which you would typically see when installing SQL Server 2008. In the menu on the left, click Installation, which displays the screen shown in Figure 1.

Typically, when installing SQL Server on a new cluster, you’d select the second option, New SQL Server failover cluster installation. Because you’re performing an upgrade, you’ll select the fourth option, Upgrade from SQL Server 2000 or SQL Server 2005. Selecting this option will launch a separate Upgrade to SQL Server 2008 wizard, which will guide you through the upgrade process. The first thing this wizard will do is open Internet Explorer to the Windows Upgrade page to ensure that you have all the current hotfixes installed. After installing any hotfixes that are missing, you can continue with the installation.

Back in the installer, the SQL Server Installation wizard will check the setup support rules to ensure that the installer can continue. These checks are similar to the checks that the installer runs when performing a new SQL Server 2008 installation. It makes sure that the server OS is supported, that the user has Administrative rights, and that the computer isn’t pending a reboot, among other checks that it performs. After the checks have been completed, click OK to move into the rest of the wizard.

On the next screen, you’ll be prompted to enter your license key. If you have a volume license installer, this field will be filled out for you. Otherwise, you’ll need to enter the key, which is provided by Microsoft on your installation media. On the next screen, agree to the license terms and click Next. Then you’ll be prompted to install the Setup Support Files. Click the Install button to proceed with the installation.

Next, the installer will run another set of checks. These checks ensure that the cluster is ready to continue with the installation. After the checks have been completed, click Next. The next page of the installer shows which instance you want to upgrade. In this case, you’re selecting the default instance (MSSQLSERVER). You can see in Figure 2 that the installer has identified all of the features that are installed on your SQL Server 2005 instance, as well as the version and edition (Developer edition in this case) that you’re upgrading from.

If you have more than one instance installed on the cluster, each one must be upgraded independently of the others.

Like the upgrade process from SQL Server 2000 to SQL Server 2005, when upgrading SQL Server 2005 to SQL Server 2008 you have to upgrade to the edition you’re currently running or higher. For example, you can’t upgrade to SQL Server 2008 Standard Edition from SQL Server 2005 Enterprise Edition. Instead, if you have SQL Server 2005 Enterprise Edition installed, you have to upgrade to SQL Server 2008 Enterprise Edition. After you’ve selected the instance you want to upgrade from the drop-down menu, click Next.

On the next screen, you’ll see the features list. If there are any features that aren’t already selected that you want to install, select them here. Figure 3 shows that you’re installing the SQL Server engine, the Full Text Search feature, and the SQL Server Replication feature.

After making any changes that are needed, click Next, which will take you to the Instance Configuration screen. On this screen, which will appear to be pretty redundant because it’s a recap of the information shown in Figure 2, you can verify the instance that will be upgraded, and then click Next.

Then you’ll see the Disk Space Requirements screen. This screen simply ensures that the drive that you’ll be installing the SQL Server software onto has enough free space to hold the installation. The next screen, shown in Web Figure 1, lets you select the domain accounts that any new services will be running under.

The SQL Server service isn’t listed here because changing the account that it runs under isn’t supported. In this case, SQL Server Browser was configured to run under the domain account CORPSQLServer. Because the Full Text Search service can run under a domain account and is configured as a clustered service in SQL Server 2008, it’s listed so that you can configure it to run under a domain account. Typically, you’d select the same account to run this service as the one that runs the SQL Server service, but that’s not required. After you’ve set the necessary usernames and passwords for your environment (the services listed on your screen might vary based on the services you have installed), click Next.

If you’ve chosen to install Full Text Search, the next screen you come to will ask you how you want to handle the full-text data. Your options are to import the data, rebuild the indexes, or reset the catalogs. If you choose to import the data, the full-text indexes will be imported as is. This process takes longer during the installation process because the full-text catalog must be loaded from the full-text files into the database itself. However, once the database is online, the full-text index will be fully populated. The downside to this option is that full-text indexes don’t use SQL Server 2008’s word breakers until the full-text index is rebuilt.

You can also choose to rebuild the catalog, which tells the SQL Server engine after the installation is done to do a complete rebuild of the full-text indexes. This process takes (depending on the size of the data being indexed by the full-text index) a lot of CPU and memory resources. However, the full-text data will be processed by SQL Server 2008’s word breakers. Although this is faster than importing the data during the installation process, if your tables are quite large, your full-text index could take a while to populate, which could result in incomplete search results when searches are run against the full-text feature of the database engine.

Your third option is to reset the catalogs, which tells the SQL Server engine to drop the old full-text database files and reset the catalogs. The metadata for the full-text catalogs and indexes is retained; however, the catalogs will be empty until you trigger a full rebuild manually once the upgrade has been completed. If you select this option, no results will be returned from the full-text engine until you trigger the full population of the data in the catalogs. After you’ve selected the way you want to process the full-text indexes, click Next.

The next screen is the standard error reporting and feature reporting screen. Microsoft recommends that you select both of the check boxes on this screen. If you select these check boxes, SQL Server will transmit error and usage data to Microsoft on a regular basis so that Microsoft can more quickly respond to problems with the project as well as market usage trends. However, this is optional and should be set per your company’s guidelines.

The next screen verifies that the system can handle the upgrade. Various checks are performed to ensure that the OS is capable of running SQL Server 2008 with the features that you selected. Note that these checks don’t check your database code to ensure that the database code will run on SQL Server 2008 without any problems. If there are any errors on this screen, you’ll need to resolve them before continuing with the installation.

The next screen shows you summary information about the cluster, as shown in Figure 4.

It shows both nodes of the cluster, that they’re currently both SQL Server 9.3.4035 (depending on the service pack and hotfix you have installed, this build number might vary), and which node is online. As I mentioned earlier, SQL Server will move the instance to another node in the cluster to minimize downtime.

After you’ve reviewed the screen shown in Figure 4, click Next to go to the Ready to Upgrade screen, which shows the standard summary of what will be installed. After you’ve reviewed this screen, click Upgrade to begin the upgrade process. Depending on the speed of your computer, the size of your full-text database, the upgrade option selected (if full text is installed on your server), and the number of features you have installed, this process can take quite a while to complete.

Once the installation on this node is complete, you’ll be presented with a screen that shows each service and the status of the installation for that service. Click Next to go to a screen that shows that SQL01A has completed the upgrade process, as shown in Web Figure 2.

After you’ve reviewed this screen, click Next, and then Close to complete the wizard.

Upgrading the Second Node

When you launch the installer on the second node, the first thing it will do is install .NET hotfixes, which it also installed on the first node you upgraded. After installing these hotfixes, the SQL Server cluster will fail over, moving the databases into the SQL Server 2008 instance, which then upgrades the databases to the SQL Server 2008 version. Once the cluster has failed over to the upgraded node, there’s no way to fail the database back, so it’s important that you only trigger this failover when you’re ready.

Once the node has finished rebooting, you can relaunch the SQL Server installer. All the options that you select on this node of the cluster should be the same options that you selected on the first node of the cluster. As you move through the process, you’ll get back to the Cluster Upgrade Report, which shows that the SQL01A node of the cluster is upgraded and that the SQL10B node is pending upgrade, as shown in Web Figure 3.

Once the upgrade process has completed, you’ll be shown the final Cluster Upgrade Report, which is shown in Figure 5.

This screen shows that both nodes of the cluster have been upgraded. At this point, you can install the service pack and hotfixes that you need to install on the cluster, and then your cluster is ready to be tested and released to production.

Minimal Downtime

Although the cluster upgrade process is quite long, the total downtime to upgrade a SQL Server 2005 cluster to SQL Server 2008 is only a few seconds, letting you maintain your service level agreements while upgrading to SQL Server 2008 to give your application access to its new features.