SQL Server 2017 Availability Group now thinks it is in a cluster - it isn't and never was!

Question

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

Answer 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.