Calculating estimated data loss with Always on
Ever wondered how calculate estimated data loss (time) for
always on. The metric in the always on dashboard shows the metric quite nicely
but there does seem to be a lack of documentation about where the metrics
---come from. Heres a script that calculates the data loss ( lag ) so you can
set up alerts based on your DR SLA's:
WITH DR_CTE ( replica_server_name, database_name,
last_commit_time)
AS
(
select
ar.replica_server_name, database_name, rs.last_commit_time
from
master.sys.dm_hadr_database_replica_states rs
inner join
master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
inner join
sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id =
rs.group_database_id and rs.replica_id = dcs.replica_id
where replica_server_name !=
@@servername
)
select ar.replica_server_name, dcs.database_name,
rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss,
DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'
from master.sys.dm_hadr_database_replica_states rs
inner join master.sys.availability_replicas ar on
rs.replica_id = ar.replica_id
inner join sys.dm_hadr_database_replica_cluster_states dcs
on dcs.group_database_id = rs.group_database_id and rs.replica_id =
dcs.replica_id
inner join DR_CTE on DR_CTE.database_name =
dcs.database_name
where ar.replica_server_name = @@servername
order by lag_in_seconds desc