December 2012 - Posts

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

Posted by blakmk