Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Updating the Passive Node in a SQL Server 2005 Cluster


By:   |   Last Updated: 2009-01-19   |   Comments (8)   |   Related Tips: More > Clustering

Problem

Somewhere during the installation of a three node Active/Active/Passive clustered environment, the Service Pack and Hot Fix did not get properly updated for just one server of the third node in the cluster. This was discovered after the instance failed over to this node and some applications were not running properly. A check from Management Studio showed the instance on version 9.00.1399 of SQL Server 2005. We are currently on Hot Fix 3282, which requires SP 2a. After research with Microsoft Premier Support, it was undetermined how this happened.

Solution

Again, our company sponsored trip to PASS pays off. One thing I have discovered about going to a conference is always come back with something valuable for management. Instead of attending the "here is what's new and you probably won't use it for a year or 2" sessions, I have become determined to learn 1 or 2 more valuable tools for what we are doing today. Here at Amedisys, Inc. (http://www.amedisys.com), we perform all system changes during a Maintenance Window where the IT department can take systems offline to make production and sometimes development and test system changes. We do not make changes like these during normal business hours which is 24 x 7 for us.

Another DBA at our company had been communicating with the CSS team at Microsoft through our Premier Support agreement for about a month with all suggestions coming up empty. PASS had a Microsoft First-Aid station for attendees. A Sr. Escalation Engineer with Cluster Environments was in the room when I entered. The gentleman had done a cluster presentation at the last PASS conference I went to, so this was a good start.

He started by explaining in detail about a Cluster Environment, which there are many other articles on this site that can be read. The tip that helped us was Possible Owners on a resource.  This is where the Service Pack or Hot Fix installation program determines what servers to update.

sql server properties

Figure 1 - Properties of SQL Server instance from Cluster Administrator

First step was to fail the Resource Group to the third node in the cluster. So, during our monthly maintenance window, we brought the systems offline and failed over the Resource Group.

Next, we removed the first and second nodes from the Possible Owners. Possible Owners are the physical servers that this resource can be hosted on. Basically, we are telling the Cluster Manager that only the third node can host this Service Resource. The installation program will only update the possible owner server(s).

modify possible owners sql server properties

Figures 2 and 3 - Before and After moving the Possible Owners

sql server properties

Figure 4 - Update Properties of SQL Server instance

The engineer suggested to go to the Console of the physical machine, but said I could try to remote to the node. I tried to remote to the third physical server and to the virtual server but the update was failing.

After trying a console remote (mstsc.exe /console) into the virtual server name, the installation of Service Pack 2a started to succeed. Another warning I was given from the Sr. Escalation Engineer was to not have any remote sessions into any of the other nodes on the cluster. You can use Terminal Services Manager to check other nodes for remote sessions.

The Service Pack and HotFix succeeded and now we will see if we can get back some of those hours for our Premier Support call. Also, we are going to give this information to the support people at CSS for the next company that has a similar problem.

Before bringing the systems back online, we needed to add back the 2 physical servers removed to the instance resource as possible owners.

modify possible owners

Figure 5 - Returning Possible Owners to SQL Server instance

Next Steps


Last Updated: 2009-01-19


get scripts

next tip button



About the author
MSSQLTips author Thomas LeBlanc Sr. DBA Thomas LeBlanc MCITP 05/08 DBA & 08 BI has spoken at the PASS Summit 2011/12, SQL Rally & many SQL Saturday’s.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, December 20, 2010 - 3:45:32 PM - Amadeo Back To Top

Thomas God bless you :)

Tx a lot ...


Tuesday, February 24, 2009 - 12:14:41 PM - michprat Back To Top

We had this same issue on a two node cluster. We had build a single node cluster and then added the second node. http://msdn.microsoft.com/en-us/library/ms191545.aspx This left the cluster with two mismatched versions, which the ms documentation and the install to the second node warns you about. Problem is that it isn't easy to get the new node up to the latest patch level. I had to shut down the primary node and install the service packs and hot fixes to get them to the same patch level. (This is essentially the same thing as removing the servers from being possible owners.)

 Once they were at the same patch level, I thought that I would be able to patch the two node cluster together with SP3. Not so... failed each time with this error:

