If you manage only a handful of SQL Server Always On clusters, or if you have a strictly standardized configuration for your SQL Servers, you may know your AG configuration by heart. However, if that’s not the case—or even if it is—you might want to periodically collect a snapshot of your configuration or have it documented before a major upgrade, for your junior DBAs, new hires, and so on.
Alternatively, perhaps you’ve just started working for a new employer or client and want to get the lay of the land.
Whatever your reason, here are some DMV queries to help you gather this information.
/*
Compatibility: SQL Server 2012+ (Tested on 2016-2022)
Required Permissions: VIEW SERVER STATE
*/
-- 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 LEFT 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 not enabled on this instance' 'AlwaysOn Status' END;