Monitoring the synchronization status of SQL Availability Groups replicated databases, on a replica, is very straightforward.
Synchronization state & health
SELECT
DB_NAME(database_id) as [name],
[synchronization_state_desc],
[synchronization_health_desc],
[database_state_desc]
FROM
sys.dm_hadr_database_replica_states;
name | synchronization_state_desc | synchronization_health_desc | database_state_desc |
---|---|---|---|
DB1 | SYNCHRONIZED | HEALTHY | ONLINE |
DB2 | SYNCHRONIZING | PARTIALLY_HEALTHY | RECOVERY |
DB3 | NOT SYNCHRONIZING | NOT_HEALTHY | EMERGENCY |
- 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
SELECT
DB_NAME(database_id) as [name],
[last_sent_time],
[last_received_time],
[last_commit_time]
FROM
sys.dm_hadr_database_replica_states;
name | last_sent_time | last_received_time | last_commit_time |
---|---|---|---|
DB1 | 2022-06-15 11:47:34.317 | 2022-06-15 11:47:34.317 | 2022-06-15 11:05:26.687 |
DB2 | 2022-06-15 11:47:34.180 | 2022-06-15 11:47:34.180 | 2022-06-15 11:05:28.533 |
DB3 | 2022-06-15 11:47:35.020 | 2022-06-15 11:47:35.020 | 2022-06-15 11:47:34.827 |
- 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.
What worked for me?
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
Question
What about secondary_lag_seconds?
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.