If you were like me, you would know your AG configuration by heart. However, what if you get busy with increasing responsibilities in other areas, projects etc.? Therefore, you do not just want to rely on your memory. On the other hand, maybe you want to simply collect snapshot of your configuration every now and then or have it documented before a major upgrades, for your junior DBAs, new hire etc...
Alternatively,
maybe you just started working for a new employer or a new client.
Whatever is your reason, here is a DMV query to gather the information.
SELECT ag.name
ag_name ,
ar.replica_server_name
replica_server ,
CASE WHEN hdrs.is_primary_replica
= 0
AND
hdrs.replica_id =
ar.replica_id THEN
0
ELSE
1
END
is_primary ,
adc.database_name
,
agl.dns_name
,
agl.port
,
*
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar ON
ag.group_id =
ar.group_id
INNER JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id
INNER JOIN sys.availability_group_listeners agl ON agl.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_database_replica_states
hdrs ON hdrs.group_id
= adc.group_id
AND hdrs.group_database_id
= adc.group_database_id
AND hdrs.replica_id
= ar.replica_id
ORDER BY ag.name , adc.database_name , is_primary DESC;