SQL Server Always On Availability Groups Synchronization Lag
DECLARE @LagSecsThreshold INT = 1;
WITH AG_DATA_PREP
AS (SELECT AR.replica_server_name,
HARS.role_desc,
db_name(DRS.database_id) [DBName],
AR.availability_mode_desc,
DRS.synchronization_health_desc,
DRS.synchronization_state_desc,
DRS.last_commit_time,
last_sent_time,
last_received_time,
last_hardened_time,
last_redone_time,
log_send_queue_size,
log_send_rate,
redo_queue_size,
redo_rate
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS
ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
),
PRIMARY_COMMIT_TIME
AS (SELECT replica_server_name,
role_desc,
[DBName],
availability_mode_desc,
synchronization_health_desc,
synchronization_state_desc,
last_commit_time,
last_sent_time,
last_received_time,
last_hardened_time,
last_redone_time,
log_send_queue_size,
log_send_rate,
redo_queue_size,
redo_rate
FROM AG_DATA_PREP
WHERE role_desc = 'PRIMARY'
),
SEC_COMMIT_TIME
AS (SELECT replica_server_name,
role_desc,
[DBName],
availability_mode_desc,
synchronization_health_desc,
synchronization_state_desc,
last_commit_time,
last_sent_time,
last_received_time,
last_hardened_time,
last_redone_time,
log_send_queue_size,
log_send_rate,
redo_queue_size,
redo_rate
FROM AG_DATA_PREP
WHERE role_desc = 'SECONDARY'
),
AG_DATA_FINAL
as (SELECT p.replica_server_name [Primary_Replica],
p.[DBName] AS [DatabaseName],
s.replica_server_name [Secondary_Replica],
abs(ISNULL(DATEDIFF(ss, s.last_commit_time, p.last_commit_time), 0)) AS Sync_Lag_Secs,
p.synchronization_health_desc as Primary_Synchronization_Health_Desc,
p.synchronization_state_desc as Primary_Synchronization_State_Desc,
p.last_commit_time as Primary_Last_Commit_Time,
p.last_sent_time as Primary_Last_Sent_Time,
p.log_send_queue_size as Primary_Log_Send_Queue_Size,
p.log_send_rate as Primary_Log_Send_Rate,
s.synchronization_health_desc as Secondary_Synchronization_Health_Desc,
s.synchronization_state_desc as Secondary_Synchronization_State_Desc,
s.last_commit_time as Secondary_Last_Commit_Time,
s.last_sent_time as Secondary_Last_Sent_time,
s.last_received_time as Secondary_Last_Received_Time,
s.last_hardened_time as Secondary_Last_Hardened_Time,
s.last_redone_time as Secondary_Last_redone_Time,
s.log_send_queue_size as Secondary_Log_Send_Queue_Size,
s.log_send_rate as Secondary_Log_Send_Rate,
s.redo_queue_size as Secondary_Redo_Queue_Size,
s.redo_rate as Secondary_Redo_Rate
FROM PRIMARY_COMMIT_TIME p
LEFT JOIN SEC_COMMIT_TIME s
ON [s].[DBName] = [p].[DBName]
WHERE abs(ISNULL(DATEDIFF(ss, s.last_commit_time, p.last_commit_time), 0)) >= @LagSecsThreshold
)
Select * from AG_DATA_FINAL
|
|
|
|
|
|