Difference between patching in SQL 2005 and 2008 cluster:-

Difference between patching in  SQL 2005 and 2008 cluster:-

SQL 2005 Clusters 

Patching a cluster is slightly more complicated and the process is different from SQL 2005  , than it is for versions later than SQL 2005.  With SQL Server 2005, the patch is applied to the active node of the cluster and it is the patch that will go off to all passive nodes and will apply the patch files remotely. This can be the most problematic part of the process and fortunately this was removed in SQL 2008.  Here is the process for patching a SQL 2005 cluster

1.     Copy the patch to the active node of the cluster

2.     Ensure that no-one is using the SQL Server, and then run the patch installer

3.     The initial screens for the patch will be very similar to that of the stand alone installer, so just follow the on-screen instructions

Once you start physically copying file patch files, this is where is gets different as the installer will start doing remote installations.  What you will find is that once you start patching a SQL 2005 cluster, the instance is unavailable to users for the duration of the patching process.  Since the patch does the remote installations on all nodes in the cluster this may take quite a bit of time (I have seen a 2 node cluster take 40 minutes to patch), and you need to be aware that the instance will effectively be down during that time.  With SQL 2005 the patch does everything so once it's finished either all nodes are patched, or none are patched.  If there is an issue during the patching phase, then the entire patch needs to be rolled back on all nodes.  Therefore if you have 3 node cluster and the last node fails to patch, the patch will be rolled back on all nodes - which will take quite a bit of time to do.  Bottom line for patch a SQL 2005 cluster is to do it out of hours or during some other down time, and allow yourself plenty of time to do it.

SQL 2008 onward (Clusters)

With the introduction of SQL 2008, we remove all of the remote installation process from the installer.  This also applies to the patching process as well which is very useful to us from a patching process as it means we can maintain the service availability whilst we are patching.  With a SQL 2008+ cluster, we always patch the passive node rather than the active node.  Patching the passive nodes means that the SQL Server is available on the active node and therefore customers can continue to use the system.  The process is as follows:

1.     Connect to a passive node in the cluster and run the patch installer

2.     Follow the on-screen instructions, selecting the options that you wish to patch

3.     Fail over the cluster once the install has finished

4.     Now patch the remaining passive node

When you patching the passive and failover, the unpatched node gets removed from the possible owners list so that the cluster can't be failed back to an unpatched node.  This can be important as some patches will update the internal database version of the installed databases, which would cause them to not start if you tried to run them on an unpatched node.