Blog

Patching Clustered SQL Server Instances with SQL Server 2008 and SQL Server 2008 R2

By: on May 10, 2011 in Failover Clustering, SQL Server 2008, SQL Server 2008 R2, SQL Server 2008 Service Pack 1, SQL Server 2008 Service Pack 2, Whitepaper, Windows Server 2003, Windows Server 2008, Windows Server 2008 R2

One of the things you will eventually have to do to an instance of SQL Server is apply one or more of the following: a community update (CU), service pack (SP), or hotfix. I recently wrote a whitepaper entitled “Applying Updates to a Clustered Instance of SQL Server 2008 or SQL Server 2008 R2” which can be downloaded from this link. It has all the background information you need, and includes how to do things like check for reboots, script thge installs, etc. The scripts for the whitepaper can be downloaded here.

That whitepaper describes the process on how to approach patching clustered instances of SQL Server and patching, but in its examples, covers two more difficult examples because things can be complex and those are not described anywhere else (hence me wanting to write the paper). Read the whitepaper for the background information, but I thought in this blog post I would present the most simple case: one instance on a two-node cluster. All changes will be denoted in red italics.

In this example, to start, the instance is currently owned by Node A and is at SQL Server 2008 SP1 and you are looking to update the instance to SQL Server 2008 Sp2. Both nodes can potentially own the instance at this point.

 

Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance

Yes Yes SP1 No Yes

SP1

Table 1. Initial cluster state

To start the process, you will first have to remove the node which does not own the instance as a possible owner of the network name resource for the instance. This process and why you need to do it is described in the whitepaper.

  Node A

Node B

  Owner Possible Owner Version Owner Possible Owner

Version

Instance Yes Yes SP1 No No

SP1

Table 2. Cluster state after removing Node B as a possible owner

Install SP2 on the node which is not currently a possible owner of the instance. Once that it is done, it will look like Table 3.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance Yes Yes SP1 No No

SP2

Table 3. Cluster state after patching the instance on Node B

Add Node B back as a possible owner of the network name resource. The cluster will now look like Table 4.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance Yes Yes SP1 No Yes

SP2

Table 4. Cluster state after adding Node B back as a possible owner

You now need to fail the instance over to Node B to start the upgrade of the instance itself as shown in Table 5. This will be the first time you would have to schedule an outage. You should do this in a controlled fashion as to minimize the impact to end users and applications.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance No Yes SP1 Yes Yes

SP2

Table 5. Failing  the instance over to Node B

The instance itself and the databases that were online will now be upgraded. The time it takes to do this will be fairly short. Technically you could allow connections after the failover, but you may want to wait until you are able to complete the patching of Node A and test full failover. That is up to you.

As with Node B, remove Node A as a possible owner of the instance’s network name as shown in Table 6.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance No No SP1 Yes Yes

SP2

Table 6. Removing Node A as a possible owner of the instance

Install SP2 on Node A as shown in Table 7.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance No No SP2 Yes Yes

SP2

Table 7. Installing SP2 on Node A

Once Node A has been patched, add it back as a possible owner as shown in Table 8.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance No Yes SP2 Yes Yes

SP2

Table 8. Adding Node A back as a possible owner of the instance

Finally, you should fail the instance back to Node A as shown in Table 8 not because you want it to “live” there (your instance should be able to run anywhere in the cluster assuming you did capacity planning correctly), but because you want to ensure that there will be no problems after the installations are done and that the instance can run on all nodes. If you have allowed connections into the instance already, you will have to schedule an outage or do it during an already scheduled outage.

  Node A

Node B

 

Owner

Possible Owner Version Owner Possible Owner

Version

Instance Yes Yes SP2 No Yes

SP2

Table 9. Final state


10 responses to “Patching Clustered SQL Server Instances with SQL Server 2008 and SQL Server 2008 R2”

  1. […] don’t do that” with regards to PowerShell and Windows failover clustering in general (do you ever patch your instances of SQL Server 2008/R2?), but one of them said even their Windows guys do not either. I was on a con call with another […]

  2. Rob Vanderwal says:

    I am planning to upgrade in-place a clustered (2 node, active/passive) SQL Server 2008 instance to SQL Server 2008 R2. Would the approach be the same as if it were a patch?

  3. Mark says:

    Thanks for the help. Just wondering though… in a 2 node failover cluster is this really necessary? If you’re running the SP exe on the passive node, that shouldn’t cause a failover to that passive node so the process of making the passive node unable to host the Instance seems like it may be an extra step. I guess there could by chance be a random failover while you’re applying the SP on the passive node but that would be unlikely it seems. I would welcome your reply. I’m just making sure I fully understand this. Thanks, Mark

  4. Allan says:

    Yes, this is the process. The whole point is to ensure that while you’re patching the other node (or nodes – see my whitepaper), you can’t fail over there because it’s in a transient state. If you want to run that risk, that’s on you. As a guy who values availability and wants to minimize risk, I wouldn’t skip that step. BTW, this is also what is documented by Microsoft (albeit much harder to read) in the support KB, so I’m not making this up.

  5. Hans Lindahl says:

    I have a 2-node a/p 2008 R2 cluster that was installed almost 2 years ago. The nodes were installed with SP1 before creating the cluster. Recently I’ve discoverd that on the node which is normally passive the SP1 installation didn’t complete. Furthermore, I need to activate the “Filestream” function, which apparently has issues if not activated prior to SP1 installation. I therefor have decided on a rather lengthy process and would like your input as to whether or not all steps seem relevant.

    Current state:
    Node 1 normally Active, Reports SQL 2008 R2 SP1, However, Windows Update suggests applying SP1

    Node2 normally passive, Reports SQL 2008 R2 TRM.

    Step 1: Remove Node2 as possible owner
    Step 2: On Node2, uninstall SP1 (if possible), reboot
    Step 3: On Node2, Activate Filestream through Surface Configuration
    Step 4: On Node2, install SP1, reboot
    Step 5: Add Node2 as possible owner, failover.
    Step 6: Remove Node1 as possible owner
    Step 7-10: Repeat steps 2-5 for Node1.

    I should now have a working SP1 environment and be ready for upgrade to SP2. I think I’ll let it run for a week or two in complete SP1 status before upgrading.

    Your thoughts on this?

    Sincerely,
    Hans Lindahl, DBA @ City of Boras, Sweden

  6. Allan says:

    YOur steps do not go with what BOL says to enable Filestream after the fact http://msdn.microsoft.com/en-us/library/cc645886(v=sql.105).aspx. So on that basis alone, it looks like your steps are flawed. You should not have to remove SP1 to enable FS.

    I’m also not sure what problems you are referring to with not having it prior to SP1.

    How do you know that the second node is RTM? Did you see errors in detail.txt for the SP1 installation?

  7. Jagadeep says:

    do we have to follow the same process if we have CU/Hotfix followed by SP. I mean can we apply CU/Hotfix on the passive node after applying the SP or do we have to apply SP on other node then come baack and apply CU/Hotfix on both nodes??

  8. jay says:

    Hi the document link to your whitepaper has expired, is there anywhere else i can get it from?

Leave a Reply

Your email address will not be published. Required fields are marked *