Troubleshoot Sql Server cluster and ClusterRecovery

With some San disk configuration and drive letter changes, a two-node 64-bit Sql Server 2005 cluster on Windows 2003 boxes would not start, as evidenced in Cluster Administrator. Here are some errors in Event Viewer:

Sql Server application error in Event Viewer
[sqsrvres] OnlineThread: Error 435 bringing resource online.

Sql Server Agent
[sqagtres] CheckServiceAlive: QueryServiceStatus failed (status 6).

Some research didn’t turn out anything. Since this issue was fairly urgent, I called Microsoft PSS and put in an urgent request. I was connected to a very nice support person out of Bangalore, India.

We then tried to start Sql Server from services applet on the local active node, to no avail. Next we looked at Sql Server startup parameters, located at HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters. It turned out that those parameters are pointing to the wrong drive, because I know Master database is not there.

So I manually changed the drive letter, then went back to Cluster Administrator to start Sql Server. It failed again. And the odd thing was, the startup parameter would revert the drive letters back to the wrong ones.

So support supplied me with a tool called ClusterRecovery.exe. And we were able to use this to make the registry drive letter change stick. Here is what we did:

1. Connect to Sql virtual server name (not the underlying Windows cluster virtual name);
2. Pick Restore cluster resource checkpoints;
3. Click Next, then pick Sql Server from the drop-down;
4. Proceed with registry change;
5. Do the restore on ClusterRecovery tool.

We also changed the drive letters for Sql Server agent, using the same method, at this registry location:
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent

ClusterRecovery.exe is available for free download.

Regarding PSS, in some cases I found that I actually know more than Microsoft support guys. To me, the most valuable thing is it provides a sounding board. Usually, when you are calling support, you are likely to be in a stressed mode. A knowledgeable person acting as a sounding board is invaluable, since s/he can remind you things you may have overlooked, provide you with a new angle to look at things. You are speaking to a person at your level with a language you both understand, instead of having to dumb things down for your manager, who, depending on his/her personality, may actually make things worse. Through this kind of peer to peer sharing and brainstorming, a solution can usually come out. It may not be a silver bullet all the time. But when it is, that can really make your day. So thanks, Jayaraj, if you are reading this.

I also like PSS’s EasyAssist, which is a screen-sharing tool. It allows both you and the support person to look at things together, which is invaluable in my opinion. Sometimes I found PSS’s request of log files tedious, because I have looked at them already and I’d rather do the screen sharing so we can look at them together. But I understand that for less experienced professionals, that is necessary. Whether you get a nice support person depends totally on your luck.

Hope this will help somebody. If it helps you and you feel obliged, do a donation through my email at PayPal. I promise I will put it into good use 🙂

Update: My blogging buddy and fellow PASS volunteer K. Brian Kelley commented here, that points to a kb article that describe this issue. Thanks a lot Brian!

3 responses to “Troubleshoot Sql Server cluster and ClusterRecovery”

  1. The ClusterRecovery tool specifically states it is not to be used on 64 bit systems…hmmm

  2. Definitely DO NOT run this on a 64bit cluster. I did it and it did solve the immediate issue, but created many more errors. I can no longer use database mail or maintenance plans.

  3. Thanks guys for the comment. Although I’ve done it more than 2 years ago, and didn’t see anything wrong with it, it is probably a good idea to be careful with it. Clustering is very touchy-feely.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.