locked
site level failover RRS feed

  • Question

  • scenario:

    2 datacenter with good connectivity. SQL 2012 principal and witness servers are placed in main site and mirroring server is place in DR site. high-safety mode with auto failover proposed.

    customer is asking what about the main site down? can force mirroring server continue to support the application (RMS2012) in DR site?


    Jason

    Wednesday, May 28, 2014 1:48 PM

Answers

  • Hello,

    If main site is completely down Quorum cannot be formed so automatic failover will not happen.Quorum is required for automatic failover.In this case you need to manually failover and point application to new principal

    If pricipal is down but witness is up and running it can form quorum with mirror and failover can happen.

    Please study various scenarios mentioned in below MS link. refer to section

    Database Mirroring Availability Scenarios

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    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


    • Edited by Shanky_621MVP Wednesday, May 28, 2014 1:59 PM
    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 1:57 PM
  • and what about after manually failover and then main site is up? there would be 2 principal sql servers.

    Jason

    When you do manual failover you run below commands to break mirroring. This will break mirroring and make Mirror online so now you dont have two principal as you dont have mirroring in place.

    ALTER DATABASE DB_NAME SET PARTNER OFF GO RESTORE DATABASE DB_NAME WITH RECOVERY

    GO

    Now when Previous principal comes online ( may be after couple of  hrs ) you reconfigure mirroring making original principal as mirror by taking backup from current server which is running( the mirror which is now running as principal failed ) and restoring it on previous principal with norecovery and configure mirroring . Then do failover to make it principal( previous principal as principal)


    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

    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 3:10 PM
  • so is it equivalent rebuild the SQL mirroring after main site is up?

    Jason


    Yes its kind of but that wont require a downtime, just a failover would require couple of seconds when you want to make original principal as new principal

    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


    • Edited by Shanky_621MVP Wednesday, May 28, 2014 3:38 PM
    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 3:37 PM

All replies

  • Hello,

    If main site is completely down Quorum cannot be formed so automatic failover will not happen.Quorum is required for automatic failover.In this case you need to manually failover and point application to new principal

    If pricipal is down but witness is up and running it can form quorum with mirror and failover can happen.

    Please study various scenarios mentioned in below MS link. refer to section

    Database Mirroring Availability Scenarios

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    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


    • Edited by Shanky_621MVP Wednesday, May 28, 2014 1:59 PM
    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 1:57 PM
  • thanks for that. can you specify the manual failover part?

    Jason

    Wednesday, May 28, 2014 2:53 PM
  • and what about after manually failover and then main site is up? there would be 2 principal sql servers.

    Jason

    Wednesday, May 28, 2014 2:59 PM
  • and what about after manually failover and then main site is up? there would be 2 principal sql servers.

    Jason

    When you do manual failover you run below commands to break mirroring. This will break mirroring and make Mirror online so now you dont have two principal as you dont have mirroring in place.

    ALTER DATABASE DB_NAME SET PARTNER OFF GO RESTORE DATABASE DB_NAME WITH RECOVERY

    GO

    Now when Previous principal comes online ( may be after couple of  hrs ) you reconfigure mirroring making original principal as mirror by taking backup from current server which is running( the mirror which is now running as principal failed ) and restoring it on previous principal with norecovery and configure mirroring . Then do failover to make it principal( previous principal as principal)


    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

    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 3:10 PM
  • so is it equivalent rebuild the SQL mirroring after main site is up?

    Jason

    Wednesday, May 28, 2014 3:27 PM
  • so is it equivalent rebuild the SQL mirroring after main site is up?

    Jason


    Yes its kind of but that wont require a downtime, just a failover would require couple of seconds when you want to make original principal as new principal

    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


    • Edited by Shanky_621MVP Wednesday, May 28, 2014 3:38 PM
    • Proposed as answer by Sofiya Li Friday, May 30, 2014 3:01 AM
    • Marked as answer by Sofiya Li Wednesday, June 4, 2014 8:19 AM
    Wednesday, May 28, 2014 3:37 PM
  • Will try it in lab. Thanks for the advise.

    Jason

    Wednesday, May 28, 2014 3:44 PM
  • Will try it in lab. Thanks for the advise.

    Jason

    You are welcome thanks for visiting Forum

    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

    Wednesday, May 28, 2014 3:49 PM
  • Hi Shanky,

    did you try this in lab or based on a theory that I don't know?

    from what I research, it doesn't seem a Microsoft support scenario (SQL mirroring with witness server)

    http://technet.microsoft.com/library/dd772673(WS.10).aspx


    Jason

    Tuesday, July 22, 2014 10:22 AM