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];

One thought on “Monitor availability groups and availability replicas status information using T-SQL

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