none
How to monitor how many data lag between primary and secondary replica RRS feed

  • Question

  • What's the best way to estimate the data difference between primary and secondary , replica (async)…..??

    Sunday, August 25, 2019 6:05 PM

All replies

  • Hi,

    I'm assuming this is an async setup as if it is set in synchronous commit mode then the data is committed to the secondary before the primary, so it will always be in sync.

    You can query for the committed and hardened LSNs on the AlwaysOn health dashboard page.  When you load that dashboard by right clicking the AlwaysOn group in SSMS then there is table at the bottom. You can add columns to that table to show the LSN details. 

    LSN is log sequence number with relates to VLFs in the log file.  It wont show what data is missing but will show the LSNs, which gives an idea. 

    Even in async though, the secondary isnt far behind unless there is latency between the 2 and large amounts of data in changed.

    Thanks,

    Matt

    Sunday, August 25, 2019 6:19 PM
  • Check for last commit time , estimated data loss values in always on primary replica dashboard , by default these values are not shown in always on dashboard you can add then by right clicking on the dashboard 

    Thanks, Nithin

    Monday, August 26, 2019 5:11 AM
  • what's the difference between last harden time and last commit time ?
    Monday, August 26, 2019 5:56 AM
  • Hi,

    last_redone_lsn numeric(25,0) Actual log sequence number of the last log record that was redone on the secondary database. last_redone_lsn is always less than last_hardened_lsn.
    last_redone_time datetime Time when the last log record was redone on the secondary database.
    last_commit_lsn Numeric(25,0) Actual log sequence number corresponding to the last commit record in the transaction log.

    On the primary database, this corresponds to the last commit record processed. Rows for secondary databases show the log sequence number that the secondary replica has sent to the primary replica.

    On the secondary replica, this is the last commit record that was redone.

    Source for the above is here


    Thanks,

    Matt

    Monday, August 26, 2019 9:10 AM
  • Hi sakurai_db,

     

    >>what's the difference between last harden time and last commit time ?

     

    There are two steps to synchronizing on the secondary replica, hardening the log block sent from the primary replica to disk, and then redoing it (executing to the database).

     

    last harden time : On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn). On a primary database, reflects the time corresponding to minimum hardened LSN.

     

    At regular intervals, the secondary replica sends the progress of the hardening and redo back to the primary replica.

     

     last commit time: The last time the synchronization progress from the secondary replica was received by primary replica.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 26, 2019 9:28 AM