Monitor availability groups and availability replicas status information using T-SQL

Just a quick blog post to share a query, which I wrote to monitor availability groups and replicas and the associated databases:

WITH [AvailabilityGroupReplicaCTE]
AS (
	SELECT dc.[database_name]
		,dr.[synchronization_state_desc]
		,dr.[suspend_reason_desc]
		,dr.[synchronization_health_desc]
		,dr.[replica_id]
		,ar.[availability_mode_desc]
		,ar.[primary_role_allow_connections_desc]
		,ar.[secondary_role_allow_connections_desc]
		,ar.[failover_mode_desc]
		,ar.[endpoint_url]
		,ar.[owner_sid]
		,ar.[create_date]
		,ar.[modify_date]
		,dr.[recovery_lsn]
		,dr.[truncation_lsn]
		,dr.[last_sent_lsn]
		,dr.[last_sent_time]
		,dr.[last_received_lsn]
		,dr.[last_received_time]
		,dr.[last_hardened_lsn]
		,dr.[last_hardened_time]
		,dr.[last_redone_lsn]
		,dr.[last_redone_time]
		,dr.[redo_queue_size]
		,dr.[log_send_queue_size]
	FROM [sys].[dm_hadr_database_replica_states] dr
	INNER JOIN [sys].[availability_databases_cluster] dc
		ON dr.[group_database_id] = dc.[group_database_id]
	INNER JOIN [sys].[availability_replicas] ar
		ON ar.[replica_id] = dr.[replica_id]
	WHERE dr.[is_local] = 1
	)
	,[AvailabilityGroupReplicaDatabaseState] (
	[ReplicaID]
	,[ReplicaDBName]
	,[ReplicaServerName]
	,[JoinState]
	,[Role]
	,[AvailabilityMode]
	,[SynchronizationState]
	,[SynchronizationHealth]
	,[OperationalState]
	,[ConnectedState]
	,[SuspendReason]
	,[RecoveryHealth]
	,[RecoveryLSN]
	,[TruncationLSN]
	,[LastSentLSN]
	,[LastSentTime]
	,[LastReceivedLSN]
	,[LastReceivedTime]
	,[LastHardenedLSN]
	,[LastHardenedTime]
	,[LastRedoneLSN]
	,[LastRedoneTime]
	,[RedoQueueSize]
	,[LogSendQueueSize]
	,[PrimaryRoleAllowConnections]
	,[SecondaryRoleAllowConnections]
	,[FailoverMode]
	,[EndPointURL]
	,[Owner]
	,[CreateDate]
	,[ModifyDate]
	)
AS (
	SELECT c.[replica_id]
		,c.[database_name]
		,cs.[replica_server_name]
		,cs.[join_state_desc]
		,rs.[role_desc]
		,c.[availability_mode_desc]
		,c.[synchronization_state_desc]
		,c.[synchronization_health_desc]
		,rs.[operational_state_desc]
		,rs.[connected_state_desc]
		,c.[suspend_reason_desc]
		,rs.[recovery_health_desc]
		,c.[recovery_lsn]
		,c.[truncation_lsn]
		,c.[last_sent_lsn]
		,c.[last_sent_time]
		,c.[last_received_lsn]
		,c.[last_received_time]
		,c.[last_hardened_lsn]
		,c.[last_hardened_time]
		,c.[last_redone_lsn]
		,c.[last_redone_time]
		,c.[redo_queue_size]
		,c.[log_send_queue_size]
		,c.[primary_role_allow_connections_desc]
		,c.[secondary_role_allow_connections_desc]
		,c.[failover_mode_desc]
		,c.[endpoint_url]
		,sl.[name]
		,c.[create_date]
		,c.[modify_date]
	FROM [AvailabilityGroupReplicaCTE] c
	INNER JOIN [sys].[dm_hadr_availability_replica_states] rs
		ON rs.[replica_id] = c.[replica_id]
	INNER JOIN [sys].[dm_hadr_availability_replica_cluster_states] cs
		ON cs.[replica_id] = c.[replica_id]
	INNER JOIN [sys].[syslogins] sl
		ON c.[owner_sid] = sl.[sid]
	)
SELECT *
FROM [AvailabilityGroupReplicaDatabaseState];
Advertisements

2 thoughts on “Monitor availability groups and availability replicas status information using T-SQL

  1. Thanks for sharing. I noticed I was only getting results on half of the servers with Always On. It was because the owner for the missing half was Public 0x02.

    There was two ways too get the missing rows. Make the syslogin join a “left outer join” or remove the join and use suser_sname(c.[owner_sid])

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s