locked
sql mirroring with high safety / no witness. how do we recover if primary database goes down? RRS feed

  • Question

  • We have SQL mirroring with high safety / no witness.  What steps are required to recover the secondary database when it is in "no recovery" mode?  We are assuming that the primary database location is no longer available.

    If we have SQL mirroring with high safety / witness and all three votes (primary database, secondary database, and witness) are not able to communicate.  Only the secondary database is online ( we can RDP to it).  The primary database location is hit by a hurricane which has destroyed the location.   How would we recover the secondary database which is in "no recovery" mode?  What steps would we take?

    Does asynchronous mirroring ( high performance ) require a witness?  Is there a automatic failover feature available for asynchonous mirroring? And how would do recover the secondary database when the primary database if permanently offline.   I am assuming the secondary database is in "no recovery" mode.   

    Will the following query below enable the secondary database to become "Recoverable"?  what is the is the purpose of using FILEGROUP=**** after the name of the database ( database is "Sales").

    The query using adventureworks2012 does not use the FILEGROUP=****.

    RESTORE DATABASE Sales FILEGROUP=SalesGroup2 WITH RECOVERY;

    OR

    RESTORE DATABASE AdventureWorks2012 WITH RECOVERY

    Can the RESTORE DATABASE query be used for asynchronous mirroring?


    dsk





    • Edited by kimdav111 Thursday, April 5, 2018 3:57 AM
    Thursday, April 5, 2018 2:48 AM

All replies

  • We have SQL mirroring with high safety / no witness.  What steps are required to recover the secondary database when it is in "no recovery" mode?  We are assuming that the primary database location is no longer available.

    If we have SQL mirroring with high safety / witness and all three votes (primary database, secondary database, and witness) are not able to communicate.  Only the secondary database is online ( we can RDP to it).  The primary database location is hit by a hurricane which has destroyed the location.   How would we recover the secondary database which is in "no recovery" mode?  What steps would we take?

    In this case you have to break mirroring and bring mirror database online. You need to use  Forced Service (with Possible Data Loss).

    Connect to the mirror server.
    
    Issue the following statement:
    
    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    
    where <database_name> is the mirrored database.
    
    The mirror server immediately transitions to principal server, and mirroring is suspended

    Does asynchronous mirroring ( high performance ) require a witness?  Is there a automatic failover feature available for asynchonous mirroring? And how would do recover the secondary database when the primary database if permanently offline.   I am assuming the secondary database is in "no recovery" mode.  

    Witness is only required for automatic failover and it would not be required in Async mirroring. High-performance mode, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server so you cannot have automatic failover in Async Mode mirroring. For recovering secondary database you have to run forced service allow data loss as posted above.

    The query does not seems ok look at the query I have given


    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

    Thursday, April 5, 2018 5:08 AM