0

We have a read-scale availability group on SQL 2017 that for years has worked without fault. Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "thinks" it's part of a WSFC -- however, it isn't part of a WSFC and never has been. It's a standalone workgroup server. The other read-scale replicas of this AG are still working correctly -- it's all very strange. Do you know how to modify the AG to be clusterless? I don't see a way to do it with an active AG (i.e. I see it in the CREATE, but not the ALTER) -- however I have no idea how the AG is working when it thinks it's in a cluster but clearly is not. Any advice?

Error Info/ When a join is attempted with "ALTER AVAILABILITY GROUP {AG_Name} JOIN WITH (CLUSTER_TYPE = NONE)", it fails with the following:

Msg 47100, Level 16, State 0, Line 1 The cluster type of availability group '{AG_Name}' doesn't match its primary configuration. Verify that the specified availability group cluster type is correct, then retry the operation.

Msg 41080, Level 16, State 0, Line 1 Failed to delete SQL Server instance name to Windows Server Failover Clustering node name map entry for the local availability replica of availability group '{AG_Name}'. The operation encountered SQL Server error 35222 and has been terminated. Refer to the SQL Server error log for details about this SQL Server error and corrective actions.

Msg 41158, Level 16, State 3, Line 1 Failed to join local availability replica to availability group '{AG_Name}'. The operation encountered SQL Server error 47100 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command.

sys.dm_hadr_cluster returns the following (cluster_name is blank): cluster_name quorum_type quorum_type_desc quorum_state quorum_state_desc

0   NODE_MAJORITY   1   NORMAL_QUORUM

sys.availability_groups returns 0 for cluster_type and wsfc for cluster_type_desc

sys.hadr_cluster_members returns one row: {primaryservername}

0 CLUSTER_NODE 1 UP NULL

3
  • (the "refer to SQL Server error log for more details" in the errors above -- we checked, and no entry was made in the SQL Server error log)
    – Stephen H
    Sep 19, 2023 at 16:17
  • Were you able to find anything in the errorlog/xe data? Sep 21, 2023 at 12:55
  • Nothing of note is in there -- very strange! - thanks for asking!
    – Stephen H
    Sep 22, 2023 at 13:05

1 Answer 1

1

Do you know how to modify the AG to be clusterless?

It's not possible, it would need to be dropped and re-created.

Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "thinks" it's part of a WSFC [...]

Please post the actual error, output from the DMVs (sys.availability_groups, replica, etc), and other relevant information if you'd like further help.

sys.availability_groups returns 0 for cluster_type and wsfc for cluster_type_desc

Then it's a WSFC integrated AG, that metadata can't be changed. Either that's how it's always been, someone created a new AG with the same name on a different node, or someone dropped the old one and created it again with the same name but not the proper cluster type.

This could have happened who knows when. You could try checking the XE data if it's been setup to run (by default it doesn't, if the wizard is used to setup the AG it'll enable it) and see if anything happened recently but that's a long shot.

Going through the errorlogs, if available, from previous days/weeks/months may also have some tidbits of information.

The operation encountered SQL Server error 35222 and has been terminated.

This is a generic error that SQL Server was unable to access the cluster database, which would make sense if you don't have a WSFC or it wasn't properly set up. Since there doesn't seem to be an actual cluster setup, this would be expected.

2
  • Thanks for the response, Sean! I updated the questions with more error, cluster, and ag info.
    – Stephen H
    Sep 19, 2023 at 16:14
  • Marked the answer as "accepted" because Sean correctly let us know there is no way for us to do what we were hoping to do. (for curious folks, we're ending up creating a new clustered AG and migrating -- the clustered AG has benefits over the clusterless that are now valuable enough to us to make the investment worth it)
    – Stephen H
    Sep 22, 2023 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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