none
Manual Failover RRS feed

  • Question

  •  

    Hello,

     

    I have set up database mirroring but I have set it not to use a witness so that if my principal server gets rebooted or is only down for a few minutes it does not failover. 

     

    But manual failover can only be initiated from the principal.  So if I do lose my main server and do want to failover, how can this be done from the mirror?

     

    Many Thanks for your help.

     

    Simon

    Tuesday, June 17, 2008 3:23 PM

Answers

  • 1. If you had configured mirroring using Safety Full (Without a witness) i.e High protection mode then follow as stated below,

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF
    RESTORE DATABASE <database name> WITH RECOVERY

    2. If you had configured mirroring using Safety OFF i.e High performance mode then follow as stated below,
    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively.
    Server_A fails. You need to execute the following on Server_B to make the database service available:
    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    Refer the topic 'Loss of principal' in the below link for more information.
    http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

    - Deepak
    Tuesday, June 17, 2008 3:29 PM
    Moderator

All replies

  • 1. If you had configured mirroring using Safety Full (Without a witness) i.e High protection mode then follow as stated below,

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF
    RESTORE DATABASE <database name> WITH RECOVERY

    2. If you had configured mirroring using Safety OFF i.e High performance mode then follow as stated below,
    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively.
    Server_A fails. You need to execute the following on Server_B to make the database service available:
    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    Refer the topic 'Loss of principal' in the below link for more information.
    http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

    - Deepak
    Tuesday, June 17, 2008 3:29 PM
    Moderator
  • In scenario 1 - are you guaranteed not to lose any data?

    Monday, June 30, 2008 10:55 AM
  • There might be some data loss in high performance and high protection mode since witness server is not available.

     

    - Deepak

    Monday, June 30, 2008 12:29 PM
    Moderator
  • Hi all,

     

    Can someone please explain to me how having a witness server would guarantee no data loss, even if someone was to put a bullet through my hard disk?

     

    Thanks.

     

    Monday, July 7, 2008 8:33 AM
  • Hi,

     

    Using winess server you will be able to configure mirroring in High availability mode (i.e synchronous mode), hence transactions in the primary will commit if the mirror also commits it.

     

    Consider witness server is monitoring both principal and mirror server. If your principal server is down, witness won't be able to connect principal server hence it will intiate the mirror server to ping principal and if mirror also not able to connect to principal then automatic failover occurs and your mirror server will become principal server.

     

    Check out the link below

     

    Types and Operating Modes in Mirroring

    Database Mirroring Role Change

    Monday, July 7, 2008 11:15 AM
    Moderator
  • Can you configure mirroring to not automatically failover?

     

    I'm a bit concerned about a network blip causing an automatic failove.

     

    Monday, July 7, 2008 11:46 AM
  • Yes you can configure mirroring in asynchronous mode, but you won't get automatic failover feature. You need to manually failover to mirror server incase of problem occurs.

    Monday, July 7, 2008 1:10 PM
    Moderator
  • Hi Vidhya Sagar,

     

    Many thanks for your response. 

     

    Your link named "Database Mirroring Role Change" doesn't work.

     

    Please can you clarify: to run a mirror with manual failover and no data loss then I need to run in "High Protection Mode", i.e. without a witness and transactional safety set to full.

     

    Thanks.

     

    Monday, July 7, 2008 2:00 PM
  • Check this and see if it helps, http://sql-articles.com/index.php?page=articles/dbmrrlchg.htm

    - Deepak
    Monday, July 7, 2008 3:30 PM
    Moderator
  • Hi,

     

    Did the article given by Deepak worked for you??

     

    While configuring mirroring don't select witness server and complete the mirroring wizard, in the end you will be promted to start the mirroring, there click on "Don't start mirroring" button, in another window you have the option to select High protection & High performance mode, select the required one and click on start mirroring button to intiate mirroring between the instances.

    Tuesday, July 8, 2008 1:52 PM
    Moderator
  • Yes it does thanks.

     

    However, the article does contradict Deepak's statement:

     

    "There might be some data loss in high performance and high protection mode since witness server is not available."

     

    As it states that "High Protection Mode" would give me synchronous communication between principal and mirror.  I.e. no data loss, but failover would be manual.  Which is exactly what I'm after.

     

    So was Deepak's statement incorrect?  If I run the Mirror in "transactional safety FULL" with no witness?

     

    Tuesday, July 8, 2008 2:26 PM
  • Anyone like to comment on this?

     

    Friday, July 11, 2008 8:15 AM
  •  

    I hope Deepak is right.. Consider you have witness and auto-routing in application to point mirror server incase of failure, In this case the application will works with principal and if principal fails then witness makes mirror as principal and hence you will be able to work with mirror automatically with out user intervention.

     

    If you don't have a witness (but synchronous) then

     

    §  Only a manual failover is possible, because there is no witness to fill the tiebreaker role.

    §  Forced failover is possible when Principal database is not available, with possible data loss. When you do this if some of the transactions is not commited in mirror then those datas will be loosed, I hope this is what Deepak is trying to say.

    Friday, July 11, 2008 11:58 AM
    Moderator
  • Hi Vidhya,

     

    Thanks for the response.

     

    I have don't have auto-routing and I'm unlikely to have this in the future as the Applications are written Delphi.

     

    So if I'm running in synchronous and the principle fails, all transactions up to this point have been sent to the mirror?  I understand that the applications will fail at this point as the principle DB doesn't exist, this is acceptable though as the user will get an error.

     

    I can then failover the mirror manually using:

     

    ALTER DATABASE TestDB SET PARTNER OFF

    RESTORE DATABASE TestDB WITH RECOVERY

     

    And then point my applications to the mirror server and everything works.  All I need to confirm is that all transactions committed at the mirror (up to the point of failure) will be committed at the Mirror?

     

    Friday, July 11, 2008 12:18 PM
  • Vidhya\Deepak can you comment please?
    Friday, July 18, 2008 1:56 PM