Automatic failover with SQL mirroring and connection strings

Question

I have 3 servers set up for SQL mirroring and automatic failover using a witness server. This works as expected.

Now my application that connects to the database, seems to have a problem when a failover occurs - I need to manually intervene and change connection strings for it to connect again. The best solution I've found so far involves using Failover Partner parameter of the connection string, however it's neither intuitive nor complete: Data Source="Mirror";Failover Partner="Principal" found here.

From the example in the blog above (scenario #3) when the first failover occurs, and principal (failover partner) is unavailable, data source is used instead (which is the new principal). If it fails again (and I only tried within a limited period), it then comes up with an error message. This happens because the connection string is cached, so until this is refreshed, it will keep coming out with an error (it seems connection string refreshes ~5 mins after it encounters an error). If after failover I swap data source and failover partner, I will have one more silent failover again.

Is there a way to achieve fully automatic failover for applications that use mirroring databases too (without ever seeing the error)?

I can see potential workarounds using custom scripts that would poll currently active database node name and adjust connection string accordingly, however it seems like an overkill at the moment.

Solution 1

Read the blog post here http://blogs.msdn.com/b/spike/archive/2010/12/15/running-a-database-mirror-setup-with-the-sqlbrowser-service-off-may-produce-unexpected-results.aspx

It explains what is happening, the failover partner is actually being read from the sql server not from your config. Run the query in that post to find out what is actually being used as the failover server. It will probably be a machine name that is not discoverable from where your client is running.

Solution 2

You can clear the application pool in the case a failover has happened. Not very nice I know ;-)

  // ClearAllPools resets (or empties) the connection pool. 
  // If there are connections in use at the time of the call, 
  // they are marked appropriately and will be discarded 
  // (instead of being returned to the pool) when Close is called on them.
  System.Data.SqlClient.SqlConnection.ClearAllPools();

We use it when we change an underlying server via SQL Server alias, to enforce a "refresh" of the server name.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearallpools.aspx