Calculating estimated data loss with Always on
- by blakmk
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
