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

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.