SQL server tables unavailable on failover cluster

Question

I have a failover cluster with two nodes. There is a shared IP address for the cluster and a domain name (SqlServer) for that shared IP. Sql Server is installed on both nodes and high availability groups are setup for tables in the database in SQL. These high availability groups show up as roles in my failover cluster manager and all tables in both clusters are synchronized.

Sometime the databases will be unavailable. I can connect to SqlServer with SSMS but I can not expand any of the databases and all queries fail. If I pause the first node and drain roles so all roles move over to node 2 the same issue occurs. I can connect to SqlServer but databases can not be expended or queried. If I unpause node 1 and restore roles to node 1 everything works again, I can connect to SqlServer and open all databases and query all tables. Node 2 never works and Node 1 will occasionally have this issues and I have to do this failover/restore process to get my database back.

Any suggestion where the issue might be?

Answer 1

How are you connecting to SQL Server? You should connect to the listener, rather than the specific node. If you connect directly to the node that is currently the secondary, you will not be able to access the databases.

Answer 2

Hi

It sounds like you have sync issues on Node 2 and may need to either create a new server for your HA and remove the failed one.

check the article below - this was written for SQL Server 2012 (which was the initial release of Always-On HA Groups), but is still very much valid:

https://www.mssqltips.com/sqlservertip/2573/monitor-sql-server-alwayson-availability-groups/

Make sure your Dashboards are showing everything as "Synchronized" and "No Data Loss".

Also, check the health of your Failover Cluster by running a Cluster Validation - this will ensure there are no network latency issues. Best practice configuration steps can be found in the links below:

https://www.mssqltips.com/sqlservertip/2519/sql-server-alwayson-availability-groups-part-1-configuration/ https://www.mssqltips.com/sqlservertip/2518/sql-server-alwayson-availability-groups-part-2-availability-groups-setup/

Hope this helps,

Thanks

Michael Durkan

• If the reply was helpful please upvote and/or accept as answer as this helps others in the community with similar questions. Thanks!

Answer 3

Hi @Samuel Smith,

Here are some potential troubleshooting steps you can try:

• Check the AG status: Use the monitoring method provided by Michael Durkan. Look for any error messages or warnings that might indicate a problem.
• Check the SQL Server logs: Look for any error messages or warnings in the SQL Server logs on both nodes. These can provide additional information about the issue. If you see an error message, please post it here.
• Check the network configuration: Make sure that the shared IP address is properly configured and accessible from both nodes. Check the network connection between the nodes to ensure that it is stable and reliable.
• Check the SQL Server configuration: Verify that the SQL Server instances on both nodes are properly configured for the AG, and that they are able to communicate with each other.
• Check for database corruption: Run DBCC CHECKDB on each database in the AG to check for any corruption issues.
• Check for resource constraints: Verify that both nodes have sufficient resources (CPU, memory, disk space, etc.) to support the AG and the databases it contains.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".