02/24/2009 07:56:22.246 Remote process completed for product instance target
02/24/2009 07:56:22.246 Exit code for passive node: TESTSQLCLUSTERB = 1325
02/24/2009 07:56:26.158 The following exception occurred: No passive nodes were successfully patched  Date: 02/24/2009 07:56:26.158  File: \depot\sqlvault\stable\setupmainl1\setup\sqlse\sqlsedll\instance.cpp  Line: 3510
02/24/2009 07:56:30.995 Watson: Param1 = Unknown
02/24/2009 07:56:31.010 Watson: Param2 = 0x2b01
02/24/2009 07:56:31.026 Watson: Param3 = Unknown
02/24/2009 07:56:31.026 Watson: Param4 = 0x0
02/24/2009 07:56:31.042 Watson: Param5 = [email protected]
02/24/2009 07:56:31.057 Watson: Param6 = Unknown
02/24/2009 07:56:31.073 Watson: Param7 = SQL9
02/24/2009 07:56:31.089 Watson: Param8 = [email protected]
02/24/2009 07:56:31.104 Watson: Param9 = x64
02/24/2009 07:56:31.120 Watson: Param10 = 4035

I had to apply SP3 and CU 4207 with one node shut down at a time.

Strange that I am unable to patch with both nodes up once they are at the same patch level. There must be a registry entry or something like that missing. We have two instances and both fail over to either node successfully.


Friday, January 30, 2009 - 3:15:57 PM - Markh Back To Top

HI Thomas, Thank you for the clarification, I was looking at the Cluster group and not the Server, once I set the the new node as the only perfered server and ran the SP install it worked successfully.

 God Bless, Mark

 

P.S. The following link that I posted before did not work because it won't allow for the SP to be uninstalled.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=88972&enterthread=y


 

 

 

 


Thursday, January 29, 2009 - 11:23:53 AM - ThomasLL Back To Top

The Possible Owners and Preferred Owners are 2 different properties on different items.

 In Cluster Administator, If you Right-Click the virtual server name in the Groups tree (SVR-SQL-DEV02 in my example) and go to properties, you will see Preferred Owners for the entire Resource Group.

 But, if you left-click on the virtual server name in the group tree on the left, then Right-click the "SQL Server" resource Item in the list view on the right, go to Properties. There you will see Possible Owners.

The installation program is looking at the SQL Server instance installed on the virutal server, not the virtual server. The virtual server has move than just the instance: Disk M:, SQL IP Address (svr-sql-dev02), SQL Network name (svr-sql-dev02), Sql Server (instance name), SQL Server Agent and SQL Server Fulltext Index.

 Hope this helps.

 God Bless,

Thomas


Wednesday, January 28, 2009 - 9:35:50 AM - Markh Back To Top
 Hi Thomas,Thank you for your post on this issue, having trouble finding answers...

We are running Windows 2003 with SQL 2005 (SP2, CU2) in an active\active configuration and we are attempting to add a third node to the cluster. In Win 2003 the options are different than that of the screen shots.

In the pictures it reads: Available Nodes and Possible Owners

On Windows 2003 it reads Available Nodes and Preferred Owners

 

Since the "Preferred Owner" does not limit which node can host the instance it still tries to install on all nodes.

 What version of OS are you running?

 

I also found this post... trying the uninstall SP and hotfix option to bring it to the default version and then reinstalling

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=88972&enterthread=y

 God Bless, Mark

 

 

Thursday, January 22, 2009 - 7:47:26 AM - ThomasLL Back To Top

His intial responce says he will get a KB together with some better comments, but this is what he started with:

Well for basic questions he can refer to "How to: Recover from Failover Cluster Failure in Scenario 1"  -- From Books online.

 

God Bless,

Thomas 

 


Tuesday, January 20, 2009 - 6:41:32 PM - ThomasLL Back To Top

I will see what I can to do...
 
I do know once the passive node is setup in the windows cluster, you sql server run setup again from Add/Remove programs... on the active node and this will add the passive node to the SQL Server installation. (go thru the Change steps and it will prompr you for the new node). Then, you can follow this article to get it on SP2a and whatever hotfix you are using.
 
Thanks and God Bless,
Thomas


Monday, January 19, 2009 - 3:26:02 PM - greganson Back To Top

Hello,

 Can you tell me who at MS you talked to regarding this tip?  I wasn't able to make the PASS conference this year but I have a similar question regarding modifying and existing active/active cluster to add a passive node.  Not too much info out there on the subject of adding to an existing cluster and I don't have a non-prod environment to test this in...so getting it right the first time is crucial.  Any info would be appreciated.


Learn more about SQL Server tools