Timeout error when the connection string has a failover partner

Question

Hi,

I am getting a timeout expired error only when i use Failover Partner in the connection string:

Here is my scenario:
I have set up mirroring and it's working fine.  I even tried fail over from one of our web servers and it did failed over to the mirror database and the application is working fine.  Whereas when i tried to set-up the same from the other web server I am getting timeout error.

The web server has got SQLClient (2005)

Scenario 1:
<add key="ConnString" value ="server=10.10.1.201;Failover Partner=10.10.1.202;database=Company1;Integrated security=true;"/> 
 - The application fails with time out error.

Scenario 2:
<add key="ConnString" value ="server=10.10.1.201;Failover Partner=;database=Company1;Integrated security=true;"/> 
- The application works fine. (Note that Failover Partner is empty)

Scenario 3:
<add key="ConnString" value ="server=10.10.1.202;Failover Partner=;database=Company1;Integrated security=true;"/> 
- The application works fine. (Note that Server points to the mirror server)


Here is the error:
System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, String primaryHost, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

Thanks in Advance.

Deepak

---

Deepak

Answer 1

If you can increase the connection timeout parameter in the connection string then that could help you. Try the below parameters in the connection string.

Connect Timeout=200;
CommandTimeout 

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 2

I have just addressed this in my blog.

http://blogs.msdn.com/b/dipanb/archive/2011/05/14/you-may-see-an-instant-timeout-when-connecting-to-a-mirrored-database-from-a-net-application.aspx

HTH!

Dipanjan