Error Creating AlwaysOn Availability Group

This month blog post, I would like to share an error case on creating sql server availability group.

I had a pair of sql servers, formed a WSFC, and enabled alwayson availability group feature in SSCM. But later our system administrator said he need to destroy and create again the WSFC. After the WSFC re-created, I tried to create availability group, then I get the following errors:

"Failed to obtain cluster information. Either the specified instance of SQL Server is not running on a Windows Server Failover Cluster (WSFC) node, or the user lacks sysadmin permissions on the SQL Server instance to obtain the cluster information."

"The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster (WSFC) node."

Here are the troubleshooting steps:

1. Double check availability group feature enabled in all sql replicas.

2. Check your current login got sufficient right to create availability group in all sql replicas, for simplicity, sql server sysadmin server role.

3. Check cluster permission by right-click windows cluster in fail-over cluster manager -> Properties -> Cluster Permissions

By default, the Local Administrator group has Full Control on the WSFC. So for simplicity, the sql sysadmin login you are using should be added to the OS local administrator group too.

4. Run the following sql queries on each sql replica:
SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM sys.dm_hadr_cluster;
It should return some rows like below:

But if no any rows returned, then that sql replica needed to disable and re-enable availability group feature in SSCM.

Solution:
Disabled alwayson availability group feature on the “bad” node using SSCM, then restarted the sql service. Then enable availability group feature again followed by another sql service restart. After finishing this, you will able to get information about the cluster and nodes via the query which I mentioned earlier.