SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Gather Always On Configuration using a DMV query

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
                  ANDhdrs.replica_id =ar.replica_id THEN0
        ENDis_primary ,
        adc.database_name ,
        agl.dns_name ,
        agl.port ,
FROM    sys.availability_groups ag
        INNER JOIN sys.availability_replicas ar ONag.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;

Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.


Leave a comment on the original post [sqlpal.blogspot.com, opens in a new window]

Loading comments...