locked
Reboot Sequence RRS feed

  • Question

  • I have to reboot both the principle and mirror servers and I do not want a failover to occur in the process. Would anyone please tell me the proper steps to do it. I've done a search on the internet and are confused about the uses of "set witness off", "set partner suspend" and "set partner off" and am at a loss of which one I should use.

    Monday, November 21, 2016 3:34 AM

Answers

  • HI,

    I believe option you have selected is High availability(Transaction safety)--"high safety with automatic failover". , If we consider transaction safety then its considered to be High availability but we do have other asynchronous option like High performance which can work with out mirror server for some period , but fill-up the log(unsend log), as per my knowledge it not good to go with mirroring if mirror server is down for long time.

    Based on above information you have broke mirroring and need to setup from backup + restore , to confirm what is the status of mirrored database now on principle server.


    Thanks, Dev SQL DBA



    • Edited by Hanuman D Tuesday, November 29, 2016 8:56 AM
    • Marked as answer by m24545 Tuesday, December 6, 2016 3:33 AM
    Tuesday, November 29, 2016 8:13 AM

All replies

  • Hi m24545,

    From my perspective:

    1.        If you are using high safety with automatic failover mode, I would suggest you remove witness before reboot and set database mirroring to high safety without automatic failover mode.
    2.        Pause database mirroring session before reboot.
    3.        Reboot Mirror server.
    4.        Reboot Principal server.
    5.        After Principal server back online, resume database mirroring session. You could add witness server back at this moment and change back to high safety with automatic failover mode if necessary.

    If you have any other questions, please let me know.

    Regards,

    Lin


    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.

    • Proposed as answer by Shanky_621MVP Tuesday, November 22, 2016 5:05 AM
    Tuesday, November 22, 2016 2:28 AM
  • Thanks for the detailed instruction. Since I have only a very limited time window to reboot the two servers. I just wonder if it would be easier if I can just use "alter database xxx set partner suspend" before the reboot and then "alter database xxx set partner resume" after the reboot to avoid failover to occur.
    Tuesday, November 22, 2016 5:02 AM
  • Yep that should work and that is what called as suspending the database mirroring.

    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, November 22, 2016 5:05 AM
  • I rebooted the principle and mirror servers last Friday and things didn't go quite as expected. I started off by using "set partner suspend" and then proceeded to reboot the mirror server and unfortunately the mirror server froze at the Windows startup screen because of a newly installed antivirus agent conflict . At that point, I was expecting the principle database to continue to work but user calls started to pour in. I found out that status of the principle database had changed to disconnected. I eventually had to use "set partner off" to remove the database mirroring to make the principle database work again. My question is why the principle database didn't work when the mirror server was down. Is it by design or just a misconfiguration on the servers? Thanks
    Monday, November 28, 2016 6:49 AM
  • Your Mirroring operating mode should answer your question because it depends on operating mode set, if you are using High availability(Witness configured) or High Protection you may face issues if mirror server is offline, as they are in transactional safety FULL,

    Please let us know the operating mode set for you database.


    Thanks, Dev SQL DBA

    Monday, November 28, 2016 2:23 PM
  • The mirroring mode is "high safety with automatic failover". I suppose this mode is not a very " high availability" solution if service to users has to stop when the mirror server goes down.

    Since the mirroring has been broken for a few days, the databases in the principal and mirror servers will be very different now. Do I have to do a backup in the principal server and restore it in the mirror server before I re-establish the mirror again, or can I just re-establish the mirror without performing backup/restore and the system will take care of the rest? Thanks

    Tuesday, November 29, 2016 7:45 AM
  • HI,

    I believe option you have selected is High availability(Transaction safety)--"high safety with automatic failover". , If we consider transaction safety then its considered to be High availability but we do have other asynchronous option like High performance which can work with out mirror server for some period , but fill-up the log(unsend log), as per my knowledge it not good to go with mirroring if mirror server is down for long time.

    Based on above information you have broke mirroring and need to setup from backup + restore , to confirm what is the status of mirrored database now on principle server.


    Thanks, Dev SQL DBA



    • Edited by Hanuman D Tuesday, November 29, 2016 8:56 AM
    • Marked as answer by m24545 Tuesday, December 6, 2016 3:33 AM
    Tuesday, November 29, 2016 8:13 AM