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)

AlwaysOn, SQL Scripts, SQL Server, SQL Server Cluster, SQL Server Configuration
Previous Post
SQL SERVER – SQL Service Not Getting Started Automatically After Server Reboot While Using gMSA Account
Next Post
SQL SERVER – FIX: Database Diagram Error 15517 – Cannot Execute as the Database Principal Because the Principal ‘dbo’ Does Not Exist

Related Posts

1 Comment. Leave new

  • You asked for feedback on the steps that cause this issue when encountered.

    Our storage is now NIC connected and not HBA, so when a mistake during network maintenance caused servers to lose connectivity to storage, my servers went down hard. All the servers are virtual on VMware hosts. The primary server in a cluster flat failed to come back up and had to be removed from the AG and then the cluster. A new server was spun-up with the same name and IP config. It added to the cluster with no problem, but we encountered the error referenced in this post when trying to add it as a replica to the AG. I followed your directions and it cleared up the issue. Thanks for the posting. You Rock!

    Reply

Leave a Reply

Menu
Exit mobile version