If you ever wondered why I wrote a simple post like SQL Server AlwaysOn-Find my Primary, this post is really for you. There are a lot of times customers ask me how much time is my secondary behind my primary? Such a simple question can be answered easily using some good DMVs. Here is the script I use extensively when working with AlwaysOn customers to identify what is doing behind the scene’s in their setup.
SELECT AGS.name AS AGGroupName,
AR.replica_server_name AS InstanceName,
Db_name(DRS.database_id) AS DBName,
AR.availability_mode_desc AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
ORDER BY AGS.name,
Remember to run the above query on your Primary server. I have placed the output in two images for your reference.
The above columns give us an indication to our setup. What is the availability group, what are the servers, their roles, DB involved, their modes and what is their current state.
The second image which is above is the critical and the most important columns include when was the last_hardened_time and the last_redone_time. This shows how far away is my secondary. Also if the end_of_log_lsn of my Primary is different from the last_redone_lsn you know our secondary is behind my primary. For the above setup I have stopped to data sync to get this effect. Having said that, you get the drift how powerful this DMV can be.
This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).