AnswerBun.com

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Database Administrators Asked on January 6, 2022

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.

One Answer

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..

Answered by Shekar Kola on January 6, 2022

Add your own answers!

Related Questions

Read after write in Galera

3  Asked on December 4, 2021

   

Generate SQL Table DDL from a View

2  Asked on December 4, 2021 by user162241

       

MySQL taking up 400% CPU

2  Asked on December 4, 2021 by littlelebowski

         

No output when use pymongo watch()

1  Asked on December 2, 2021 by red-black

   

changing the connection string in SQL Server

1  Asked on November 30, 2021 by teja

   

SSIS Excel Import – First row getting skipped

1  Asked on November 30, 2021 by mithun-john-jacob

     

Get if field has max value and display value

2  Asked on November 30, 2021 by carlos-alberto-martinez-martin

   

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP