Gather Always On Configuration using a DMV queries

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 are some DMV queries to gather the information.

---

-- SQL Server Info
SELECT
    'Server Info' AS [Description],
    SERVERPROPERTY('ServerName')                   [ServerName],
    SERVERPROPERTY('InstanceName')                 [InstanceName],
    @@VERSION                                      [VersionString],
    SERVERPROPERTY('ProductVersion')               [ProductVersion],
    SERVERPROPERTY('Edition')                      [Edition],
    SERVERPROPERTY('IsHadrEnabled')                [AlwaysOn_Enabled];

IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN	
	-- Overview of the AGs
	SELECT 
	    'Overview' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica Server],
	    ar.availability_mode_desc AS [Availability Mode],
	    ar.failover_mode_desc AS [Failover Mode],
	    ars.role_desc AS [Current Role],
	    ars.operational_state_desc AS [Operational State],
	    ars.connected_state_desc AS [Connected State]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

	-- AG listeners
	SELECT 
	    'AG Listeners' AS [Description],
	    ag.name AS [AG Name],
	    agl.dns_name AS [Listener DNS Name],
	    agl.port AS [Listener Port],
	    agl.ip_configuration_string_from_cluster AS [IP Config]
	FROM sys.availability_groups ag
	JOIN sys.availability_group_listeners agl ON ag.group_id = agl.group_id;

	-- AG Health
	SELECT 
	    'AG Health' AS [Description],
	    ag.name AS [AG Name],
	    ags.primary_replica AS [Primary Replica],
	    ags.primary_recovery_health_desc AS [Primary Health],
	    ags.secondary_recovery_health_desc AS [Secondary Health],
	    ags.synchronization_health_desc AS [Sync Health]
	FROM sys.availability_groups ag
	JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id;

	-- AG replicas and roles
	SELECT 
	    'Replica Roles' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica Server],
	    ars.role_desc AS [Current Role],
	    ars.synchronization_health_desc AS [Sync Health],
	    ar.availability_mode_desc AS [Availability Mode],
	    ar.failover_mode_desc AS [Failover Mode]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

	-- Replication status
	SELECT 
	    'Replication Status' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica],
	    ar.availability_mode_desc AS [AG Mode],
	    db.name AS [Database],
	    drs.synchronization_state_desc AS [Sync State],
	    drs.log_send_queue_size AS [Log Send Queue KB],
	    drs.log_send_rate AS [Log Send Rate KB/sec],
	    drs.redo_queue_size AS [Redo Queue KB],
	    drs.redo_rate AS [Redo Rate KB/sec]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
	JOIN sys.databases db ON drs.database_id = db.database_id;

END;
ELSE
BEGIN
    SELECT 'AlwaysOn is disabled' 'AlwaysOn Status' 
END;