How to apply Service Pack/Hotfix on SQL2008/R2 Cluster?

How to apply Service Pack/Hotfix on SQL2008/R2 Cluster?

Applying ServicePack/Hotfix/Cummulatve on SQL2008/R2 cluster is even more simpler…… you should not directly install Service Packs on Production Environment first instead apply them on non Prod (DEV/SIT/UAT) and wait for few days and if you don’t find any errors then apply it on Prod.

Unlike with SQL2005 cluster, down time to apply patches is very minimal with SQL2008/R2 clusters because MS has introduced a new concept called Rolling Upgrade

So what is this Rolling Upgrade? During a rolling upgrade the changes are first applied to the passive node of a failover cluster.  Then the SQL Server instance is failed over to the updated node.  The upgrade is then applied to the remaining nodes.  The total outage time for the SQL Server instance is the amount of time that the SQL Server instance takes to fail over to the updated node and apply the metadata changes for the update

Basic funda in applying patches on SQL2008 or higher is, connect to a Passive node and make sure that it is not hosting any SQL Instance at that moment and apply patch on it and reboot it after reboot move the SQL Instances to Passive node, once moved Passive become Active and Active become Passive, now connect to Passive(Earlier it was Active) and apply the patch and reboot it  that’s it….

Anyone who has a very little knowledge on cluster can easily apply service packs if they follow below instructions.

Case-1

We have one SQL instance running from Node-A which is actually a two node cluster, that means we call this cluster as Active –Passive cluster, in our example Node-A is active node and Node-B is Passive node.

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

A

Step3:

Connect to Passive node that means B node in our example and double click on service pack executable file, after successfully applying patch then reboot B node.

Step4:

After rebooting B node move the Instance to B node and verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Connect to A node now and double click on service pack executable file, after successfully  applying  reboot A node.

Step5:

After rebooting A node, move SQL instance from B node to A node and then check the version by running Select @@version à this should show the latest patch version

Case-2

We have two SQL instances, SQLInstanace-1 is  running from Node-A and SQLInstanace-2 is running from B node, that means we call this cluster as Active –Active cluster, in our example Node-A is active node and Node-B is also Active node.

Step1:

First you need to take System Databases and User Databases backups and keep them at safer place.

Step2:

Download appropriate Service Pack/Hotfix/Cummulative Update on each cluster node(A/B).

A

Step3:

Move SQLInstance-2 from B node to A node and connect to B node(now it is not hosting any sql)

and double click on service pack executable file. After successfully applying patch reboot B node.

Step4:

After rebooting B node move the SQLInstanace-1 and SQLInstance-2 from A node to B node,  verify the SQL Version by running Select @@version à this should show the latest patch version

Step5:

Now Connect to A node (now it is not hosting any sql) and double click on service pack executable file. After successfully applying patch reboot A node.

Step6:

After rebooting A node, move SQLInstance-1 and move SQLInstance-2  from B node to A node and then check the version by running Select @@version à this should show the latest patch version and finally move the SQLInstance-2 from A to B node.