How to upgrade Service Pack of a SQL Server 2008 Cluster?

Here I will try to explain how to upgrade SQL Server 2008 to SP3(Service Pack 3) on clustered environment.

The build number for SP3 is 5,500 and the version is 10.00.5500.

So what we are having is an Active/Active clustered environment the steps to follow for Active\Passive cluster also almost same steps.

As known in Active\Active cluster both the nodes involved in clustering will have some resources running in them. We have nodes named Node1 and Node2. SQL instance names are SQL1 and SQL2, SQL1 is running on Node1 and SQL2 instance is running on Node2. The resources are grouped as Primary group and Secondary group.

Steps to be done before starting the installation.

1.    As a DBA’s first job would be to take FULL backups of all the databases(including system and user databases) of both the instances.

2.    If needed you can make note of all server configurations, user permissions and SQL configurations. (This is an optional and won’t require unless in worst case scenario).

3.    Download the SP3 from this link.

As we have latest backup now we are good to proceed with installation. If we are going to perform this in a production environment it’s obvious to update all the users about the downtime. If the installation goes fine SP3 upgrade will not take more than 30mins for a clustered environment.

Steps to follow for installing SQL Server 2008 SP3:

1.    Login to the Node1 with administrator credentials.

2.    Open ‘Failover Cluster Manager’(FCM) from Start-> Administrative Tools -> Failover Cluster Manager. (This is for Windows 2008 OS).

3.    Once FCM opens, under the ‘Services And Applications’ you can find what all groups are created/resources running in your cluster environment. In my case I have 2 groups ‘Primary group’ and ‘Secondary Group’. As name says primary is running on Node 1 and secondary is running on Node 2. We can check the ‘Current User’ (explained in Step 6 below) to determine which groups/resources running on which node. In my case the current owner for ‘Primary Group’ is Node 1.

4.    After right clicking on the ‘Primary Group’ which is under services and applications section you will find one option ‘Move this service or application to another node’, once you place your cursor on this you can see ‘Move to Node 2’. Once you click on this option all the services and applications will be failed over to the Node 2 or the another node available in your clustered environment.

5.    Failover will not take even minutes it should complete in seconds as because that if why we use cluster environment in order to high data availability.

6.    Once all resources failed over to another node you can re-confirm by clicking on the Primary Group. After clicking on the group, on right side if you check you will find one option like ‘Current Owner’, in our case the current owner will be Node 2 as we failed all resources from node 1 to node 2.

7.    After confirming we can start the installation of SP3 by double clicking on the ‘SQLServer2008SP3-KB2546951-x64-ENU.exe’ file we downloaded already. As mine is 64bit windows I have downloaded exe with x64 and for 32bit you need ‘SQLServer2008SP3-KB2546951-x86-ENU.exe’.

8.    There is nothing much you need to select during installation just proceed by clicking ‘Next’. While checking the files in use it will show a file named ‘fdhost.exe’ is in use, you can actually ignore this and proceed by clicking next or kill this exe from task manager, it doesn’t harm anything even if you leave it running also during installation.

Note: The fdhost.exe belongs to SQL server Fulltext service and if you stop this service you can skip the reboot after installation of SP3.

9.    The next steps are pretty much same where in you need to accept the license agreement, then features that you want upgrade and finally click on ‘Install’. After clicking on install it will start the installation.

10. Once installation is done, we have to reboot the machine.

11. Once server comes back online you can open the FCM as mentioned above in step 2 and click on the Primary Group and you can find current owner of all resources is Node 2 as we failed over earlier, now following the same procedure as mentioned in Step 4 fail over the resources of primary group from Node 2 to Node 1.

12. Here thing to notice is, as mine is an Active\Active cluster we have resources running on Node 2 also. So we have to failover the resources or services and applications that are running on Node 2 to Node 1 same as mentioned in Step 4.

13. After failover of all resources to Node 1, we can start installing the SP3 as mentioned in steps 7,8 and 9.

14. Once installation is done, we need to reboot this node as well and once machine come back online you can failback the resources.

15. Once all the resources are back in place as prior to installation you can open the management studio on both nodes and confirm whether the SP3 installation was successful or not. SQL Server 2008 SP3 version number will be ‘SQL Server 10.0.5500’. To verify you can right click on server name and select properties or you can run the ‘select @@VERSION’ command which will give detailed information.

Let me know if any corrections. Thanks!!