Very interesting question ..
Based on my understanding, I will try to answer this.
Other experts can correct me wherever I am wrong.
AlwaysON is different from Database Mirroring especially when sending the log blocks to the secondary replica/s.
The difference is how the log block gets send to the mirrored instance (for mirroring) or replica (for AlwaysON).
For Database Mirroring
- it flushes the log block to the disk and once completed locally, it sends the log block to the secondary server.
For AlwaysON
- it flushes the log blocks in parallel to the secondary using a secondary log scanner on primary.
- The beauty is that the secondary could have hardened log blocks even before the primary I/O completes which increases performance and narrows the NOT IN SYNC window.
- Now the interesting part is that to monitor above scenario, there are progress messages. The secondary sends messages to the primary indicating the hardened LSN level. The primary uses that info to help determine synchronization state.
So, there is no alert mechanism like Mirroring to actually be able to know the oldest unsent transaction. The only way to know is using DMVs. and possibly Extended Events.
As a side note, you can use Policy Based Management to monitor your AlwaysON health.
Below is the query that I am using to monitor my AlwaysON environment :
/************************************************************************************************
Author : Kin Shah
Purpose : Find "How far is secondary behind primary"
Written for DBA.STACKEXCHANGE.COM
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Disclaimer : Any scripts found on internet you must irst
- understand what it is doing
- then test it if it suits your requirements
I am not responsible for any data loss or any blue screen that you might get.
^^^
*** USE THIS AS PER YOUR OWN RISK ****
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*************************************************************************************************/
SELECT AGS.NAME AS AGGroupName
,AR.replica_server_name AS InstanceName
,HARS.role_desc
,Db_name(DRS.database_id) AS DBName
,DRS.database_id
,is_ag_replica_local = CASE
WHEN DRS.is_local = 1
THEN N'LOCAL'
ELSE 'REMOTE'
END
,AR.availability_mode_desc AS SyncMode
,DRS.synchronization_state_desc AS SyncState
,DRS.last_hardened_lsn
,DRS.end_of_log_lsn
,DRS.last_redone_lsn
,DRS.last_hardened_time
,DRS.last_redone_time
,DRS.log_send_queue_size
,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'
/*
if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server
then there is NO LATENCY
*/
,DATEDIFF(SECOND, DRS.last_redone_time, DRS.last_hardened_time)
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 Db_name(DRS.database_id)
,is_ag_replica_local
References :