0

Forward Clarification: I'm using RPO to describe "Estimated Data Loss", and RTO to describe "Estimated Recovery Time" in this question. I realize they're not exact definitions, but are close enough for the sake of the question.

At the bottom of this article here, there's an excellent block of code used to calculate RPO for a given Always-On enabled instance:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b3177c3d-5450-4948-a234-34a8dd41bf37/estimateddataloss?forum=sqlreplication

Estimated Data Loss

--@EstimatedDataLoss (RPO)
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

And with a few of my own modifications, a similar query can be derived for RTO:

Estimated Redo Rate

 --@EstimatedRecoveryTime (RTO)
select ar.replica_server_name, dcs.database_name, rs.redo_queue_size, rs.redo_rate, rs.redo_queue_size/rs.redo_rate as TRedo
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 ar.replica_server_name != @@servername

These metrics will work fine for my Always-On environments, however I'm looking to apply the same for SQL Mirroring on some pre-2012 environments.

Are there similar system objects to calculate the same metrics for SQL Mirroring?

Note: My references to what counts as RTO and RPO come from this Mircosoft article:

https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx

1 Answer 1

2

sp_dbmmonitorresults will generate the data you're after. You could either periodically log it to a table (if you want to gather historical data), or run it ad-hoc, storing the data in a temp table to be able to query to derive your desired metrics.

You can also use Perfmon counters to track this if you want to integrate it with a monitoring tool that relies on Perfmon.

As an aside, I would avoid using RPO and RTO in this circumstance (despite what Microsoft says). Mirroring/AGs should be treated as high availability, not disaster recovery. A user dropping a table cannot be protected by an availability group or mirror - they will happily replay that mistake on your secondary nodes.

Using these terms with people not familiar with the technology could be misleading.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.