How to migrate SQL Server 2005 Service Pack 2 failover clustered instances from Windows Server 2003 Service Pack 2 to Windows Server 2008

Pre-upgrade steps

1. Before you start the upgrade on your production servers, test all the components and applications on a test cluster.
2. So that you do not lose data or configuration settings, perform a complete inventory of SQL Server instances, of Windows, and of resource groups on the cluster. Some things to consider are as follows:
   
   • In Windows Server, inventory all policies, users, user groups, and other user account features.
   • For SQL Server 2005, examine Add or Remove Programs for all installed SQL Server 2005 applications.
   • In Windows Server, review the Windows Clustering Resource Groups.
   • Determine your quorum type for the cluster on the Windows Server.
     
3. Make sure that you have done the following:
   
   • You have correctly backed-up all data.
   • You have performed full file-level backups of all SQL Server cluster drives.
   • You have performed full SQL Server-level backups of all databases for both SQL Server system databases and for user databases. To do this, run the following commands at the command prompt:
     
     1. Type sqlcmd, and then press ENTER. This starts the sqlcmd utility.
     2. Fully back up the user databases on Node2, type BACKUP DATABASE [userdb1] TO DISK = N’Z:\userdb1.bak’, press ENTER, type GO, and then press ENTER.
     3. To restore the database on the new cluster, type
        RESTORE DATABASE [userdb1] FROM DISK = N’Z:\userdb1.bak’ WITH NORECOVERY, press ENTER, type GO, and then press ENTER.
     4. Set the databases as ‘read-only’ on the old cluster, type
        ALTER DATABASE [userdb1] SET READ_ONLY
        , press ENTER, type GO, and then press ENTER.
     5. To make log backups of the databases on the old cluster, type BACKUP LOG [userdb1_log] TO DISK = N’Z:\userdb1_log.bak’, press ENTER, type GO, and then press ENTER.
     6. To make backups of system databases on the old cluster, type BACKUP DATABASE [master] TO DISK = N’Z:\master.bak’, press ENTER, type GO, and then press ENTER.
     7. Type BACKUP DATABASE [msdb] TO DISK = N’Z:\msdb.bak’, press ENTER, type GO, and then press ENTER.
        
     8. Type BACKUP DATABASE [model] TO DISK = N’Z:\model.bak’, press ENTER, type GO, and then press ENTER.

Upgrade Node1

To upgrade Node1, follow these steps.

Note When you perform this process, the server will incur periods of downtime. During this downtime, the servers and the applications will not be available to production until the processes are fully completed.

1. Fail over all services on Node1 to Node2.
   
   Note This step will incur downtime.
2. Use Add or Remove Programs to remove all instances of SQL Server from Node1.
3. Use Cluster Administrator to evict Node1 from the Windows cluster:
   
   1. Start Cluster Administrator on Node2.
      
   2. Right-click Node1, and then click Stop the Cluster service.
      
   3. Right-click Node1, and then click Evict Node.
      
4. Stop the cluster service on Node2, and then set the service to disabled.
5. Perform a fresh Windows Server 2008 installation. You will have to reconfigure any settings that are needed from your Windows Server 2003 installation. To do this, follow these steps:
   
   1. On Node1, do a fresh installation of Windows Server 2008.
   2. Migrate any user settings, users, and policies to Node1. This includes the following:
      
      • IP-v4 address
      • Computer name and Domain name
      • Account/Security Group (Add the domain user to the local administrators group.)
      • External disk (partition and drive letters)
6. Install any supporting software. This includes drivers and providers.
7. Migrate the Windows Server 2003 failover cluster disks to the new Windows Server 2008 node. Or, attach new SAN disks for failover cluster use.
   
8. Create a one-node Windows Server 2008 cluster on Node1:
   
   1. Click Add feature on the Initial Configuration Tasks (ICT) console or on the Server Manager snap-in, and then select Failover Clustering to install.
9. Create a single node cluster by using the Failover Cluster Management console with Node1 by following the wizard that was started by the console.
10. Install SQL Server 2005 with clustering on Node1 as a new SQL Server Failover instance.
    
    
    Important The new instance of SQL Server on Node1 must be installed by using the same drive letters as Node2.
    
    Warning You must use the same data path that you use when you perform SQL Server system database backups.
    
    To install SQL Server 2005 with clustering on Node1 as a new SQL Server failover instance, follow these steps:
    1. Prepare your Failover Cluster to have a SQL Server Failover Clustered Instance installed To do this, follow these steps:
       1. Right-click Services and Applications.
          
       2. Click More Actions, and then click Create Empty Service or Application.
          
          Note This should be your SQL Server cluster container.
       3. Right-click the new container that you created, select Rename, and then give this a more meaningful and descriptive name that is associated with your SQL Server installation that you will be performing.
       4. In the Failover Cluster Management console, make sure that your new SQL Server container is selected, and then click Add Storage under Actions.
          
       5. Select the cluster drive for the instance of SQL Server to use. This cluster drive should have the same drive letter as the migrated instance. You can expand the available cluster disks to reveal the volume's drive letter.
          
          
          Note Your failover cluster is now prepared to have a SQL Server failover clustered instance installed.
    2. Install SQL Server 2005, and then install the database engine as a Clustered Instance on Node1.
       
    3. When you are prompted, specify a new cluster instance name, such as FocInstance2.
    4. When you are prompted, specify an unused, available shared disk, such as G:, for data files.
    5. Keep the same SQL Server database instance name as before, or use the default name. For example, use SqlInstanceName1.
       
       
