Sql-server – SQLDriverConnect fails when attempting to connect to Failover_Partner

connectivitysql serversql-server-2008

SQLDriverConnect fails when attempting to connect to Failover_Partner.

Connection string being used:

Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx;
    Database=DB;Uid=user;Pwd=Pwd;Connect Timeout=300;Pooling=false

When we failover the database on dbx the next operation we perform SQLError(), which gives us the following information:

SqlState:  08S01
NativeError: 10054
MessageText: “TCP Provider: An existing connection was forcibly closed by
    the remote host."

At this point we disconnect then close off all open handles for that connection.

Then we call SQLDriverConnect using the same connect string. When we only have a single open connection this call succeeds. When we have multiple open connections we receive the following error:

[Microsoft][SQL Native Client][SQL Server]Login failed for user 'user'.

On the dbx server the error log shows:

Logon   Error: 18456, Severity: 14, State: 38.
Logon   Login failed for user 'XSUser'. Reason: Failed to open the explicitly 
    specified database. [CLIENT: 54.164.196.67]

On the dby server there are no new logs.

When we run with only one open connection the failover works fine. We only have a problem when there are multiple open connections.

When we start up with dbx failed over, we successfully connect to dby on all connection with the connect string supplied. At this point, when we are connected to dby and we can fail over dby and all connections successfully connect to dbx.

We suspected there might be a problem with connection pooling. However, I believe we have successfully disabled connection pooling:

SQLINTEGER pooling;
DBCRESULT rc = SQLGetEnvAttr(m_henv, SQL_ATTR_CONNECTION_POOLING , 
    &pooling, 0, NULL);

rc is 0, pooling is 0.

Any suggestions on how to proceed would be appreciated.

EDIT:

My testing procedure is to let the application connect and query some data. Then with the application's connection still open, manually use Management Studio on the primary to Failover the database. Then on the mirror server use Management Studio to run the same select stmt as the application, which succeeds. At this point I release the application to execute a SELECT. The call to SQLExecDirect fails, then I call SQLDriverConnect which also fails. I would have expected the call to SQLDriverConnect to succeed since the SELECT in Management Studio succeeded.

Best Answer

When database mirroring fails over to the mirror machine, the server runs startup code on the database including recovery steps to ensure the consistency of transactions in the database. This process can take some time; perhaps on the order of 10 to 20 seconds.

You need to wait for the database on dby to come online. I would recommend retrying the connection several times, with a wait of a second or two in between.

Don't disable connection pooling, that will only cause you performance problems, and has nothing to do with database mirroring.

Related Question