Unable to enable SQL AlwaysOn High Availability when joined to specific cluster.

Question

question

DBA-Bandy asked Dec 14, '20 TrayceJordan-5446 answered Jun 21, '22

Unable to enable SQL AlwaysOn High Availability when joined to specific cluster.

Hello.

I'm having difficulty enabling AlwaysOn High Availability on any new server I add to a failover cluster. All servers are running Windows Server 2019 Datacenter. The server joins to the cluster with no issues.

The error states:
Unable to save the AlwaysOn High Availability settings [return code: 0x80041033].

There are servers already on the cluster that have always on enabled, so it worked at some point.

When we add the same server(s) that produce the error on another cluster we can enable always on without issue.
Therefore I believe the issue must lie with the cluster itself.

We can't find anything specific in the logs for this issue outside what I have listed.

Has anyone else come across this issue?

Thanks.

SQL Version: Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)

sql-server-general windows-server-clustering

· 3

5 |1600 characters needed characters left characters exceeded

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

Criszhan-msft · Dec 15, 2020 at 07:14 AM

Hi anonymous user-2453,

Unable to save the AlwaysOn High Availability settings [return code: 0x80041033].
There are servers already on the cluster that have always on enabled, so it worked at some point.

You mean that after a new server is successfully added to the failover cluster(WSFC), the Always on availability groups feature on SQL Server 2016 instance cannot be enabled in the SQL Server Configuration Manager on this server, and the above error occurs?But adding this server to another failover cluster can enable the Always on availability groups feature of the SQL Server 2016 instance on this server?
Please try to use PowerShell to Enable Always On Availability Groups.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/enable-and-disable-always-on-availability-groups-sql-server?view=sql-server-ver15#PScmd2Procedure
Can you find any relevant information in the event viewer logs when fail to enable the Always on feature.

0 ·

DBA-Bandy Criszhan-msft · Dec 15, 2020 at 01:17 PM

Hi Criszhan.

Yes, that is exactly what I mean.

We actually use the PowerShell command to enable always on normally but it had failed. The error I provided was from the GUI though. The PowerShell error is as follows:

Enable-SqlAlwaysOn : ChangeHADRService failed for Service 'MSSQLSERVER'.

Andy.

0 ·

DBA-Bandy · Dec 15, 2020 at 02:32 PM

We also get this in the the Application section of the Event log whenever we try to enable always on:

Windows Management Instrumentation has stopped WMIPRVSE.EXE because a quota reached a warning value. Quota: HandleCount Value: 37458 Maximum value: 4096 WMIPRVSE PID: 5332 Providers hosted in this process: C:\Program Files\Microsoft SQL Server\130\Shared\sqlmgmprovider.dll, C:\Program Files\Microsoft SQL Server\130\Shared\sqlmgmprovider.dll

0 ·

Answer 1 · 2020-12-16T06:15:48.113Z

Criszhan-msft answered Dec 16, '20 DBA-Bandy commented Sep 3, '21

Hi anonymous user-2453,

Windows Management Instrumentation has stopped WMIPRVSE.EXE because a quota reached a warning value. Quota: HandleCount Value: 37458 Maximum value: 4096 >>WMIPRVSE PID: 5332 Providers hosted in this process: C:\Program Files\Microsoft SQL Server\130\Shared\sqlmgmprovider.dll, C:\Program Files\Microsoft SQL >>Server\130\Shared\sqlmgmprovider.dll

It looks like this is a WMI issue. I would recommend you to read this blog-WMI: How to Troubleshoot WMI High Handle Count.

Based on the application log information you provided, because the handle quota is much higher than the set limit, you may need to perform data collection to find out the cause of the high handle count.

If possible, stop the always on availability group role in the failover cluster manager Before trying to enable the AOAG feature. But this will shut down the availability group and make the databases temporarily unavailable.Please decide based on your environment, this is just an attempt.

Please also refer to this similar case.

If the answer is helpful, please click "Accept Answer" and upvote it.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

· 3

5 |1600 characters needed characters left characters exceeded

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

DBA-Bandy · Dec 16, 2020 at 01:22 PM

Hi @Criszhan-msft

Thanks for all the suggestions. Sadly I had been through those documents. Been doing a lot of searching and so far nothing has gotten me to a resolution.

I will not be able to stop the always on roles at the moment as there are live databases. However this is something we will do if we can't find another method. At that stage we would likely migrate the databases to a new cluster.

I am still very much leaning towards an issue with the cluster itself given that these servers can join another cluster without issue.

Thanks.

Andy.

0 ·

Criszhan-msft DBA-Bandy · Dec 17, 2020 at 03:06 AM

Hi Andy,
Does the cluster log have nothing relevant? Is there any significant difference between successful and failed clusters,

0 ·

DBA-Bandy · Sep 03, 2021 at 01:16 PM

In the end we were able to use WMI: How to Troubleshoot WMI High Handle Count to assist in resolving the issue. We temporarily upped the HandlesPerHost to accommodate this.

0 ·

Answer 2 · 2020-12-17T03:02:20.357Z

Criszhan-msft answered Dec 17, '20 Criszhan-msft edited Dec 17, '20

Hi Andy-2453,

When the problem only occurs in a specific cluster, it does make us suspect that this cluster has something special.

Stop the role is an attempt, not sure if it is the solution, and in that post with a similar situation to yours, the questioner mentioned that he has reinstalled SQL Server and WSFC cluster, which seems to be of no use. Maybe you need to collect data then open a support incident.

5 |1600 characters needed characters left characters exceeded

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

Answer 3 · 2022-06-21T22:23:46.973Z

TrayceJordan-5446 answered Jun 21, '22

I recently ran into the same error message using PowerShell, though I had no WMI errors or handles issues.
Our issue was intermittent and only on the would be secondary.
Our theory was that the "secondary" (in quotes because it wasn't one yet, but designated to be) could not contact the cluster.
To confirm our theory, we shut down the cluster services and ran our PowerShell code - and we reproduced the issue at will.
So more than likely the problems above were because the cluster could not be contacted.

On another note - a "cluster" is more than a "name" - so if you delete everything to do with a cluster, but don't disable AlwaysOn feature, even if you create a new cluster with the same name, it won't work properly. You would have to disable the AG feature and then re-enable the AG feature (restarting each time) before you would be able to successfully get the feature installed and running properly.

5 |1600 characters needed characters left characters exceeded

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

question