Failover Partner behavior for Sql ConnectionString

问题:

I'm using .Net 4.0 and SqlConnection/SqlCommand to connect to a 2008 R2 SQL server that has mirroring setup. The ConnectionString specifies a Failover Partner

Can anyone explain how the ConnectionTimeout setting affects the ability for a client to failover to the mirror? Ie does it always wait the ConnectionTimeout before going to the failover server? Or does the failover becoming primary mean the client will immediately switch to connecting to the failover disregarding the ConnectionTimeout?

I read the MSDN articles Database Mirroring in SQL Server and Making the Initial Connection to a Database Mirroring Session and they talked about the client automatically connecting to the mirror when the main server is down, but would having a high ConnectionTimeout specified affect the time it would take the client to connect to the failover server?

I have to set the ConnectionTimeout on all of my connection strings to at least 150 due to a known bug in ADO.NET. And we are concerned a huge timeout will affect the ability of apps to handle failovers in a timely manner

回答1:

If the primary database server is not available, then ADO.Net will immediately attempt to contact the failover partner. If the primary server is avaiable, but the requested database is in a mirrored state, the ADO.Net caller will be redirected to the principal server (Which may or may not match the connection string failover partner). Any other situation which implies availability of the primary database and server will wait for a timeout or error.

回答2:

Be wary that your timeout must be big enough to handle a tcp timeout - in other words, if your sql connection does not wait long enough, it will not wait for the tcp connection to timeout, retry,timeout,retry before deciding that the server is dead. We have to set ours to at least 60 seconds.

I wrote about this more here, https://serverfault.com/questions/249589/db-auto-failover-in-c-does-not-work-when-the-principal-server-physically-goes-o