SQL Server AlwaysOn查看数据同步进度

SQL Server AlwaysOn数据同步失败，修复完成后，检查数据追赶同步进度情况

SELECT 
ag.name AS ag_name, ar.replica_server_name, db_name(dr_state.database_id), is_ag_replica_local = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE'
END 
, ag_replica_role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc
END
, dr_state.last_redone_time
, dr_state.last_hardened_lsn
, dr_state.last_hardened_time
, datediff(ms,last_hardened_time, getdate()) as 'seconds behind primary'
FROM (
( sys.availability_groups AS ag 
JOIN sys.availability_replicas AS ar 
ON ag.group_id = ar.group_id 
)
JOIN 
sys.dm_hadr_availability_replica_states AS ar_state 
ON ar.replica_id = ar_state.replica_id
)
JOIN 
sys.dm_hadr_database_replica_states dr_state 
on 
ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id
order by last_hardened_lsn desc,last_hardened_time

last_hardened_time:这个时间点表示AlwaysOn主节点的事务日志传到从节点的时间，即redo log传输延迟。
last_redone_time:这个时间点表示 AlwaysOn主节点的事务日志传到从节点后完成redo的时间（完成落盘），完成redo表示从节点已经完全同步了主节点的事务日志，即redo log应用延迟。