SQL Availability group primary not visible from secondary via sys.dm_hadr_database_replica_states

Question

Trying to create a single pane of glass for all our servers as to the Availability Group State and Health. I want to do this by writing an app and calling t-sql statements to gather data from each of the server master tables/views and then displaying as a combined dashboard.

I am using the sys views but am running into the following issue:

Running select * from sys.dm_hadr_database_replica_states from the Primary replica displays the states for both Primary and Secondary replicas for each database, but running it from the Secondary replica only displays results for the Secondary.

For example, when run from the Primary, I get:

TAPSLog     ONLINE  FULL    SRVR-01-HA  TAPSLog_LN      Secondary   SYNCHRONIZED    HEALTHY NULL    NULL
TAPSLog     ONLINE  FULL    SRVR-01     TAPSLog_LN      Primary     SYNCHRONIZED    HEALTHY ONLINE  NULL

And from the Secondary:

TAPSLog     ONLINE  FULL    SRVR-01-HA  TAPSLog_LN      Secondary   SYNCHRONIZED    HEALTHY NULL    NULL

Does anyone know if its possible to read the status of all replicas from all replicas and I am just using the wrong tables?

Answer 1

but running it from the Secondary replica only displays results for the Secondary.

Does anyone know if its possible to read the status of all replicas from all replicas [...]

This is expected and how it works. This is due to the protocol that availability groups use, the primary knows of everyone and their current status and the secondary only knows of itself.

The status messages and essentially only send from the secondary to the primary, additionally the secondary only connects to the primary and the secondary is responsible for this, it's different than what most people are used to and typically the implementation details aren't a big deal.

If you want a single pane of glass, you'll need to do an inventory of the replicas and AGs first. Note that this is exacerbated by distributed availability groups and, to some extent, read-scale availability groups. This means you'll need to use multiple catalog and DMVs, such as the ones with cluster in their names and to check each individual replica, which can be difficult based on the type of AG.