Sqlserver查詢alwayson同步情況指令碼（2）

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主節點的事務日誌傳到從節點的時間。

last_redone_time:這個時間點表示 AlwaysOn主節點的事務日誌傳到從節點後完成redo的時間（完成落盤），完成redo表示從節點已經完全同步了主節點的事務日誌。