Search This Blog

Thursday, April 26, 2018

Gather Always On Configuration using a DMV queries

Gather Always On Configuration using a DMV queries
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;