Geek asks and answers

Automatically switch to another resource with SQL mirroring and connection strings

I have 3 servers configured to mirror SQL and automatically fail over using a witness server. This works as expected.

Now my application, which connects to the database, seems to have problems when a failure occurs - I need to manually intervene and change the connection strings so that it connects again. The best solution I have found so far includes using the Failover Partnerconnection string parameter , however it is neither intuitive nor complete: Data Source="Mirror";Failover Partner="Principal" found here .

From the example in the blog above (scenario No. 3), when the first transition to another resource occurs, and the main (fault tolerance partner) is unavailable, the data source (which is the new main) is used instead. If it does not work again (and I only tried for a limited period of time), then an error message appears. This is due to the fact that the connection string is cached, so until the update is updated, it will exit with an error (it seems that the connection string is updated 5 minutes after it encounters an error). If, after switching to another resource, I replaced the data source and the fault tolerance partner, I will again have another mode of silence.

Is there a way to fully automate the transition to another resource for applications that use mirrored databases (without seeing errors)?

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

+3
source share
3 answers

, . , ; -)

  // 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();

, SQL Server, "" .

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

+1

The solution is to disable connection pooling Pooling="false"

Although this has minimal impact on small applications, I have not tested it with applications that receive hundreds of requests per minute (or more) and are not sure what the consequences are. Anyone want to comment?

0
source

More articles:


All Articles