1

I'm testing a PowerShell Disaster Recovery script and trying to fail over to an off-site AG DR (asynch) replica with this command

ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS

On several clusters this works fine but on a couple of other ones it usually fails (but not always) with the error:

Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5023). The WSFC service may not be running or may not be accessible in its current state, or the specified cluster group or node handle is invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.

Failed to designate the local availability replica of availability group 'MyAG' as the primary replica. The operation encountered SQL Server error 41018 and has been terminated. Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

We have to force the quorum simulating the situation where the async DR replica can't communicate with the other replicas: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-2017

A comparison of the clusters that work vs. those that don't with Get-Cluster and Get-ClusterGroup doesn't reveal any major differences.

This failover has worked successfully on some clusters but fails 99% of the time on two of the clusters. It has worked on these two clusters, but rarely. One of the common issues I found from a Google search is permissions for NT Authority\System (see https://dataginger.com/2014/10/28/sql-server-failed-to-bring-availability-group-availability-group-name-online/), which I confirmed it has.

I ran a trace and see the same errors and no other clues in the trace.

If nothing turns up I may have to open a Microsoft ticket to engage their support.

Any thoughts?

Thanks for your help!

|improve this question
0

This error may happen if your SQL Server service is running under user who does not have the needed permission on your servers or the account is locked. try to use another account and make sure account is unlocked.

If that did not fix the issue then you need to edit your regisery (make sure you take a backup first)

  1. open start menu,write regedit then press ok
  2. navigate to 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:
    For a named instance of SQL Server:

    Value Name: InstanceName
    Value Type: REG_SZ

    Value Data:
    VirtualServerName

    Value Name: VirtualServerName
    Value Type: REG_SZ
    Value Data:

4- quit registery editor

|improve this answer
  • Thanks, @ahmad-abuhasna. For the registry part, can you please elaborate? Do you have any information on what is being fixed or why you believe this to be an issue? Why would these registry keys not exist on some clusters? Also, I see multiple GUID folders, so which one should I change? – Mike Petrak Apr 2 '19 at 13:44
  • Those regisertries are responsable for SQL cluster resources.I usually start my troubleshooting by checking the steps I provided (they are safe provided by MS support.microsoft.com/en-us/help/810056/…) there is a lot of reasons why those regiserties do not exist on every clsuter environment: for exmple: they were manually removed, there is a policy that restriced creating speicifc registeries on your system, or there were an error during the installation. my advise to monitor cluster master since it is very sensitive – Ahmad Abuhasna Apr 3 '19 at 6:13
  • @MikePetrak if those reigsteries did not fix the issue please try to execute the command again and check what exaclty in event viewer and provide us with event viewer error. – Ahmad Abuhasna Apr 3 '19 at 6:14
  • Thanks,@ahmad-abuhasna. I was able to resolve this issue by waiting until the cluster state was online and stable with FixQuorum = 1. I didn't have to touch the registry keys. Thanks again for the help. Have a great day! – Mike Petrak Apr 4 '19 at 13:12
0

Thanks for the help everyone. I troubleshot this issue today and it turns out that some of the clusters are taking much longer for the WSFC to actually come up and be ready for the failover. I put a loop with a try, wait, and verification to work around this stability issue.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.