SQL Server 2008 Service Pack 2 Archives

May 10, 2011

Patching Clustered SQL Server Instances with SQL Server 2008 and SQL 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

February 7, 2011

Scripting the Addition and Removal of Cluster Nodes as Possible Owners of a Resource

Currently I’m writing a whitepaper on how to best approach installing updates on a multiple instance failover cluster. I’m using Windows Server 2008 R2 as the underlying operating system. As part of my testing, I’m automating a bunch of things via a combination of PowerShell scripts and scripts to run the SP install without having to do anything in the GUI. The scripts will be available as part of the whitepaper.

As some of you may know, part of the process for installing a SP or CU on a clustered instance of SQL Server is removing some nodes as possible owners. This is done on the Network Name resource. You have three ways of accomplishing this task: GUI (Failover Cluster Manager), cluster.exe (deprecated in Windows Server 2008 R2, but it’s still there for now), and in PowerShell. Because cluster.exe is going away, I’m using all PowerShell. Doing things in the GUI is more time consuming, and as I said, I want to automate as much as possible.

I wrote about this in my book Pro SQL Server 2008 Failover Clustering in Chapter 8. However, I did show some wrong screen shots for PowerShell – I showed removing possible owners of the SQL Server resource with PowerShell. That is wrong. I showed it correctly with cluster.exe. So that’s one errata for the book. I was also using the beta of Windows Server 2008 R2. Now that it’s released, I can update you here with the new stuff.

My cluster has three nodes: DENNIS, TOMMY, and JY. I’m currently in the midst of updating the EQUINOX instance from SQL Server 2008 SP1 to SQL Server 2008 SP2. I can remove TOMMY and JY from the network name resource’s possible owners with no problem using PowerShell. Set-ClusterOwnerNode has an -owners option, and my understanding is that it was always implicit. It is … but only sometimes. Let me show you.

Below is adding the nodes working as expected using cluster.exe (which I do not want to use; this syntax currently works on Windows Server 2003, Windows Server 2008, and Windows Server 2008 R2).

Adding the nodes back using cluster.exe

Let’s see what happens in PowerShell.

First, here is the removal of the other nodes as a possible owner so I can do the patching on those nodes. Note that I do not use the -owners clause. I apologize if the formatting of the following pictures looks a little weird on your screen.

Removing nodes to patch

Things are working as expected. So far, so good. Now, let’s try to add the nodes removed back as possible owners.

First, set the nodes to all three not using the -owners clause.

Adding the nodes back not using -owners with error

Clearly, that didn’t work. Now let’s try it with the -owners clause.

Adding the nodes back successfully with -owners

Yay! All is right with the world again.

Now, what happens if you use quotes around each node?

Putting quotes around each node

As expected, this works just like it would if the nodes were not encased in quotes.

This whole -owners inconsistency is not a hindrance, and clearly you can get everything to work just fine.

However if you just use the list all encased in quotes, you will encounter this:

Error when specifying all nodes encased in quotes

What happened? Well, it not only didn’t add or remove the two nodes I wanted, but it removed ALL of the nodes as possible owners because -owners parses everything encases in quotes and expects it to be a node name. There is no node named DENNIS,TOMMY,JY so it removes all nodes as possible owners. It seems to treat it like a NULL or something like that.

This is behavior I hope is changed in a future version of Windows so that it will leave the current configuration alone or only allow removal/addition of nodes which currently do not own the resource. That way the resource, and possibly the resource group if there are dependencies (such as SQL Server being dependent on the Network Name), won’t accidentally go offline.

So what are the lessons to be learned?

Be careful if using PowerShell to add or remove nodes and using quotes around the nodes. Doing that wrong may have unintended consequences.
Adding and removing nodes using PowerShell is nearly opposite of what you would think. Since it is a single command, you specify all nodes you want to own that resource. With cluster.exe you had separate switches for adding and removing, so it was a bit more intuitive.

November 9, 2010

UPDATE – Combining Clustering and Log Shipping Bug

I’m out here at PASS this week and was in the CAT area. Cindy Gross (who works for MS and blogs here) hooked me up with someone who let me know that what I found back in May (where if you changed your path for install, things like log shipping wouldn’t work after failover) has been fixed. This is GREAT news.

The fix can be found in SQL Server 2008 SP1 CU10 and it didn’t get finished in time for SP2, but it will be in SQL Server 2008 SP2 CU1.

So how do you deal with the problem if you’ve already deployed?
Well, installing the CU won’t fix the issue. You’d have to slipstream the CU into your 2008 install, remove the node, and re-add the node to SQL Server. I am not saying evict the node in Failover CLuster Manager – just run the normal remove process for SQL Server.

How do you ensure that all new deployments do not have this issue?
Slipstream the CU before running Setup.

Popular Posts

Categories