sql cluster not failing over.

Heisenberg 261 Reputation points
2021-08-27T05:29:15.413+00:00

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.

Windows Server Clustering
Windows Server Clustering
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.Clustering: The grouping of multiple servers in a way that allows them to appear to be a single unit to client computers on a network. Clustering is a means of increasing network capacity, providing live backup in case one of the servers fails, and improving data security.
969 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Limitless Technology 39,461 Reputation points
    2021-08-27T19:03:16.673+00:00

    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 : )

    0 comments No comments