Always On Availability Groups – Welcome to our SQL World!

If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server that is enabled for Always On availability groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no row is returned.

SQL Query

SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

The following query provides good overview of Always On availability groups health and status.

SQL Query

SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
       drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
          drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
          drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,
          drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,
          drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,
          drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);