8

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?

Improve this question
2
  • Can you confirm that you are seeing NOT_HEALTHY on the secondary ASYNC replica only ?
    – Kin Shah
    Commented Jul 20, 2015 at 19:04
  • i'm seeing NOT_HEALTHY on both the SYNC and ASYNC replicas.
    – swasheck
    Commented Jul 20, 2015 at 19:26

1 Answer 1

Reset to default
5

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.

Improve this answer
3
  • The link reaches a page saying MS Connect has retired. This Q&A was 2015. It's four years later. I'm here because I'm seeing the same thing as the OP. I guess they meant that "won't fix". That's a bit lame. On the flip side - anyone know of a good script for reporting AG health and config, from group to replica to database sync level?
    – youcantryreachingme
    Commented Aug 8, 2019 at 4:58
  • PS. For me, sys.dm_hadr_availability_group_states.synchronization_health_desc (which I understand is reporting on the health of the whole group) reports NOT_HEALTHY on secondaries, but HEALTHY on the primary (3 replicas). Docs describe the col as "a rollup of the synchronization_health of all availability replicas in the availability group". This shows disparity within the system without even looking at sys.dm_hadr_availability_replica_states (which is, presumably, from where the data are being rolled up).
    – youcantryreachingme
    Commented Aug 8, 2019 at 5:02
  • @youcantryreachingme that's a different question - your best bet there would be starting a new question.
    – Brent Ozar
    Commented Aug 8, 2019 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.