SQL Server – Why Are Two HADR DMVs Reporting Different States?

availability-groupsdmvsql-server-2012

SQL Server 2012 (11.0.5058.0) Enterprise Edition

We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are configured for HA and DR, 1 is configured for HA only, and 1 is configured for DR only.

Each of the 6 HA/DR Availability Groups have "SQLB" as a primary and "SQLA" as a secondary (synchronous) HA replica and "SQLC" as a secondary (async) replica.

On both secondaries:

SELECT dhags.group_id, dhags.synchronization_health_desc
FROM sys.dm_hadr_availability_group_states dhags

reports that all Availability Group replication sync health are NOT_HEALTHY and

select replica_id,synchronization_health_desc
from sys.dm_hadr_availability_replica_states

reports that all replicas have a sync health of HEALTHY.

The primary replica reports all Availability Groups and replicas with a sync health of HEALTHY.

While I understand that one reports on replica sync health and the other reports on AG sync health, it seems logical to me that if the more granular (AG) state was not healthy, that would affect the overall health of the broader context (replica). I cannot find MSDN documentation that describes how the health is determined at each level.

Why would the secondaries report NOT_HEALTHY for Availability Group sync health, but HEALTHY for replica sync health, and why does this differ from the primary's report?

Best Answer

Sadly, sys.dm_hadr_availability_replica states is not a reliable indicator of replica health. Here's the Connect item on one of the bugs we've run into where that DMV stops refreshing - note in the comments that log_send_queue_size in the DMV sys.dm_hadr_database_replica_states shows 0 even when there's log data to be sent.

Note that the Connect item is marked as Won't Fix. Sad trombone.

Endpoint configuration

One of the most common causes are misconfigured endpoints for the Database Mirroring role. There are various options and configurations that can be used and sometimes, especially when created by hand, those configuration options do not match. Take the OPs example:

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)

This creates an endpoint called dbm_endpoint on tcp port 5022 for database mirroring. However it leaves out quite a bit of configuration options to which we assume are defaulted but we really do not know. This means the default options such as Windows authentication using either Kerberos or NTLM, it also doesn't specify which IP address (v4, v6, interface) is used, nor does it specify encryption options to which the default is REQUIRED and depending on the version of SQL Serve either RC4 or AES.

That is quite the assumption to make, especially when it isn't working. If you're not sure what the endpoint is set to, join sys.tcp_endpoints with sys.database_mirroring_endpoints to turn the resulting single row to individual configuration options.

There are advanced configurations, for example, that use certificates since they can't use Windows authentication. We don't know if the OP has a proper domain and if these servers are joined. Again, the assumption based off the endpoints is that they are, however we don't know for sure. When using certificates the largest issue is that the same certificate is used on multiple replicas yet the private key is not exported when copying the certificate to import to the replicas. Since the endpoints use the private/public key pair, using the same certificate will require that endpoint at some time to use the private key. Make sure the certificate is exported and imported with both keys if you're using it on multiple replicas.

When specifying the IP Address, the default is ANY which could be any of the IPv4 or IPv6 addresses. Though most places don't register or use IPv6 addresses, I do see it as an issue every once and a while. Checking to see what addresses are registered to a host in your DNS can help narrow the issue. You can also setup a network capture and filter based on the port. Every once and a while an IPv6 address is used and it's improperly setup, disabled, or firewalled on other servers but is returned via DNS.

Availability Group Replica Specification

When creating the availability group each replica will need to be specified and as such will need an endpoint url which will define the communications port (database mirroring endpoint).

Taking the OPs example:

REPLICA ON
        'SQL1' WITH
            (
                ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            ),

This replica, called SQL1 has a TCP endpoint on sql1.sql.sandbox.net port 7022. the address seems to be a FQDN and should be checked that the proper DNS lookups, (if using) Kerberos SPNs, and firewall rules exist or are created. If there are multiple interfaces or addresses that are returned on a lookup of the address, then it may make sense to narrow it down to a specific address in the endpoint url for testing or to narrow it down in the mirroring endpoint specification.

SQL Server Permissions

One of the last surface areas for the disconnected issues to appear is the endpoint permissions on each replica. Since each endpoint the account will need the CONNECT permission (similar if using certificates) in order to use the endpoints. This is a fairly common issue, so common that this is marked in the errorlog and gives you the fix in the error message which is to grant the connect permission to the endpoint.

Windows Clustering

Since the replica manager replies upon Windows Server Failover Clustering to be working (not counting Linux, Docker, or Read-Scale) in order to start up, do make sure the cluster service is running on each replica and each node shows an up status in the cluster (Powershell, Failover Cluster Manager, etc.). If the cluster service is not running, then the manager will stop until it comes back up - this will be logged in the errorlog and should be very evident.

Troubleshooting Methodology

When nothing else works or shows up, what do you do?

Availability groups are a distributed system, this means there are many individual parts (replicas) that are distributed among many areas (databases) that function through a common set of protocols (availability groups). Given this, the troubleshooting methodology differs from typical single instance or clustered instance troubleshooting.

Each part needs to be checked against itself and against the overall metadata available. Additionally, each part needs to have the intermediary items checked against each other intermediary (such as firewalls, network cards, load balancers, etc.).

Start by checking the errorlog on each replica as only some errors (due to where the error occurs) are only applicable when the replica is primary or secondary. After an overview of each errorlog, this should point in a direction - if it does not, then most likely the issue is under SQL Server which could mean networking, firewall, OS, etc. System and application logs can be helpful here as a next step.

Look out for NDIS drivers installed on the system as they could be blocking traffic or incorrectly working and dropping traffic. Additionally, do check the interface diagnostics for the network cards as things such as dropped/discarded packets may be an issue if the disconnected state is intermittent.

When all else fails, check every piece of metadata against the other and make sure all match and are the same. In the OPs metadata specified above, one port was on 7022 and the other on 5022. While this in of itself is not incorrect and can be used, it does make issues in other places such as firewall rules where someone can easily misread the port or assume and not read that the ports are the same.

Depending upon skill level and knowledge, a network trace from the primary side and the secondary side would be ideal. This can show the packets leaving the primary/secondary and hopefully arriving at the opposite side. If it does not, an intermediary is not configured correctly - look for firewalls, load balancers, routers, etc., that are stopping the traffic.

SQL Server – Does Synchronous-Commit Availability Mode Ensure Consistency Between Replicas?

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).

can I expect consistent results to be returned from both replicas, every time?

Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different meaning from the distributed (CAP) consistency. You are not guaranteed to read the most recent consistent state. Particularly, you are not guaranteed to read your own committed writes. And the reads from each replica, while each being consistent, may not match.