Application problem with AlwaysOn read only replica in synchronous commit

  • sherrerk

    Ten Centuries

    Points: 1165

    I am trying to implement a read only replica to move much of the data read for an application to the secondary replica. Initially I had the the primary and secondary set to asynchronous commit. QA brought up an issue with creating entities from the application because after the creation of an entity the application turns around and repopulates the entire aggregate object. Well it seems that the application was reading the secondary replica before the data had been committed. Although I understand the issues that synchronous commits can cause, I went ahead and made the change as I expected it to fix the issue. After changing the primary replica to synchronous we still had the error, so I also changed the secondary although that makes no since, but the issue remains. I rebooted the primary machine with no luck. So, now I am stuck and confused. The application has to get a successful commit back before it can move on to read the data so I don't understand how the data is not current on the read replica. If anyone has any ideas or suggestions, please let me know... thanks!

  • sqlnyc

    SSCommitted

    Points: 1726

    There are two processes that run on the Secondary:

    1. hardening of the log

    2. REDO (replaying of the log)

    Not sure if this is what you're running into, but the Syncronization state you see in the dashboard and through the DMVs only applies to the hardening of the log. If for some reason REDO has latency, the data on the Secondary will not be up to date, even though the sync has committed and reported back to the primary that all is well.

    REDO latency issues such as this can cause problems when backing up on the Secondary, because the backup will include only the point up to which the log has had the REDO applied. REDO latency may also be the cause of the issue that you're seeing.

    sqlnyc

  • Perry Whittle

    SSC Guru

    Points: 233794

    the redo obviously has some latency here. Why is the application being forced to read a secondary in this case, it should be querying the primary I would have thought

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • sherrerk

    Ten Centuries

    Points: 1165

    Thank you for the explanation sqlnyc; I had a basic misunderstanding on how the log was being applied at the secondary. I have read a bit more to understand what you have described and now better understand what is going on, and yes I think this is what is happening.

    Perry, the list of problems with this application is a long one. Everything is done through NHibernate and there are large aggregate objects that are completely populated anytime anything is done. I was looking for someway to separate some of the read volume. I can at least get reporting off of the read only database. On the other hand I have had the opportunity to set up AlwaysOn finally and learn a bit about it. Currently testing fail over situations and looking to replace log shipping with it soon. I appreciate the help you gave me there. Ultimately One of the things I learned was to create a computer with appropriate permissions and adding the listener through script and getting away from using the "wizard".

  • Perry Whittle

    SSC Guru

    Points: 233794

    ok, so here's the thing, the more readonly workload you pass to the secondary the more it can impede the redo thread and so, the further behind the secondary may be.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • sherrerk

    Ten Centuries

    Points: 1165

    Yes, I understand that now. Thanks! I was not thinking about the fact that once the transaction log is written on the secondary that it then needs to be rolled forward to update memory.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply