locked
Problem to connect after manual failover and the new mirror is running RRS feed

  • Question

  • Hi,

    I have some problem with connecting to the new principal (the old mirror) after a manual failover and the old principal (the new mirror) is still running (The new mirror database is in mode: Mirror, Synchronized / Restoring...)

    I have a classic ASP website and I am using the following connection string:

    Set MM_TestDB_STRING = Server.CreateObject("ADODB.Connection")
    MM_TestDB_STRING.ConnectionString = "Driver={SQL Server Native Client 10.0}; Server=WIN-SERVER1.mydomain.com; Failover_Partner=WIN-SERVER2.mydomain.com; Database=TestDatabase; Uid=test; Pwd=test;"
    MM_TestDB_STRING.Open

    If the new mirror is still running after a manual failover (WIN-SERVER1.mydomain.com) I get the following error:

    Microsoft OLE DB Provider for ODBC Drivers
    Error code: (0x80040E4D)

    Description: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "TestDatabase" requested by the login. The login failed.


    If I stop the SQL Server service on the new mirror (WIN-SERVER1.mydomain.com) after a manual failover I get the following error:

    Microsoft OLE DB Provider for ODBC Drivers
    Error code: (0x80004005)

    Description: [Microsoft][SQL Server Native Client 10.0]TCP Provider: The wait operation timed out.


    But if I use the following connection string with a bogus server everything works correctly and the connection is using the failover_partner and connect to the new principal (WIN-SERVER2.mydomain.com):

    Set MM_TestDB_STRING = Server.CreateObject("ADODB.Connection")
    MM_TestDB_STRING.ConnectionString = "Driver={SQL Server Native Client 10.0}; Server=blablablabla.mydomain.com; Failover_Partner=WIN-SERVER2.mydomain.com; Database=TestDatabase; Uid=test; Pwd=test;"
    MM_TestDB_STRING.Open


    All of this is very frustration since in many cases the old principal may not be entirely down and if it is not entirely down the connection will always fail.

    What am I doing wrong?

    • Edited by Kalle_L Wednesday, October 5, 2011 3:08 PM
    Wednesday, October 5, 2011 3:06 PM

