sql cluster not failing over.

Question

We are having issues with our SQL Server cluster failover instance. here is what is happening.

When cluster fails (manually or automatically) and ownership goes to secondary node, i see SQL Server service in "Running" state (on secondary node), SQL Agent stays as it is in stopped state, at the same time I see SQL Server resource in WSFC shows as "online pending" (on second node), it takes a long time in this state. Then it fails and all resources fails back to primary node.

After failing back to primary node WSFC shows SQL Server resource as "online pending" when i go to services i see "SQL server" service is in running state but SQL Agent stays in stopped state. It stays in "online pending" state for a long time just like it does in first failover and then SQL Server resource in WSFC goes into "failed" state.

Then what i do is on the primary node, go to services bring SQL Server service online by starting it manually, SQL Server service comes back up in running state and then i try to bring SQL Server resource in WSFC online by right clicking on it. It shows online pending for some time and then it fails again.

Next what i do is, go to services again and bring SQL Server and SQL Agent both services online one after another, they successfully starts and goes in "running" state. Then i try to bring SQL Server cluster resource online, it comes back online alongwith "sql agent" cluster resource.

Can someone tell me what is going on, why is WSFC not able to bring SQL resources online automatically and only way to bring sql server cluster resource online is by manually starting both sql server and sql server agent services manually and then manually bring cluster resource online.

Answer 1

Hello @SQLServerBro

The resource-specific registry keys that correspond to the SQL Server cluster resource that you are trying to bring online are missing. This problem also occurs if the values that correspond to the resource-specific registry keys are not correct.

However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs.

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

Hope this answers all your queries, if not please do repost back.
If an Answer is helpful, please click "Accept Answer" and upvote it : )