SQL Server 2012 – Oldest Unsent Transaction Alert for Availability Group

availability-groupssql-server-2012

In SQL Server 2008 when using database mirroring, it was possible to set up alerts when the oldest unsent transaction exceeded a certain threshold. We could check the state of any database mirroring using the Launch Database Mirroring Monitor menu command.

In SQL Server 2012 we now have Availability Groups. It would seem to me that the same potential problem exists that if transactions cannot be sent to the secondary server then the mirroring will be suspended, and potentially the transaction logs will fill up all our disk space. However, the Database Mirroring Monitor tool does not seem to recognise databases in Availability Groups as being mirrored.

Is there a way to set up an alert if the oldest unsent transaction exceeds a certain value? Is there a way to set that certain value?

Best Answer

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
    */
    ,'seconds behind primary' = CASE 
            WHEN EXISTS (
                    SELECT DRS.last_hardened_lsn
                    FROM (
                        (
                            sys.availability_groups AS AGS INNER JOIN sys.availability_replicas AS AR ON AGS.group_id = AR.group_id
                            ) INNER JOIN sys.dm_hadr_availability_replica_states AS HARS ON AR.replica_id = HARS.replica_id
                        )
                    INNER JOIN sys.dm_hadr_database_replica_states DRS ON AGS.group_id = DRS.group_id
                        AND DRS.replica_id = HARS.replica_id
                    WHERE HARS.role_desc = 'PRIMARY'
                        AND DRS.last_hardened_lsn = DRS.last_hardened_lsn
                    )
                THEN 0
            ELSE datediff(s, last_hardened_time, getdate())
            end
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 :

Related Question