On the other day, something flashed in my mind after correlating two things
"..... you can use /passive switch to apply SP3 only on the passive node.............."
".... Resource database is introduced in SQL 2005 to make patch upgrades easier... you can just replace resource database from upgraded server...."
So by combing both these points, I thought we can minimize downtime to apply SQL Server 2005 SP3 in a cluster to say 1 min. How?
Let's assume that we have SQL Server 2005 SP3 x64 installed in two node cluster. Let NODE A and NODE B be the two nodes and SQL is currently active in NODE A.
Now:
1. Apply SP3 in NODE B using /passive switch as mentioned in http://support.microsoft.com/kb/934749
2. Stop SQL Server (Take Offline so that SQL Server stays in NODE A)
3. Swap Resource database from test/staging server which is already upgraded to SP3 and is a x64 build)
4. Failover SQL Server to upgraded node i.e NODE B
5. Now apply SP3 in NODE A using /passive switch
That's it... SQL Server is now upgraded to SP3. Well theortically everything is correct but Practically???
I got a big NO... This process does not completely upgrade your SQL Server cluster to SP3. The main reason is that during SP3 upgrade in active node, SQL setup also runs scripts which upgrades msdb database. If you check SQL 2005 SP3 setup folder by extracting contents, you can notice scripts by name upgrademsdb.sql e.t.c. In our steps, we don't run these scripts so our method above is not valid.
So we cannot minimize downtime using above strategy.. but still if you are very much interesting in minmizing downtime, you can configure a new standalone cluster of similair configuration and apply SP3 there and swap system databases to production cluster.
