Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server 2008 R2 - Databases are getting failed over to the Secondary server automatically.

Answered SQL Server 2008 R2 - Databases are getting failed over to the Secondary server automatically.

  • Thursday, February 21, 2013 10:31 AM
     
     
    Sign In to Vote
    0

    Hello,

    We have SQL Server 2008 R2 Ent. Databases are configured for DB mirroring having primary, secondary and witness server. We have very strange issue here , databases are getting failed over to automatically. There is no SQL server service restart or reboot happened.

    Network team confirmed there has been no communication failiure happened. Even SQL server doesnt have any error related to db mirroring.

    Can anyone help to find out what will be the reason.

    regards,

    Nitin Shetye

     

All Replies

  • Thursday, February 21, 2013 11:07 AM
    Answerer
     
     
    Sign In to Vote
    0

    Hello,

    What does the SQL Server errorlog say on the instance that fails?

    -Sean

    Sean Gallardy | Blog | Twitter

     
  • Thursday, February 21, 2013 12:42 PM
     
     
    Sign In to Vote
    0

    Hello,

    Unfortunately no logs on Mirrored server too. Just to add these servers are running on VMWARE.

    BR/Nitin

     
  • Friday, February 22, 2013 7:09 AM
     
     
    Sign In to Vote
    0

    timeout you use for database  mirroring and connection string.

    Ramesh Babu Vavilla MCTS,MSBI

     
  • Friday, February 22, 2013 8:01 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Nitin Sheye,

    In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs.

    The principal server has lost communication with the rest of the database mirroring configuration, while the mirror and witness retain quorum. The mirror server has detected the loss of the principal server.

    The speed of error detection and, therefore, the reaction time of the mirroring session to a failure, depends on whether the error is hard or soft. Some hard errors, such as network failures are reported immediately. However, in some cases, component-specific time-out periods can delay the reporting of some hard errors. For soft errors, the length of the mirroring time-out period determines the speed of error detection. By default, this period is 10 seconds. This is the minimum recommended value.

    Possible Failures During Database Mirroring: http://msdn.microsoft.com/en-us/library/ms190913(v=sql.105).aspx.
    Quorum: How a Witness Affects Database Availability: http://msdn.microsoft.com/en-us/library/ms189902(v=sql.105).aspx.

    Thanks. 

    Maggie Luo
    TechNet Community Support

     
  • Friday, February 22, 2013 6:57 PM
     
     Answered
    Sign In to Vote
    0

    Hi Nitin

    Its sure that it cannot happen by itself. There must be some issues due to which witness cause automatic failover.

    Maggie expalin very well you the same.

    If its happening on reular basis than you need to monitor & analyze things closly from SQL server error log, network connectivity & windows event lgo point of view on all 3 servers.

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

     
  • Friday, February 22, 2013 7:04 PM
     
     
    Sign In to Vote
    0

    Nitin,

    The best way to troubleshoot this approach is to check error logs for

    1. Network Errors

    2. Disk Errors

    3. SQL Server Error

    Also, check Once DB's are failed over to Secondary Server - do they repeat the same behaviour or they stay on Server 2. If they do stay on Server 2 than the issue with with Server 1 configuration

    If the behaviour exists on both the servers than you might want to involve Windows and Storage team for troubleshooting.

    Chintan Shah