locked
Initiating a Failover with T-SQL from the mirror and re-arming with T-SQL RRS feed

  • Question

  • Hi, 

    I'm running Standard Edition with the Synchronous Full Safety Option - only choice in standard I believe.  I don't have a Witness Server.

    I see that the only way to initiate a failover from the mirror is to disable the mirror running the following commands:

    ALTER DATABASE <database name> SET PARTNER OFF

    RESTORE DATABASE <database name> WITH RECOVERY

    How do I go about re-arming the mirror?  Do I need to completely reconfigure SQL Mirroring through script?  What's the best way to do this guys.

    We have a home grown High Availability system and I'm trying to integrate SQL Mirroring into it.  Our application controls which server runs our system and all that but I need to be able to control SQL Mirroring through scripting. 

    Thanks! 

    Wednesday, February 3, 2010 7:17 PM

Answers

  • Once your mirroring is switched off (using SET PARTNER OFF ) and failed over to mirror there are no ways that SQL server

    remembers about their old mirroring configuration. Once the dbs are failed over in a mirroing session , the system tables like

    sys.database_mirroring on both the instances are immediately updated.

     

    So the only way to re-enable back to original settings would be to re-configure the mirror.


    Thanks, Leks
    Wednesday, February 3, 2010 10:00 PM
    Answerer
  • You can also use ALTER DATABASE SET PARTNER FAILOVER in high safety mode.

    BY this way the mirroring partners are not disturbed (meaning only the role-reversal happens and mirroring direction is reversed - principal is mirror and vice-versa – Mirroring is not broken) and you can come back to the original state by issuing the alter database set partner failover again .

    But if you use ALTER DATABASE SET PARTNER OFF, the mirroring session is broken.


    Thanks, Leks
    Friday, February 5, 2010 1:20 AM
    Answerer

All replies

  • Once your mirroring is switched off (using SET PARTNER OFF ) and failed over to mirror there are no ways that SQL server

    remembers about their old mirroring configuration. Once the dbs are failed over in a mirroing session , the system tables like

    sys.database_mirroring on both the instances are immediately updated.

     

    So the only way to re-enable back to original settings would be to re-configure the mirror.


    Thanks, Leks
    Wednesday, February 3, 2010 10:00 PM
    Answerer
  • Thanks Leks.

    In that case than, is there any way to initiate a fail over from the mirror without dis-arming the mirror?
    Wednesday, February 3, 2010 10:26 PM
  • You can also use ALTER DATABASE SET PARTNER FAILOVER in high safety mode.

    BY this way the mirroring partners are not disturbed (meaning only the role-reversal happens and mirroring direction is reversed - principal is mirror and vice-versa – Mirroring is not broken) and you can come back to the original state by issuing the alter database set partner failover again .

    But if you use ALTER DATABASE SET PARTNER OFF, the mirroring session is broken.


    Thanks, Leks
    Friday, February 5, 2010 1:20 AM
    Answerer