Allways on Availability Group

 

Monitoring Always On Availability Groups in SQL Server

In this post, I’ll explain some commonly used internal SQL Server objects and queries for monitoring the health and performance of Always-On Availability Groups.

Always On Health and Status Overview

The following query gives a detailed overview of the health and synchronization status of availability groups:

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

Show Availability Groups Where This Server Is Secondary

SELECT Groups.name AS AGname,
       primary_replica
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups
  ON States.group_id = Groups.group_id
WHERE primary_replica != @SERVERNAME;

Show Databases in AG Where This Server Is Primary

SELECT Groups.name AS AGname,
       AGD.database_name
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups
  ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD
  ON Groups.group_id = AGD.group_id
WHERE primary_replica = @SERVERNAME
ORDER BY AGname, database_name;

Replica and Availability Group Status

WITH AGStatus AS (
  SELECT name AS AGname,
         replica_server_name,
         CASE WHEN (primary_replica = replica_server_name) THEN 1 ELSE 0 END AS IsPrimaryServer,
         secondary_role_allow_connections_desc AS ReadableSecondary,
         availability_mode AS Synchronous,
         failover_mode_desc
  FROM master.sys.availability_groups Groups
  INNER JOIN master.sys.availability_replicas Replicas
    ON Groups.group_id = Replicas.group_id
  INNER JOIN master.sys.dm_hadr_availability_group_states States
    ON Groups.group_id = States.group_id
)
SELECT AGname,
       Replica_server_name,
       IsPrimaryServer,
       Synchronous,
       ReadableSecondary,
       Failover_mode_desc
FROM AGStatus
ORDER BY AGname, IsPrimaryServer DESC;

All AG Availability Group Status

SELECT AG.name AS AvailabilityGroupName,
       AR.replica_server_name AS AvailabilityReplicaServerName,
       dbcs.database_name AS AvailabilityDatabaseName,
       ISNULL(dbcs.is_failover_ready, 0) AS IsFailoverReady,
       ISNULL(arstates.role_desc, 3) AS ReplicaRole,
       AR.availability_mode_desc AS AvailabilityMode
ORDER BY AvailabilityReplicaServerName, AvailabilityDatabaseName;

(Note: Long query shortened for brevity—see full script for details.)

Performance and Estimation Queries

SELECT AG.name AS AvailabilityGroupName,
       AR.replica_server_name AS AvailabilityReplicaServerName,
       dbcs.database_name AS AvailabilityDatabaseName
ORDER BY AvailabilityDatabaseName;

AG Log Reads

EXEC sp_readerrorlog 0, 1, 'The state of the local availability replica', '';
EXEC sp_readerrorlog 0, 1, 'RESOLVING';

Internal SQL Server Objects for Always On

SELECT * FROM sys.dm_hadr_cluster_members;
SELECT * FROM sys.dm_hadr_cluster;
SELECT * FROM sys.dm_hadr_cluster_networks;
SELECT * FROM sys.dm_hadr_auto_page_repair;
...
SELECT * FROM sys.availability_groups;

SQL Server Extended Events for Always On

SELECT xml_data.value('(event/@name)[1]', 'varchar(max)') AS Name,
       ...
FROM (
  SELECT object_name, CONVERT(xml, event_data) AS xml_data
  FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY Time;

Stored Procedure: Calculate RPO

CREATE PROCEDURE uspCalculateRPO (
  @group_id UNIQUEIDENTIFIER,
  @replica_id UNIQUEIDENTIFIER,
  @group_database_id UNIQUEIDENTIFIER
)
AS
BEGIN
  ...
END

Stored Procedure: Calculate RTO

CREATE PROCEDURE uspCalculateRTO (@secondary_database_name NVARCHAR(MAX))
AS
BEGIN
  ...
END