Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

Cluster resource 'SQL Server (<SQL Server instance name>)' in Resource Group '<Cluster group name>' failed. - SQL Server Knowledge Sharing Network (@sqlmaster)

Cluster resource 'SQL Server (<SQL Server instance name>)' in Resource Group '<Cluster group name>' failed.

A common mistake by DBAs for missing to check whether all the functionality of tools are working when a SQL Server Cluster environment is setup, though you may not catch hold of few issues with basic failover/failback testing initially but when the application is initiated and data is stored with relevant scheduled jobs and processes the nodes funcationality may be different to each other. So in this case it is a best practice to document and perform a failover once the application usage is in peak within the DBMS environment.

Recently we have had such problems where we missed 2 cycles of failover tests between the nodes, so during this time when we had issue with active node the SQL has been failed over to other node and thats where when tried to start SQL Server agent it hangs, and goes back to 'Failed' status. As usual I was going through the SQLAgent logs and Event Viewer logs for any information on SQL Server services account privileges, nothing was wrong or no such warnings or error messages on the log.

After a strolling through the MSDN and KBA (Knowledge Base Articles on Cluster) I was able to pick the root cause of this error, rather its a bug fix: The resource-specific registry keys that correspond to the SQL Server cluster resource that you are trying to bring online are missing. Not only this it will occur if the correspdoing value of Virtual server name is missing/not correct on resource-specific registry keys.

KBA 883732 talks the resolution as follows:

To resolve this problem, you must manually re-create the resource-specific registry keys that correspond to the SQL Server cluster resource. To do this, follow these steps:

1. Click Start, click Run, type Regedit, and then click OK.
2. In Registry Editor, locate and select the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources\<GUID>\Parameters
3. Create the following registry values in the Parameters registry key:

For a default instance of SQL Server:
InstanceName

Value Name: InstanceName
Value Type: REG_SZ
Value Data: MSSQLSERVER
VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data: <Name of the virtual SQL server>
For a named instance of SQL Server:
InstanceName

Value Name: InstanceName
Value Type: REG_SZ
Value Data: <SQL Server instance name corresponding to the virtual server>
VirtualServerName

Value Name: VirtualServerName
Value Type: REG_SZ
Value Data: <Name of the virtual SQL server>
4. Quit Registry Editor.

After you create the resource-specific registry keys, you can bring the SQL Server cluster resource online successfully.

If you notice that a SQL Server Agent cluster resource cannot be brought online, you must create the same set of resource-specific keys that correspond to the SQL Server Agent cluster resource.

This is where I have got root cause for above error and for more  information review 810056 - You must use resouce-specific registry keys for SQL Server cluster resouces

Published Thursday, March 13, 2008 9:05 AM by ssqa.net

Comments

No Comments