4

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?

Improve this question

1 Answer 1

Reset to default
4

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 :

Improve this answer
2
  • Thanks. That's given me a better understanding of what options I have available.
    – paulH
    Apr 28, 2014 at 9:15
  • Np. Glad it helped 😊
    – Kin Shah
    Apr 28, 2014 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.