All replies

  • Just confirmed that if I recycle the application pool after a failover I am able to connect to the new principal.

    Same problem here:

    http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/88e03117-6eea-43b0-9747-d07449c31b4e/

    This error has been confirmed by MS and here is a kb from April 28, 2009:

    http://support.microsoft.com/kb/944099

    It's now Q4 2011 and this problem has not been fixed in any updates yet.

    Does anybody know any possible workaround or does anybody have the fix from MS?
    Apparently you need to contact MS by phone to receive the fix, of reasons unknown.

    Wednesday, October 5, 2011 10:43 PM
  • have you got the logins with same SIDs on both servers?
    http://uk.linkedin.com/in/ramjaddu
    Friday, October 7, 2011 3:14 PM
  • Yes,

    The logins have the same SIDs on both servers.

    Everything works correctly when doing a failover to the new mirror from the new principal (from "failover_partner" to "server") but not the other way around.

    I looks like KB 944099 was included in a .NET service pack that I have installed on the server, but maybe this update does not fix the problem for the ADODB-object that I am using in the classic ASP website.

    Is somebody able to reproduce this problem using my connection strings and creating a classic ASP-page?

    Set MM_TestDB_STRING = Server.CreateObject("ADODB.Connection")
    MM_TestDB_STRING.ConnectionString = "Driver={SQL Server Native Client 10.0}; Server=WIN-SERVER1.mydomain.com; Failover_Partner=WIN-SERVER2.mydomain.com; Database=TestDatabase; Uid=test; Pwd=test;"
    MM_TestDB_STRING.Open

    Sunday, October 9, 2011 11:09 AM
  • I do have some classic ASP pages running on one server that do connect to mirroring DBs without ever having seen that problem. Reading through the KB article provided above it seems though that this is not actually a .Net error, but an error in the ADO.Net provider, which would mean that maybe there is the same problem in the SNAC (SQL Native Client). What version of the SNAC do you have installed? Did you get the latests service packs / cumulative updates applied on it? And could you maybe try SNAC 10.5 to confirm?

     

    Cheers

    Lucifer

    Tuesday, October 11, 2011 5:58 PM
  • Thank you very much for replying Lucifer.

    I have SQL Server Native Client 10.0 (version 2007.100.4064.00) installed on the web server.


    The web server is running Windows Server 2008 32-bit. Is it possible to install SNAC 10.5 on a 32-bit system?

    If so, where can I find SNAC 10.5 for 32-bit?

    I found the 64-bit version of SNAC 10.5 but it was of course not possible to install it on a 32-bit system.

     

    Found the 32-bit version of SNAC 10.5. I'll try it and get back with the results.

    The databases that is mirrored runs SQL Server 2008 R2 and the SQL Server version is 10.50.2500

    • Edited by Kalle_L Wednesday, October 12, 2011 6:54 PM
    Wednesday, October 12, 2011 6:23 PM
  • Just tried SNAC 10.5 and it did not fix the problem :(

    The problem is the same as before:

    After failover from principal to mirror I get the error: Cannot open database "TestDatabase" requested by the login. The login failed.

    After doing an application pool recycle in IIS everything starts working again and I can connect to the new principal (old mirror).

    The outer way around, from new principal (old mirror) to new mirror (old principal), is working without any recycling of the application pool in IIS.

    Should I give up?

    Wednesday, October 12, 2011 9:01 PM
  • While the problem persists do you see any entries in the SQL Server Errorlog on the new principal?

    Thursday, October 13, 2011 1:44 PM
  • I do not see any error messages in the SQL Server Errorlog on the new principal. But on the old principal (the new mirror) I see the following:

    10/13/2011 23:16:58,Logon,Unknown,Login failed for user 'test'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.1.1.1]
    10/13/2011 23:16:58,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.

    But as always, recycling the application pool in IIS everything starts working again and it is possible to connect to the new principal without no problem.

    Thursday, October 13, 2011 9:27 PM
  • Do these error messages stop after a while? Or do they just continue popping up?

    So far this looks as if the client component would try reusing the connection after the mirror failover, which it is not supposed to do. After the connection bails out with an error to the client it is necessary for the client implementation to issue another Connection.Open command for the SNAC to do the reconnecting. I am not really a development expert, so please anybody correct me if I am wrong, but I think I remember a comment from a while back that it was possible in VB (pre .Net) to issue a connection reset rather than closing and opening it again, which was at this point considered to save time after a network outage. Maybe the implementation of your software uses an optimization like this anywhere? (This would unfortunately not really explain why it works when you fail back...)

    From a troubleshooting point of view what I would look at next is a network trace of the webserver, starting before the failover, then during a non-working connection attempt, until after the iisreset was issued and it's working again.

    Lucifer

    Friday, October 14, 2011 5:53 PM
  • The errors continue until the application pool is recycled. The client component is probably trying to reuse the connection after the failover.

    I can not find any information about issue a connection reset.

    If your classic ASP page is running IIS 7, is it possible for you Lucifer to do a manual failover and check if the connection to the new principal is working correctly or if the same problem will present itself and you need to do an application recycle?


    • Edited by Kalle_L Monday, October 17, 2011 2:08 PM
    Monday, October 17, 2011 2:08 PM
  • With the applications I have on my IIS 7 servers the problem does not show up. I had a chat with my developer earlier on to see how he handles the connections in the app and what he told me is that after a connection error (no matter which sort of error) he completely disposes of the ADO object and creates a new one. I think that might be key in this scenario... Unfortunately if I'm right you have to look into the code of the web application to confirm that...
    Tuesday, October 18, 2011 5:19 AM
  • Look at the database user mappings "test" on new principal server.  You need to re-map user account for successful connection available. look at this problem

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


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Thursday, April 26, 2012 1:21 PM