By Bob Ham – bob.ham@microsoft.com

I really enjoy the concept of “Reducing Downtime by Using Rolling Updates and Upgrades” available in SQL Server versions 2008 and above.  For more information on this topic, please see the SQL Server 2008 Failover Clustering .

A colleague of mine discovered a switch that’s available for SQL 2005 command line installations:

How to: Install SQL Server 2005 from the Command Prompt

http://msdn.microsoft.com/en-us/library/ms144259(v=SQL.90).aspx#passive

Description of the /Passive Switch:

[PASSIVE]

Use the PASSIVE parameter to patch a passive node on a failover cluster instance without causing the instance to fail over. When this parameter is included in an installation command, hotfix.exe will pass “Passive=1″ to the following MSP packages: SQL Server, Analysis Services, Reporting Services, and Integration Services.

He had a Cluster where one node was on SP4 and the other node (the passive node) was on SP3.  We weren’t sure why the passive node failed during SP4 updates, only knew the two nodes had to be on the same build.  So he asked me about the passive switch, to see if we could use it to update the passive node.  I set the scenario up in my VM environment, tested it out, and sure enough, it worked fine.  We also thought that this would be a great method to “Minimize” down time for other SP updates in our SQL 2005 inventory.

Here are the steps to update each Node to a SP separately.   The basic concept is, update the passive Node first, fail over the SQL Cluster, and then update the other Node.  The fail over portion is where we reduce down time.

Test Environment:

• Windows Server 2003 x86
• 2 Node/Single Instance Cluster with  SQL 2005

Both Nodes are on SQL 2005 Build = 9.0.4035 (SP3):

• Log into Node2
• Download SP4 for SQL 2005 to a folder available to both Nodes
• Run from command Prompt “SQLServer2005SP4-KB2463332-x86-ENU.exe /passive”
  • Setup extracts the files to a temporary Directory
  • Now you will see the following Prompt:

Click Next –> Accept the License Agreement

Click Next –> Choose all Features (components):

Click Next –> Enter Valid Credentials

Click Next –> Choose whether or not to send Error or Usage Reporting to Microsoft

Click Next –> You may choose to stop Processes in order to avoid a reboot of the Node, after installation (in the case of locked files). You can note here that since we are on the passive Node – the SQLsrvr.exe doesn’t show up.

Click Next –> Run the Service pack installation

Click Install –> after installation is complete – reboot the Node if necessary

Click Next –> A Summary is Provided after successful installation

You can choose whether or not to run the Provisioning Tool or not.  This can always be launched later, after both Nodes are updated.

Reboot Node2 after SP4 is applied

Note: Node1 is still online and owns all the SQL resources

Fail over SQL from Node1 to Node2:

The build level is now 9.0.5000:

With SQL on line and on Node 2, you are now ready to update Node1 to SP4.