locked
Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server RRS feed

  • Question

  •  Hi!

    I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,.  Each SQL Server instance is hosted on its own machine on sql2005 EE SP2.  The mirroring is working correctly.  If I submit to server ServerA command:

    ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

    , ServerB becomes the principal, it means that mirroring works correctly. 

    My issue is with the SQL Native Client  and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database.  I have setup my front-end application to use SQL Native Client and specified the failover server in connection string.  Here is the connection string that I am using :

    PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

    Everything works perfectly on my front-end application when ServerA is the principal.  If I execute on server ServerA command:

    ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

     , ServerB becomes the principal, and the failover occurs correctly on the database side.  The problem is that my front-end application is not able to query the database on ServerB. The error appears:

    Microsoft SQL Native Client error '80004005'

    Cannot open database "MYDBNAME" requested by the login. The login failed.

     This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

    When I swap server,  I  execute on server ServerB command:

    ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

    my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

    I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007).   Has anyone experienced this issue?  I'm thinking that it's a problem in the SQL Native client

    Wednesday, March 28, 2007 8:06 AM

All replies

  • Are you using connection pooling>?
    Wednesday, March 28, 2007 9:05 AM
  • Yes, I think so. I use SQL Native Client settings by default. As I know  SQL Native Client  uses connection pooling by default.
    Wednesday, March 28, 2007 9:36 AM
  • We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins.  Manual failover seems to work without problems (again this is with a witness).

     

    Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

    Monday, April 16, 2007 9:22 PM
  • There are different keywords for failover partner.  As far as I know there are three keywords that all work in different instances and fail in others.  In your situation I am not sure which you would have to use in the connection string.

     

    FailoverPartner=ServerB

    Failover_Partner=ServerB

    Failover Partner=ServerB

     

    I would just try them and do a manual failover of the database and see which one works and which one fails.  I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

    Thursday, June 28, 2007 5:02 PM
  • This also might help.  Not sure how accurate the statement is.  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

     

     

    Thursday, June 28, 2007 5:21 PM
  • As already stated, connection pooling will be your issue.

     

    turn it off and try again...

     

    The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

    Thursday, June 28, 2007 10:08 PM
  • Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

     

    Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

    Wednesday, September 12, 2007 3:35 PM
  • Hi All ~

    Finally MS tell us the turth: http://support.microsoft.com/kb/944099

    Wasted us so many days and nights!!!!   I am so angry now!

    And, no solution yet!!!!

    MS say this:


    SYMPTOMS

    Consider the following scenario. In an application, you use the SQL Native Client data provider to connect to an instance of Microsoft SQL Server 2005. The instance of SQL Server 2005 is configured to use database mirroring. Additionally, you specify a failover partner server in the connection string of the application. In this scenario, you may receive the following error message when you connect to the principal server after failover occurs:
    Internal .Net Framework Data Provider error 6
    Additionally, the following error message may be logged in the SQL Server error log:
    Error: 18456, Severity: 14, State: 16.
    Login failed for user DomainName/UserName

    CAUSE

    This problem occurs because the code in the System.Data.dll file does not correctly process state changes for the current connections in the connection pool. This behavior occurs when you specify a failover partner server in the connection string of the application.

    Wednesday, May 7, 2008 11:56 AM
  • We're having the same issue but using a Classic ASP app.  Have you had any luck getting the failover to work reliably since you posted?
    Tuesday, December 23, 2008 12:34 PM
  • Hi all,

    I am experiecing exactly the same issues as you all.  Alan328, did you or anyone manage to figure out a solution to this problem?

    Is it a connection pooling issue?

    Any advice would be greatly received. 

    Many thanks in advance


    Simon

     

    Thursday, June 10, 2010 1:19 PM