By: Thomas LeBlanc | Updated: 2009-01-19 | Comments (8) | Related: > 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.
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).
Figures 2 and 3 - Before and After moving the Possible Owners
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.
Figure 5 - Returning Possible Owners to SQL Server instance
Next Steps
- Again, I am thankful for the time and money the Amedysys' management has invested in our continued education with another visit to PASS.
- Microsoft has done a great service to us by adding the First Aid station and having it manned by support engineers. Thanks for your help Shon!!!
- As you need to address this issue, be sure to move MSDTC from Cluster Resource group to its own group.
- Use Group Affinity to prevent two resources from failing to the same physical machine.
- Check out these related tips on MSSQLTips.com:
About the author
Thomas LeBlanc is a Sr DBA and MCITP 05/08 DBA & 08 BI has spoken at the PASS Summit 2011/12, SQL Rally & many SQL Saturday’s.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-01-19
