Sql-server – Get DAG members

availability-groupsdistributed-availability-groupssql server

Via transact SQL with this query:

SELECT ag.[name] as 'AG Name', 
    ag.Is_Distributed, 
    ar.replica_server_name as 'Replica Name'
FROM    sys.availability_groups ag, 
    sys.availability_replicas ar       
WHERE   ag.group_id = ar.group_id;

I can find the avaibility groups (distributed or not) visible from SQL server.

If it is an AG member I can see the instance. When it is DAG I cannot see the instances…

From the primary, with a query, there is a way to get the primary instance of the secondary AG in the DAG?

Or to get the listener name of the secondary AG..

Best Answer

At least to my knowledge, you cannot get the list of servers that are participating in the Distributed Availability Group (DAG). This (set) of queries is what I use to look at my environment which uses DAG's extensively. It shows the DAG and lists the two groups that are participating it and then later shows the health information for the local availability group(s) that are on the server you are talking to. But this should be enough to get you started. But you have to connect to both listeners to see the status of all nodes.

Well, it doesn't give you the listener name directly, but if you parse the endpoint_url you can use that to connect. 

--CURRENT Server Name, DateTime, UTCDateTime
SELECT @@SERVERNAME AS CurrentServerName
    , SYSDATETIME() AS CurrentLocalTime
    , SYSUTCDATETIME() AS CurrentUTCTime

--Windows Cluster Information (Top Level, then member information)
SELECT cluster_name, quorum_type_desc, quorum_state_desc FROM sys.dm_hadr_cluster 
SELECT member_name, member_type, member_type_desc, member_state_desc FROM sys.dm_hadr_cluster_members

--Distributed AG health.  Only visible from primary node of each distributed AG.  
SELECT AG.[name] AS AG_Name
    , AG.is_distributed
    , AR.replica_server_name AS UnderlyingAG
    , ARS.role_desc
    , ARS.synchronization_health_desc
    , AR.endpoint_url
    , AR.availability_mode_desc
    , AR.failover_mode_desc
    , AR.primary_role_allow_connections
    , AllowReadOnly = CASE WHEN AR.secondary_role_allow_connections = 2 THEN 'YES' ELSE NULL END
    , AR.seeding_mode_desc
FROM sys.availability_groups AS AG
    INNER JOIN sys.availability_replicas AS AR ON ar.group_id = AG.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id
WHERE AG.is_distributed = 1
ORDER BY AG.[name]
        ,  CASE WHEN ARS.role_desc = 'PRIMARY' THEN 1
                WHEN ARS.role_desc = 'SECONDARY' AND AR.secondary_role_allow_connections = 2 THEN 2
                ELSE 3
                END
        , AR.replica_server_name

--Local, non-distributed AG information (shows all nodes)
select AG.[name] AS AG_Name 
    , AGL.dns_name AS AG_ListenerName
    , AGL.port AS AG_ListenerPort
    , ARCS.replica_server_name AS ServerName
    , ARS.role_desc
    , ARS.connected_state_desc
    , AR.availability_mode_desc
    , AR.endpoint_url
    , AR.failover_mode_desc
    , AllowReadOnly = CASE WHEN AR.secondary_role_allow_connections = 2 THEN 'YES' ELSE NULL END
from sys.availability_groups AS AG
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AG.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.group_id = AG.group_id AND ARS.replica_id = ARCS.replica_id
    LEFT OUTER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = AG.group_id
    INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id AND AR.replica_id = ARCS.replica_id
WHERE AG.is_distributed = 0
ORDER BY AG.[name]
    , CASE  WHEN ARS.role_desc = 'PRIMARY' THEN 1
            WHEN ARS.role_desc = 'SECONDARY' AND AR.secondary_role_allow_connections = 2 THEN 2
            ELSE 3
            END
    , ARCS.replica_server_name

--Local AG Health by Database.  
SELECT AG.[name] AS AG_Name 
    , AG.is_distributed
    , ARCS.replica_server_name AS ServerName
    , ARS.role_desc AS ServerRole
    , DB_IsPrimary = NULLIF(DRS.is_primary_replica, 0)
    , Server_AllowReadOnly = CASE WHEN AR.secondary_role_allow_connections = 2 THEN 'YES' ELSE NULL END
    , D.[name] AS DatabaseName
    , DatabaseState_Local = CASE WHEN DRS.is_local = 1 THEN D.state_desc ELSE NULL END
    , DRS.synchronization_state_desc AS SyncState
    , DRS.synchronization_health_desc AS HealthState
    , is_suspended = NULLIF(DRS.is_suspended, 0)
    , DRS.suspend_reason_desc
    , DRS.last_commit_time 
    , EstimatedDataLoss = CONVERT(VARCHAR(20), DATEADD(MILLISECOND, ISNULL(DATEDIFF(SECOND,DRS.last_commit_time,P_DRS.last_commit_time),0) * 1000, 0), 114)
FROM sys.availability_groups AS AG
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AG.group_id
    LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS DRS ON DRS.replica_id = ARCS.replica_id AND DRS.group_id = ARCS.group_id
    INNER JOIN sys.databases AS D ON D.database_id = DRS.database_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.group_id = AG.group_id AND ARS.replica_id = ARCS.replica_id
    LEFT OUTER JOIN (SELECT database_id, group_id, replica_id, last_commit_time FROM sys.dm_hadr_database_replica_states WHERE is_primary_replica = 1) AS P_DRS ON P_DRS.group_id = AG.group_id AND P_DRS.database_id = DRS.database_id
    INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id AND AR.replica_id = ARCS.replica_id
WHERE AG.is_distributed = 0
ORDER BY AG.[name]
    , CASE  WHEN ARS.role_desc = 'PRIMARY' THEN 1
            WHEN ARS.role_desc = 'SECONDARY' AND AR.secondary_role_allow_connections = 2 THEN 2
            ELSE 3
            END
    , ARCS.replica_server_name
    , D.[name]
Related Question