locked
Automatic failover failure RRS feed

  • Question

  • Hello,

    we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.

    When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.

    However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).

    Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.

    The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.

    The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.

    Any ideas on what might be the problem? Has anybody experienced a similar situation?

    Monday, November 27, 2006 9:43 AM

All replies

  • Can you see any errors or warnings from SQL error log in Principal & mirror servers?
    Monday, November 27, 2006 11:23 AM
  • These are entires I've found on the principal (former mirror) server

    2006-11-26 12:42:52.60 spid8s      Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
    2006-11-26 12:42:52.60 spid22s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:42:52.60 spid22s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
    2006-11-26 12:42:52.60 spid8s      The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
    2006-11-26 12:42:52.75 spid22s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:42:52.75 spid22s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
    2006-11-26 12:42:54.52 spid8s      Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.

     

    These are entries I've found on the witness server

    2006-11-26 12:45:03.36 spid27s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:45:03.36 spid27s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
    2006-11-26 12:45:03.50 spid19s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:45:03.50 spid19s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
    2006-11-26 12:45:03.82 spid19s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:45:03.82 spid19s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
    2006-11-26 12:45:04.03 spid19s     Error: 1474, Severity: 16, State: 1.
    2006-11-26 12:45:04.03 spid19s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.

     

    I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.

    Thanks,

     

    Ingmar

    Monday, November 27, 2006 3:14 PM
  • We are experiencing the same issue.

    Some additional information on our situation:

    We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).

    We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.

    We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.

     This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.

     

    Friday, December 8, 2006 5:06 PM
  • Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.
    Thursday, December 21, 2006 2:43 PM
  • Hello,

    We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.

    Regards,

    Ingmar

    • Proposed as answer by Kasturi Rajesh Saturday, October 10, 2009 8:46 AM
    Friday, January 19, 2007 12:46 PM
  • We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index.  Does anybody have an estimate as to when the "full" release of SP2 is to be released?

     

    Thanks,

    Jody

    • Proposed as answer by Kasturi Rajesh Saturday, October 10, 2009 8:46 AM
    Friday, January 19, 2007 5:47 PM