SQL: Always On Availability Group - Database Replication
Author: jason
Date: 2014-05-08 14:31:30
Category: Musing

Here is nice script to review the Availability Group Database Replication status with tSQL. At the end of the script I use DATEDIFF to compare the Last Send and Last Hardened datetime fields to determine how long the sync is taking.

Monitor Availability Groups: http://technet.microsoft.com/en-us/library/ff878305.aspx


--Database Replication Information
ag.name AvailabilityGroupName, r.replica_server_name, d.name DatabaseName, drs.is_local
, drs.database_state_desc, drs.synchronization_health_desc db_sync_health_desc
, drs.synchronization_state_desc, r.availability_mode_desc, c.is_failover_ready
, drs.suspend_reason_desc, drs.last_sent_time, drs.last_received_time, drs.last_hardened_time, drs.last_redone_time
, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate
, DATEDIFF(ss, drs.last_sent_time, drs.last_hardened_time) SyncTime_Seconds
FROM sys.dm_hadr_database_replica_states drs
INNER JOIN sys.availability_groups ag on ag.group_id = drs.group_id
INNER JOIN sys.databases d on d.database_id = drs.database_id
INNER JOIN sys.availability_replicas r on r.replica_id = drs.replica_id and r.group_id = drs.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars on ars.replica_id = r.replica_id and ars.group_id = r.group_id
INNER JOIN sys.dm_hadr_database_replica_cluster_states c on c.replica_id = drs.replica_id and c.group_database_id = drs.group_database_id;

jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009