SQL Server 2019 DAG WFC - Manual Failover won't work (MSSQL Error 41131)

Question

We set up a distributed failover cluster with 2 Windows Server 2019 Datacenter nodes, each of them running SQL Server 2019 Enterprise + SSMS18.

• The two nodes are located in two different sites with two different IP-Subnets.

• Each Host is a ESXI VM with only one NIC (Host A in Subnet A, Host B in Subnet B).

• Both sites are connected via a S2S-VPN Connection and routing possibilities for traffic between.

Problem

We double checked every possible problem, but we cannot get managed, to manual failover an AvailabilityGroup with a synchronized DB via SSMS

• Instance -> Always On High Availability -> Availability Groups -> -> Right-Click "Failover"

  • SQL Server error 41131 (see attachment)

Troubleshooting

• Connection between hosts is up and the "dashboard" shows, that both hosts are communicating, up and synchronized.

• Defender Firewall rules are there for the DAG-listeners, the Agent, the Browser service. On a PaloAlto Firewall at site A, traffic can be detected between both SQL hosts, but no traffic is denied.

• Both hosts run via a separate service user for SQL Server Agent and SQL Server engine, so there should not be any trouble with missing rights for the NT Authority\SYSTEM.

Rights to the AD-Clusterobject are there, to create and update any child objects. Two DNS entries for the listener and one for the cluster object are also there after the creation.

Even the automatic seeding between both hosts is working, only the failover through SMSS18 is failing (inserted rows replicate from host A to host B).

Questions

Are there any ideas, at which point we can troubleshoot?

I attached the Error-Message, but was not able to find any useful information online, since the only connected solution is always to change rights for the NT Account, which we do not use for Agent or Engine.

Answer 1

SQL Server error 41131 (see attachment)

and

We double checked every possible problem, but we cannot get managed, to manual failover an AvailabilityGroup with a synchronized DB via SSMS

Error 41131 only appears when the availability group inside of SQL did not get a good signal that it has come online from the cluster side and hit a timeout (5 minutes). This is going to be something that needs to be investigated, initially, from the cluster log.

This was already suggested by Gaurav Rathod, in the comments:

You may want to check cluster logs and SQL errorlogs on both replicas to get some clue.

However, the answer to this question, also a comment (in case they've been cleaned up):

Unfortunately, we also even did that. There are no usefull entries or errors logged.

I can assure you there is most definitely an entry in one of those logs that gives a hint of what's going on. If, after the initial combing through, you don't believe there is anything helpful, it may be time to get someone (consultant :wink:, Microsoft Support, etc.) else involved that is used to troubleshooting these types of issues.

Sometimes it's helpful, if it's easily reproduceable, to set the logging level to debug for the cluster before reproducing the issue - just to add more detail, though the default level should have something to get you started.

Typically, in these scenarios. you'll want to start by finding the MoveGroup call to see where the cluster started the move on the destination node. You'll then be able to follow the logic the cluster is taking and any errors that occur while bringing the resources for the group, online.

Answer 2

Thanks for all of you, who invested time to point to the right direction. I was able to solve the issue:

As @gurav mentioned, I indeed had to dig deeper in the ClusterLogs. After the Xth time reading every single line, I was able to find these line in the logs of the second node and only the second node:

[Verbose] 000013a0.00002834::2022/08/10-15:19:32.291 ERR   [RES] SQL Server Availability Group <GroupName>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Registry information is corrupt or missing. Make sure the provider is installed and registered correctly. (27)

So this phenomenon was described and solved here. The author of this is the hero we deserve.

So I checked the RegKeys from the website on both nodes and found exactly what was written in the blogpost (missing keys on Node B). So I exported the missing RegKeys and checked the SQL Configuration Manager, if the missing protocols were there after that - they were.

First try to failover the AG after the fix via SSMS succeded. Unbelievable problem.

Error 41131, online always refered to missing NT rights, came from a broken Registry, and only God knows where this came from on only one host after a clean install on a fresh system.