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

Published Tuesday, December 18, 2012 10:36 AM by blakmk

Comments

No Comments