What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?
2
votes
1
answer
269
views
Browsing around some sys tables, I see:
sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name
and
sys.dm_hadr_availability_replica_cluster_nodes.replica_server_name = sys.availability_replicas.replica_server_name
and
sys.dm_hadr_availability_replica_cluster_nodes.node_name = sys.availability_replicas.replica_server_name
and
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.
Asked by youcantryreachingme
(1655 rep)
Aug 7, 2019, 05:28 AM
Last activity: May 17, 2025, 11:05 AM
Last activity: May 17, 2025, 11:05 AM