Health check timeout
Issue: The value for the Health Check Timeout setting for the cluster is at or below the default value of 30000 milliseconds.
Problem: In our experience, we have found that the default value of 30000 can be too low, often resulting in unexpected cluster issues or failovers.
Learn More...Secondary lag
Issue: One or more replicas is experiencing a secondary lag of over 5 seconds.
Problem: A lag of over 5 seconds between the primary and secondary replicas indicates there is some sort of issue affecting performance of transactions in the availability group.
Learn More...HADR_SYNC_COMMIT waits
Issue: The average HADR_SYNC_COMMIT wait is over 5 milliseconds.
Problem: While 5 milliseconds may not seem like much, latency over this amount can negatively affect performance of OLTP workloads.
Learn More...Replica not failover ready
Issue: One or more replicas in your availability group is not synchronized and ready for failover.
Problem: A failover to a replica that is not synchronized would likely result in data loss.
Learn More...Endpoint using expiring certificate
Issue: The endpoint used by the availability group is using a certificate with an expiration date.
Problem: When the certificate expires, the endpoint could stop working. When the endpoint stops working, the availability group cannot allow communication with other replicas.
Learn More...Listener offline
Issue: The listener for an availability group appears to be offline on all nodes.
Problem: The listener can only be online where the Primary replica of an availability group is, but if the listener is offline everywhere than it cannot be resolved to allow database connections. Any connection attempt using the listener while it is offline will fail, so databases in the availability group will appear offline to the end user or application.
Learn More...Availability group backup preference
Issue: The backup preference for your availability group is not set to Primary.
Problem: If the backup preference is not set to the Primary, your backups will not be current if one or more replicas becomes available.
Learn More...Availability group session timeout
Issue: The session timeout value for one or more replicas in your availability group is less than or equal to the default value of 10 seconds.
Problem: In our experience, we have found that the default value of 10 seconds is low enough that brief network events that last over 10 seconds can cause unexpected events like failovers.
Learn More...Availability group data movement
Issue: Data movement is suspended for one or more databases in an availability group.
Problem: At least one secondary replica is not being updated, which has concerns if you have it set for Automatic failover. Additionally, the transaction log on the Primary database will not be cleared for reuse until this issue is resolved.
Learn More...Replica not joined
Issue: One or more databases are currently not joined to the availability group.
Problem: Secondary replicas that are not joined cannot be used for in a failover, since are not being updated with any recent transactions.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: