Introduction
I was asked to extend our current AlwaysOn
availability group to a new DR site, by adding a secondary asynchronous replica.
Once that was accomplished, I was asked to report on the latency of this
replica to gauge potential data loss.
To achieve that, I collected the last_commit_time per database and replica and then calculated
the time difference between primary and the secondaries.
Code
Here is the
code, I used to retrieve those numbers:
USE master
GO
;WITH
stat AS
(
SELECT AR.replica_server_name as [RSName]
,AG.name as [AGName]
,Db_name(DRS.database_id) as [DBName]
,DRS.last_commit_time as [LCTime]
,HARS.role_desc
FROM sys.dm_hadr_database_replica_states DRS
JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
JOIN sys.dm_hadr_availability_replica_states HARS
ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
JOIN sys.availability_groups AG
ON AG.group_id = AR.group_id
),
p AS
(
SELECT [RSName]
,[AGName]
,[DBName]
,[LCTime]
FROM stat
WHERE role_desc = 'PRIMARY'
),
s AS
(
SELECT [RSName]
,[AGName]
,[DBName]
,[LCTime]
FROM stat
WHERE role_desc = 'SECONDARY'
)
SELECT p.[RSName] as [PRIMARY]
,p.[AGName]
,p.[DBName]
,s.[RSName] as [SECONDARY]
,DATEDIFF(second,s.[LCTime],p.[LCTime]) AS [Latency]
FROM p
LEFT JOIN s
ON s.[DBName] = p.[DBName]
AND s.[AGName] = p.[AGName]
ORDER BY p.[RSName],p.[AGName],p.[DBName],s.[RSName]
