none
AlwaysOn Checkpoints on Secondary Servers RRS feed

  • Question

  • Does anyone know how checkpoints work in AlwaysOn, specifically in regard to secondary servers.

    1. How do checkpoints get applied to secondary servers.

    2. I am able to run a checkpoint manually on a secondary server I get neither an error nor is the checkpoint applied.

    Monday, January 4, 2016 2:51 PM

Answers

  •  Yes.  The important thing to understand here is that the primary and secondaries have different mechanisms for updating the database. 

    On the primary the database pages are updated in memory by user transactions, and then lazily written to the database files by the Lazy Writer and Checkpoint processes.

    On the secondary the database pages are updated by the redo process reading from the log file and applying the changes to the database files.  The redo process updates the database pages on disk, not just in the page cache.  After redo at the secondary the log file space can be reused (so long as somewhere a log backup was taken), so the redo needs to harden the transactions directly into the database file (also that's just how redo works).

    There's no reason to have a checkpoint on a secondary, since the database pages in the page cache can never be different than the pages on-disk.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MSullivan Wednesday, January 6, 2016 3:54 PM
    Wednesday, January 6, 2016 2:39 PM

All replies

  • You can run manual checkpoints on Sec replica there is no issue with that.

    AFAIK there is no change in Checkpoint behavior on secondary its same as primary and same as with Normal standalone SQL Server.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, January 4, 2016 4:30 PM
    Moderator
  • Sorry to contradict you Shanky, but I doubt that you can run manual checkpoints on Secondary, after all, the DB is read only...

    As far as I understand the process Checkpoint is running constantly on the secondary, more or less in parallel with the redo thread. But if you need to know for sure I would need to check.

    Is there any specific reason why you are asking?

    Monday, January 4, 2016 5:56 PM
  • Sorry to contradict you Shanky, but I doubt that you can run manual checkpoints on Secondary, after all, the DB is read only...

    I just ran and it worked.....

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 5, 2016 10:42 AM
    Moderator
  • Sorry to contradict you Shanky, but I doubt that you can run manual checkpoints on Secondary, after all, the DB is read only...

    I just ran and it worked.....

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Yeah, it works... but it doesn't do anything. At least when I run it...
    Tuesday, January 5, 2016 11:27 AM
  • Yeah, it works... but it doesn't do anything. At least when I run it...

    I am not sure what you are referring to here by saying does not do anything. Mostly checkpopint in system controlled but YES manual checkpoint will have affect on secondary replicas. I cannot tell you scenario exactly but as far as logic goes it should.

    And BTW how read only database has any relation to checkpoint which is internal process and does not makes changes to user databases ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 5, 2016 11:41 AM
    Moderator
  • Yeah, it works... but it doesn't do anything. At least when I run it...

    I am not sure what you are referring to here by saying does not do anything. Mostly checkpopint in system controlled but YES manual checkpoint will have affect on secondary replicas. I cannot tell you scenario exactly but as far as logic goes it should.

    And BTW how read only database has any relation to checkpoint which is internal process and does not makes changes to user databases ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    What I did to test it was:

    Setup AlwaysOn AG with readable secondary. (If the DB is not readable I can't even change into it on the secondary to issue a manual checkpoint)

    Then open a query window and issue a CHECKPOINT command

    And while doing that I was looking at the Checkpoint Pages/Sec performance counter. And as this counter remained unaffected it for me seems that the Checkpoint command on a secondary is just ignored. But if you have other experiences I am happy to hear them.

    Tuesday, January 5, 2016 11:53 AM
  • That is cool but please note that the counter Checkpointpages/sec is *not only* updated by manual checkpoint even diff backup or full backup can do this. BOL says

    Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

    And are you sure that Checkpoint ALWAYS flushes dirty pages to disk. What if it has nothing to flush. What if you execute Checkpoint 10 times is it guaranteed that on 10th execution it will flush something. I doubt that


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 5, 2016 12:03 PM
    Moderator
  • That is cool but please note that the counter Checkpointpages/sec is *not only* updated by manual checkpoint even diff backup or full backup can do this. BOL says

    Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

    And are you sure that Checkpoint ALWAYS flushes dirty pages to disk. What if it has nothing to flush. What if you execute Checkpoint 10 times is it guaranteed that on 10th execution it will flush something. I doubt that


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    I did make sure that there is something to flush, and I also did flush pretty much at the same time on the primary to check what should be there... So yes, you are right that the counter is not a direct indication, but if you do it a couple of times it gives you a feeling what's happening. (Which in my trial runs was: nothing...)
    Tuesday, January 5, 2016 12:41 PM
  • The debate that its occurring is exactly my reason for the question.  I have run manual checkpoints against the database and the checkpoint does not appear in the log file.  We have an unusual environment that requires us to manually checkpoint and wanted to know the affects on our systems.

    I also noticed that not all checkpoints I issued on the primary server made it over to the secondary server as well.  If I issue a checkpoint on the primary should that be logged on the secondary server as well?

    Wednesday, January 6, 2016 2:25 PM
  •  Yes.  The important thing to understand here is that the primary and secondaries have different mechanisms for updating the database. 

    On the primary the database pages are updated in memory by user transactions, and then lazily written to the database files by the Lazy Writer and Checkpoint processes.

    On the secondary the database pages are updated by the redo process reading from the log file and applying the changes to the database files.  The redo process updates the database pages on disk, not just in the page cache.  After redo at the secondary the log file space can be reused (so long as somewhere a log backup was taken), so the redo needs to harden the transactions directly into the database file (also that's just how redo works).

    There's no reason to have a checkpoint on a secondary, since the database pages in the page cache can never be different than the pages on-disk.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MSullivan Wednesday, January 6, 2016 3:54 PM
    Wednesday, January 6, 2016 2:39 PM
  • Thank you David was a wonderful explanation.

    Wednesday, January 6, 2016 3:55 PM