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).

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.

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.

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

Yay! All is right with the world again.

Now, what happens if you use 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:

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?

1. Be careful if using PowerShell to add or remove nodes and using quotes around the nodes. Doing that wrong may have unintended consequences.
2. 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.