locked
DB Mirroring - Failover Scenarios RRS feed

  • Question

  • Hi,

    I m performing a failover testing for my database mirroring(SQL2008 Std with SP2). Below is the scenario:

    1. The database name is test.

    2. Mirroring is configured and both principal and mirror are synchronized. The Mirroring was setup with a Witness and works in a automatic failover.

    3. I have configured the mirroring connection timeout to 300 for the test database.

    4. Part of testing, the principal server was shutdown and the moment it was down ,all the database including test moved to mirror as principal copy. The Mirroring connection timeout 300 was not considered and test database was failedover immediately.

    Why the Database mirroring connection timeout was not considered in this scenario. Pls share your thoughts.

    Venkat

     

    Tuesday, August 9, 2011 2:15 AM

Answers

  • Hi Venkat,

    I think you mentioned the principal server was "shutdown". It will make the failover take place immediately. Because the OS will send message to Witness and Mirror it's going to shutdown instead of reboot or hanging.

    You can try to disconnect the network of principal server to see the failover will delay as timeout after 300 sec.

     

    • Proposed as answer by Stephanie Lv Thursday, August 11, 2011 6:51 AM
    • Marked as answer by Stephanie Lv Monday, August 15, 2011 11:56 AM
    Thursday, August 11, 2011 5:40 AM

All replies

  • http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/06/06/database-mirroring-questions-from-teched-failovers-and-partner-timeouts.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 9, 2011 7:44 AM
  • Hi,

    Thanks for the reply. I went thru the article and found the following:

    "Fast: The next fastest failover occurs when the machine hosting the SQL Server instance crashes or shuts down (e.g. power supply cord pulled out of the back). In this case, nothing happens until the partner time-out period has expired (as there's no OS running to return the immediate failure) and then a failover will occur. By default this will be in ten seconds, but you can change this as I explained above."
    If above is the solution, then the database which was configured for 300 secs as mirroring connection timeout should wait for 300 secs and then only it needs to be failed over to mirror as principal copy.
    But in real time, it is not working/waiting for 300 secs and database is failed over to mirror as principal copy immediately.
    Any thoughts.

     

    Tuesday, August 9, 2011 8:16 AM
  • Do you configure it by running ALTER DATABASE as described in the article?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 9, 2011 8:20 AM
  • yep. Followed the same steps.
    Tuesday, August 9, 2011 8:21 AM
  • Is that possible for anyone to try this scenario and post me the status.

    The testing scenario is.

    1. Configure the database mirroring and set the mirroring connection timeout to 300 secs.

    2. Shutdown the principal server or stop the sqlserver service in principal server.

    3. Check the staus of the database in the mirror server.

    In my case it failed over immediately. It is not waiting for the 300 secs.

    Tuesday, August 9, 2011 9:20 AM
  • Hi Venkat,

    This might be because when high saftey mode with witness is used then when automatic failover occurs witness forms the quorum with mirror immediately and time out is not considered in this case.

    The Principal, Witness and Mirror forms a quorom of three after a successful setup. A fail over will occur if Witness and Mirror  agrees that Principal is dead, so they will form a quorom of two and automatic fail over will initiate within 3 seconds. Your mirror will act as the Principal. Now, once you restart Server 1 which as the original principal server before the failover it will find out that the mirror server (server 2) is the acting Principal server, so Server 1 will take the role of Mirror server this time. In this case you need to manually failover to server 1 by executing this statement on Server 2
    "ALTER DATABASE database_name SET PARTNER FAILOVER" .

    Hope this helps!!


    Regards, Vishal Srivastava
    Tuesday, August 9, 2011 9:28 AM
  • Hi ,

    That's sounds grt. Is this documented in MSDN.

    I agree that mirror and witness forms a quorum and takes over the database as principal copy. But are you sure that timeout settings will be discarded.I am still confused why the timeout settings are not considered during the failover. Can you explain more.

    For example, the mirroring setup is frequently affected by network drops and a result of network drops the database will failover to mirror. To avoid a false failover or frequent failover due to intermittent network drops , the dba is increasing the timeout. If we compare the above solution like mirror and witness forms a quorum then failover will happen.

    Wat is the need of setting the connection timeout in the above scenario.

    Appreciate your thoughts on this.

     

    Tuesday, August 9, 2011 12:50 PM
  • Venkat,

     

    The timeout settings are used to as a standard waiting time for the mirror and witness.

    If the Mirror and witness are not able to connect to principal ( due to network glitches or soft errors which are not caused by instance  failure) within the timeout limit(here 300 secs),  witness will initiate failover from principal to mirror.The principal server may be up and running in this case but since time-out limit has been crossed failover will take place.

    Increasing the time out limit would solve the case for unnecessary failovers

    The following may explain more

    http://msdn.microsoft.com/en-us/library/ms190913.aspx

    Hope this helps

    Regards,

    Sumit Chhabra

    • Proposed as answer by Sumit.Chhabra Wednesday, August 10, 2011 3:16 PM
    Wednesday, August 10, 2011 3:05 PM
  • Hi Venkat,

    I think you mentioned the principal server was "shutdown". It will make the failover take place immediately. Because the OS will send message to Witness and Mirror it's going to shutdown instead of reboot or hanging.

    You can try to disconnect the network of principal server to see the failover will delay as timeout after 300 sec.

     

    • Proposed as answer by Stephanie Lv Thursday, August 11, 2011 6:51 AM
    • Marked as answer by Stephanie Lv Monday, August 15, 2011 11:56 AM
    Thursday, August 11, 2011 5:40 AM