SQL SERVER – FIX: The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) Node.

Sometimes if the sequence is not followed correctly, we might see some weird errors. In this blog, we would learn how to fix always on an error while adding new replica “The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node.”

This was one of my existing clients to whom I worked and configured Always On Availability Group. They had some issues with nodes, and they ended up in rebuilding the cluster.

Later, they were able to redeploy and bring the cluster back. Now, when they were trying to add a replica Node2 from Node1 via SQL Server Management Studio, an error was displayed.

Here is the text of the error message

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)
 ——————————
Program Location:
 at Microsoft.SqlServer.Management.Hadr.SelectReplicasController.VerifyConnection(UIConnectionInfo ci, IServerType serverType)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

From the message is clear that there is something wrong between SQL and Cluster communication. I asked them to run below command on all nodes which are part of the windows cluster.

SELECT *
FROM sys.dm_hadr_cluster_members
GO
SELECT *
FROM sys.dm_hadr_cluster

When we ran the query on the node which was giving an error, there were no rows.

WORKAROUND/SOLUTION

We disabled Always On Availability Group on the “bad” node using SQL Server Configuration Manager. Once done, we restarted the SQL Service. Then we enabled it again followed by another SQL Service restart. After finishing this, we were able to get information about the cluster and nodes via the query which I mentioned earlier.

Have you seen such an issue earlier? What were the steps to break it?

Reference: Pinal Dave (https://blog.sqlauthority.com)