SqlClient.SqlException occurs during Database Fail Over

Question

I have two Microsoft SQL 2005 databases setup in a fail over scenario. The application connection strings have the "Failover Partner" specified in the connection string.

When the currently live database fails over to the slave database, there is a small time period that a user can obtain a SqlClient.SqlException with the message "An existing connection was forcibly closed by the remote host".

Is this mainly due to the speed that the databases are failing over or is there something else that can be done to prevent these errors?

Answer 1

This is due to your application attempting to use an existing connection in the connection pool. Only after this error occurs does the application pool get cleared, and new connections use the failover box.

Answer 2

caveat: the 'failover partner' option was not available a few years ago (or at least I wasn't aware of it!) so the following solution may be outdated

we had to trap connect-lost exceptions (several different flavors), wait a few seconds, and try again. This necessitated a wrapper for all database operations to automate the wait-and-retry logic - which wasn't a lot of work and proved to be fairly convenient; the effort went into examining the error codes and exception types and making a decision table as to whether we could safely retry the operation or not.

Answer 3

You're correct: it has to do with how long it takes the databases to fail over.

The synchronous mirroring failover timeline goes something like this:

1. Primary server A shuts down and closes all connections. From this point forward, any client that tries to connect to server A will get its connection forcibly closed.
2. Primary server A makes sure its logs are completely synchronized with secondary server B.
3. Server B's SQL Service starts.
4. Server B's SQL Service begins accepting connections, and at that point the clients can connect again.

No matter how fast your servers are, there's going to be a brief (at least a second) period when connections will bomb. Like Steven Lowe said, you have to trap that exception, wait, and try again. Keep in mind that it'll be more complex if your app issues several queries in a row as part of a larger transaction - your transaction might fail.