11. Install SQL Server 2005 SP2 and any SQL Server 2005 hotfixes that are recommended for Windows Server 2008.
    

Data migration

Use either of the following methods to migrate data.

Note These methods are not presented in any particular order. You will have to determine which method is preferred for your environment.

Important Before you use either method, the current installation's NTFS permissions must be applied as soon as the data is in the new location.

Method 1: Transfer the complete disk volume

To transfer the complete disk volume, follow these steps:

1. Remove the disk resources, such as F:, from the SQL Server failover group on the old cluster:
   
   1. Take the failover group of the instance of SQL Server offline.
      
   2. Open the SQL Server resource Properties dialog box, click the Dependency tab, and then remove the disk resources that contain the dependent database files.
   3. Open the SQL Server Fulltext resource Properties dialog box, click the Dependency tab, and then remove the disk resources that contains the dependent database files.
   4. Remove the disk resource from the failover group.
   5. Remove the drive letter of the disk.
2. Switch the disks on the new cluster. For example, switch the F: and G: drives. To do this, follow these steps:
   1. Repeat steps 1a through 1e.
   2. Add the disks that were used by the old cluster as the new storage.
      
   3. Change the drive letter of the new storage to the one that was used by the instance of SQL Server.
      
   4. Add the storage to the failover cluster group of the instance of SQL Server.
   5. Set dependency from the SQL Server resource to the new storage.
   6. Set dependency from the SQL Server Fulltext resource to the new storage.
3. Start the instance of SQL Server on the new cluster.

Method 2: Copy the complete DATA directory structure

1. On the server that is running Windows Server 2003, locate the Data folder on the previous installation.
2. On the server that is running Windows Server 2008, locate the new Data folder. This new data folder must use the same path that was used on the server that is running Windows Server 2003.
3. In the Failover Cluster Management console, take the new SQL Server failover cluster installation offline.
4. Rename the new Data folder to Data_New.
5. Copy the original Data folder that was determined in step 1 to the location that was determined in step 2. When the copy is complete, both the Data and the Data_New folders should be at the same folder level.
6. Change the IP Address and the Instance Name of the old instance of SQL Server 2003. For more information, visit the following Microsoft Web site:
   http://msdn2.microsoft.com/en-us/library/ms178083.aspx
7. Open the Properties dialog box for the Network Name resource of the group, and then change the name of the virtual machine to the name of the instance of SQL Server.
   
8. Open the property of the IP Address resource of the group, and then change the IP Address of the virtual server to the instance of SQL Server.
9. Change IP Address and Instance Name of the new instance of the SQL Server 2005. For more information, visit the following Microsoft Web site:
   http://msdn2.microsoft.com/en-us/library/ms178083.aspx
10. Open the Properties dialog box for the Name resource of the group, and then change the name of the virtual machine to the name of the instance of SQL Server.
11. Open the property of the IP Address resource of the group, and then change the IP Address of the virtual server to the instance of SQL Server.
12. Start the instance of SQL Server on the new cluster.

Restore your SQL Server databases from backups

After you have performed method 1 or method 2, you must restore your SQL Server database backups. To do this, follow these steps:

1. Open a command prompt.
2. Type sqlcmd, and then press ENTER. This starts the sqlcmd utility.
3. To restore the system databases, at the command prompt, type
   RESTORE DATABASE [master] FROM DISK = N’Z:\master.bak’, press ENTER, type GO, and then press ENTER.
4. Type RESTORE DATABASE [msdb] FROM DISK = N’Z:\msdb.bak’, press ENTER, type GO, and then press ENTER.
5. Type RESTORE DATABASE [model] FROM DISK = N’Z:\model.bak’, press ENTER, type GO, and then press ENTER.
6. To restore the user databases, type RESTORE DATABASE [userdb1] FROM DISK = N’Z:\userdb1.bak’
   , press ENTER, type GO, and then press ENTER.

Upgrade Node2

After you restore your SQL Server database backups, upgrade Node2. To do this, follow these steps

1. Uninstall SQL Server 2003 from Node2.
   
2. Open a command prompt on Node2.
3. Type sqlcmd, and then press ENTER. This starts the sqlcmd utility.
4. Type CLUSTER NODE Node2 /EVICT, press ENTER, type GO, and then press ENTER.
   
5. Type QUIT to close the sqlcmd utility.
6. Install or upgrade to Windows Server 2008.
7. Add Node2 to the new failover cluster:
   
   1. Click Add feature on the Initial Configuration Tasks (ICT) console or in the Server Manager snap-in, and then select Failover Clustering to install.
   2. On the Failover Cluster Management console, click Add Node.
8. Configure the SQL Server 2005 cluster as a failover cluster:
   1. In Control Panel, double-click Programs and Features, select Microsoft SQL Server 2005, and then click Change.
      
   2. Add Node2 to the group owners on the setup wizard, and then continue the installation.
   3. Install SQL Server 2005 SP2 on Node2.
   4. Move the failover group to Node2.

Note If the SQL Server Fulltext is in a failed state and you cannot restart it, you must repair SQL Server 2005 by completing one of the following recovery options:

Recovery option 1

To recover from a failed state, reinstall SQL Server 2005 SP2.

Recovery option 2

1. Insert the SQL Server 2005 installation media into the disk drive.
   
2. At the command prompt, type the following command, and then press ENTER.
   
   
   start /wait Drive:\setup.exe /qb REINSTALL=SQL_FullText INSTANCENAME=MSSQLSERVER REINSTALLMODE=M
   
   
   Note For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.