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];
Nice overview, i’m going to use this as the basis for a report!
Thanks!
Theo.
LikeLike
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])
LikeLike