Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Calculate RTO(Recovery Time Objective) in AlwaysON Availability Groups
by BFarrell (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-01-18









AlwaysON Availability Groups - Data Synchronization Process







Solution:


Select n.group_name as 'AG_Name', n.replica_server_name as 'Replica', n.node_name as 'Node', rs.role_desc as 'Role',
rs.connected_state_desc as 'Connected State', ar.availability_mode_desc as 'Availability Mode', ar.failover_mode_desc as 'Failover Mode',
db_name(drs.database_id) as 'DBName', drs.synchronization_state_desc as 'synch_state_desc', drs.synchronization_health_desc as 'synch_health_desc', cs.join_state_desc,
DATEDIFF(MS,LAG(drs.last_commit_time) OVER(PARTITION BY drs.database_id ORDER BY drs.database_id DESC),drs.last_commit_time) AS RTO_ms, drs.last_commit_time,
drs.last_redone_time as 'last_redone_time_logrecord_sec', drs.last_hardened_time as 'last_hardened_time_logblock_lsn_sec',
drs.last_received_time as 'last_received_time_logblock_sec', drs.last_sent_time as 'last_sent_time_logblock', drs.last_hardened_lsn,
drs.last_redone_lsn, drs.recovery_lsn
from sys.dm_hadr_availability_replica_cluster_nodes n
join sys.dm_hadr_availability_replica_cluster_states cs on n.replica_server_name = cs.replica_server_name
join sys.dm_hadr_availability_replica_states rs on rs.replica_id = cs.replica_id
join sys.dm_hadr_database_replica_states drs on rs.replica_id=drs.replica_id
left join sys.availability_replicas ar on ar.replica_id = rs.replica_id
order by db_name(drs.database_id) asc, rs.role_desc asc






sys.dm_hadr_availability_replica_cluster_nodes
sys.dm_hadr_availability_replica_cluster_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_database_replica_states
sys.availability_replicas
sys.availability_group_listeners