SQL Server Availability Group Monitoring

Monitoring the synchronization status of SQL Availability Groups replicated databases, on a replica, is very straightforward.

Synchronization state & health Link to heading

SELECT
    DB_NAME(database_id) as [name],
    [synchronization_state_desc],
    [synchronization_health_desc],
    [database_state_desc]
FROM
    sys.dm_hadr_database_replica_states;

• synchronization_state: Data-movement state
• synchronization_health: Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica and the availability mode of the availability replica
• database_state: database state

Synchronization date / times Link to heading

SELECT
    DB_NAME(database_id) as [name],
    [last_sent_time],
    [last_received_time],
    [last_commit_time]
FROM
    sys.dm_hadr_database_replica_states;

• last_sent_time: Time when the last log block was sent.
• last_received_time: Time when the log block ID in last message received was read on the secondary replica.
• last_commit_time: Time corresponding to the last commit record.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql

What worked for me? Link to heading

SELECT
    DB_NAME(database_id) as [name],
    synchronization_health_desc,
    DATEDIFF(MILLISECOND,last_received_time,GETDATE()) as [last_received_ms],
    [last_commit_time]
FROM
    sys.dm_hadr_database_replica_states;

A combination of the above queries:

• Database name
• Health of the entire availability group
• How many milliseconds has passed from the last message received on the replica
• Last commit time on the primary database

The number of seconds that the secondary replica is behind the primary replica during synchronization.

It’s a great measurement, but the value is NULL on a repilca.