locked
Database mirroring failover when Principal goes offline ?? RRS feed

  • Question

  • Hi , I have sql server 2005 database X Principal , and Mirror database X on different SQL Server 2005 .NO WITNESS ,HIGH SAFETLY WITHOUT AUTOMATIC FAILOVER

    I know 

    Failover: manual  when principal is online  :

    -- At principal

    ALTER DATABASE DBNAME SET SAFETY FULL -- High Protection

    GO

    USE MASTER

    ALTER DATABASE DBNAME   SET PARTNER FAILOVER

    GO

    -- At new principal

    ALTER DATABASE DBNAME SET SAFETY OFF -- High Performance

    But , IF my Principal Goes offline ( may be due to OS or N/W) , how can I Bring  Mirror X Database online, so that clients can work  as it will be in Restoring mode, and I do not have Latest backups for 12 hrs ..
    I thought of 

    USE MASTER

     Alter X  DBNAME SET PARTNER OFF

    then 

    Restore   with latest backups   WITH RECOVERY but here i am loosing 12 hrs of data or may be it will not allow to restore 12 hrs back backupfile...

     

    Can any one let me know , how can i bring Mirror Database online when principal is no longer available ? adn do i need to start miroring again from scratch ?


    Thanks

    Tuesday, August 23, 2011 2:08 AM

Answers

  • Assuming your mirror was in sync. with the principle when the principle failed you can issue (on the mirror instance)...

    Use Master
    
    Go
    
    
    
    Alter Database <dbname> Set Partner Off
    
    Go
    
    
    
    Alter Database <dbname> With Recovery
    
    Go
    
    


    ...this should bring the mirror online and you can then redirect your clients to point to it. Can you elaborate as to why you mention a 12 hour lag? Do you mean the 12 hour gap after the principle fails?

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Stephanie Lv Tuesday, August 30, 2011 12:31 PM
    Tuesday, August 23, 2011 2:26 AM

All replies

  • will this work ? when the Principal is offline .

    GO TO MIRROR INSTANCE:::

     

    Alter database MirrorDBNAME  SET PARTNER OFF    —removes mirroring

    RESTORE DATABASE MirrorDBNAME WITH RECOVERY     —brings db online

     

    is this the correct proceedure ?

    Please let me know 

    Thanks


     

    Tuesday, August 23, 2011 2:22 AM
  • Assuming your mirror was in sync. with the principle when the principle failed you can issue (on the mirror instance)...

    Use Master
    
    Go
    
    
    
    Alter Database <dbname> Set Partner Off
    
    Go
    
    
    
    Alter Database <dbname> With Recovery
    
    Go
    
    


    ...this should bring the mirror online and you can then redirect your clients to point to it. Can you elaborate as to why you mention a 12 hour lag? Do you mean the 12 hour gap after the principle fails?

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Stephanie Lv Tuesday, August 30, 2011 12:31 PM
    Tuesday, August 23, 2011 2:26 AM
  • NOPE I SAID I Take backups every 12hrs , but anyways thats what i guessed and did , now mirror is online ..

    Thanks you Sir

    Tuesday, August 23, 2011 2:27 AM
  • Yes that works .....

    to break the mirroring -

    ALTER DATABASE <DBNAME> SET PARTNER OFF

    bring mirroring db online -

    Restore database <dbname> with recovery

    above steps should bring your mirroring db online.

     

     


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, August 23, 2011 6:58 PM