none
Suspend an Availability Database in Always On RRS feed

  • Question

  • Hi,

    We have two VM with Always On installed and configured, we want to apply some changes in the replica, the plan is

    - Suspend Availability Database on Primary

    - Create a VM snapshot for replica

    - Apply changes on replica

    In case there is an issue and we need to rollback changes we need to restore a VM snapshot for replica and resume the vailability Database on Primary, is there anything I am missing ? do I need to stop the hourly Tlog backup on Primary ?

    Thanks for your help and feebacks

    Tuesday, June 18, 2019 3:11 PM

Answers

All replies

  • Before you start suspend data movement, is this what you are calling suspend AG on primary ? 

    Do you have to apply changes on both the replica ? How long will it take these factors are important. Are you ok with downtime. Do not break AG this can be achieved without breaking it. 


    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, June 18, 2019 5:49 PM
    Moderator
  • Hi

    I am talking about suspending the data movement and the change is done only on replica

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/suspend-an-availability-database-sql-server?view=sql-server-2017

    We will suspend the datat movement on the primary before we shutdown SQL on replica and take a VM snapshot on replica, at the end the data movement is resumed, I am wondering if there is other tasks we should perform like stop sql job for Tlog backups etc...

    Best regards

    Tuesday, June 18, 2019 6:48 PM
  • Ok, so you are going to perform the activity on both the replicas. Now what i would suggest is

    1. Suspend data movement but you need to work cautiously if the Ag is having sync replica. The reason is transaction log will start growing fast on primary since it will store log records which it needs to send to secondary replica when it comes online so before the disk fills up and application start crashing you need to bring secondary replica online and resume data movement. To mitigate this it would better to perform this activity at night when transaction flow is relatively very less. 

    2. Take snapshot backup and do your activity on secondary. After the activity is finished bring secondary replica online and resume data movement. it may take some time to get in sync with primary LET IT COMPLETE.

    3. After replica is synchronized fail over making secondary as primary.

    4. Repeat steps 1-3 on new secondary replica


    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

    Wednesday, June 19, 2019 6:38 AM
    Moderator
  • Hi S Rym,

     

    >>We will suspend the datat movement on the primary before we shutdown SQL on replica and take a VM snapshot on replica, at the end the data movement is resumed, I am wondering if there is other tasks we should perform like stop sql job for Tlog backups etc…

     

    You only need to suspend all the databases in availability group on the primary replica to stop suspend data movement. SQL AG keeps data synchronization by sending  log blocks to the secondary replica through the primary replica. It has nothing to do with log backup.

     

    Hope this could help you .

    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

    Wednesday, June 19, 2019 7:26 AM
  • Thx Dedmon and Shashank, this answer my question
    • Marked as answer by S Rym Wednesday, June 19, 2019 1:46 PM
    Wednesday, June 19, 2019 1:46 PM
  • If the posts were helpful you should be marking our response as answer, well actually our posts have helped you. 

    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

    Wednesday, June 19, 2019 2:09 PM
    Moderator