question

SQLServerBro avatar image
0 Votes"
SQLServerBro asked Ben-Miller-DBAduck answered

sql server cluster failover issue.

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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @SQLServerBro ,

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.

No. This is abnormal. Both sql server and sql server agent services are cluster resources. Both sql server and sql server agent services are cluster resources. When the failover is completed, the resource will be online on the active node.

Please see: https://docs.microsoft.com/en-us/troubleshoot/sql/failover-clusters/cluster-resource-goes-failed-state
If your problem is not resolved, can you post some error messages like the symptoms in this document?


Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLServerBro avatar image
0 Votes"
SQLServerBro answered SQLServerBro edited

hello,
Registry values are already set, pls see below screenshot.

127556-image.png


127618-image.png



image.png (49.7 KiB)
image.png (54.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @SQLServerBro,

Are you setting the value in the parameter of the corresponding cluster resource?

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.

For more information, please see this doc:
https://docs.microsoft.com/en-US/troubleshoot/sql/failover-clusters/manually-re-create-resource-specific-registry-keys

Best regards,
Seeya
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLServerBro avatar image
0 Votes"
SQLServerBro answered SeeyaXi-msft commented

hello Seeya, im not setting any parameter value explicitly, this is how it was when configures the cluster. If you see first screen shot that will show you parent tree value with name as "SQL Server" beneath that i has subfolder with name "parameter" under which you will see values like "instancename" virtual server name and so on.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLServerBro,

You should manually re-create the resource-specific registry keys that correspond to the SQL Server cluster resource.
According to your description, you also need create the same set of resource-specific keys that correspond to the SQL Server Agent cluster resource.

Best regards,
Seeya

0 Votes 0 ·
Ben-Miller-DBAduck avatar image
0 Votes"
Ben-Miller-DBAduck answered

Your first line of defense is to find the SQL Server Errorlogs and see what is going on in them. If SQL does not come online in the cluster (you should really never start or stop the SQL Services in Services when it is clustered) then the Errorlogs could give you a good indication of what SQL is doing behind the scenes and if there are failures in the logs, then you can resolve them first.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.