locked
AG sync issue RRS feed

  • Question

  • HI,

    We having an issue with our SQL 2016 AG. Our 2 nodes AG first was in asynchronous mode and we had to change it to synchronous mode. But becaus the Database is large it would take some times to both DBs become in Sync state.

    Unfortunatly someone has reboot the primary node yesteday at 15:00 hour and a failover is happend to the Node2 that has not yet was synced with Node1.


    How can we get the DBs in sync? we have Full en log backups.

    so yesterday until 15:00 hours people worked in DB of Node1 and after that worked in DB in Node2.

    I know we must take the DB out of the AG, but I would like to know how to go aobut the restore of backup to lose no data.
    Now we see this in dashboard:


    Shahin

    Friday, February 28, 2020 2:00 PM

All replies

  • Hi Shahin,

    >> Now we see this in dashboard:

    I can’t see the screenshot about the dashboard of your AG.  Did you post it? if not, please post it here.


    Best regards,
    Cathy 

    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, March 2, 2020 8:11 AM
  • Strange, I see the screenshot in my first post,

    Do you see this:


    Shahin

    Monday, March 2, 2020 10:38 AM
  • Strange, I see the screenshot in my first post,

    Do you see this:


    Shahin

    You might have to do restore and re-sync( Add database into AG again) if after failover the databases has gone out of sync and are not able to come in sync even after many hours.

    Did you saw any other error message  ?


    Cheers,

    Shashank

    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 TechNet Wiki Articles

    MVP


    Monday, March 2, 2020 3:40 PM
  • Hi Shashank,

    Thanks with your reply,

    I did speak to one of the managers and this time we got lucky because we needed only the data on the primary node and data lost on the secondary was acceptable

    we did exactly what you said, and breakdown the AG, and after that the DB on primary node was in resolving state so I run this query RESTORE DATABASE MYDB WITH RECOVERY and Database on primary came back online. after that rebuild the AG.

    Just one question,

    We want to know why when we change the AG mode from Asynchronous to  Synchronous the DB on the Node2 did not become in Sync with the Node1 ?

    How can We go about finding more info on this issue? or because we break down the AG we cannot get any more info on sysnc issue?

    Thanks


    Shahin

    Monday, March 2, 2020 3:49 PM

  • We want to know why when we change the AG mode from Asynchronous to  Synchronous the DB on the Node2 did not become in Sync with the Node1 ?

    I am not sure what could be exact reason but you should check this support article. Never change mode during tight prod hours always do it when load is relatively very less.

    How can We go about finding more info on this issue? or because we break down the AG we cannot get any more info on sysnc issue?

    Errorlog and extended events trace.


    Cheers,

    Shashank

    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 TechNet Wiki Articles

    MVP

    Monday, March 2, 2020 4:00 PM
  • Thanks for your reply,

    My AG has only 2 nodes and as I meantioned before when changed the mode from Asynchronous to  Synchronous the DB on the Node2 did not become in Sync.

    Also we have had install the SP2 and CU 10 both Nodes before this issue.


    Shahin

    Tuesday, March 3, 2020 10:10 AM
  • Thanks for your reply,

    My AG has only 2 nodes and as I meantioned before when changed the mode from Asynchronous to  Synchronous the DB on the Node2 did not become in Sync.

    Also we have had install the SP2 and CU 10 both Nodes before this issue.


    Shahin

    Shahin unless I have very close look at the logs and event trace it would be difficult to say what happened. Normally Aysnc to Sync should not cause problem when done during off business hours

    Cheers,

    Shashank

    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 TechNet Wiki Articles

    MVP

    Tuesday, March 3, 2020 11:24 AM
  • Hi Shank,

    Thank you for your reply,

    unfortunately the switch was done in business hours.

    As I mentioned issue for now is resolved, I just was wondering what has caused the sync issue.

    I did just check the SQL logs and could not find anything useful there.

    Any how, Thank you! 


    Shahin

    Tuesday, March 3, 2020 11:33 AM
  • Hi Shahin,

    >> As I mentioned issue for now is resolved,

    I am so glad to hear that you have resolved your issue. Please mark your solution as answer. This can be beneficial to other community members reading the thread.

    Best regards,
    Cathy 

    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

    Friday, March 6, 2020 8:02 AM