Browsing around some sys tables, I see:

sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name


sys.dm_hadr_availability_replica_cluster_nodes.replica_server_name = sys.availability_replicas.replica_server_name


sys.dm_hadr_availability_replica_cluster_nodes.node_name = sys.availability_replicas.replica_server_name


sys.dm_hadr_database_replica_states.group_id = sys.availability_groups.group_id

Could someone please clarify for me whether the "hadr" tables are duplicating data or not? For example, sys.dm_hadr_availability_replica_nodes and sys.avilability_replicas sound like they're describing the same thing.

I guess the question is on how safe it is to join these tables given I can see data values equate - because after all, the same value (something like 'SQL01') could conceivably be used to describe different things.

Better yet - is there no entity-relationship diagram published by Microsoft for these sys tables? I can find old posts where people ask the same question about ERDs and there are answers regarding older versions of SQL Server, but all links to diagrams seem outdated and defunct.

  • 1
    I guess broadly I'm wondering whether the HADR tables were implemented as a set, and if so, what's the history there (what were they implemented for that wasn't already in place with the other tables)? And if so, did they extend the existing tables or duplicate some functionality? Aug 7, 2019 at 22:36

1 Answer 1


It's not duplicating of the information, depending on the requirement we could utilize anyone them:

  1. DMV: The prefix sys.dm_ represents DMV the under lying information from Dynamic Management views
  2. Catalog View: No prefix sys. represents catalog views. For more details..

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.