Automatic failover with SQL mirroring and connection strings

sql-server connection-string failover mirroring
Shagglez picture Shagglez · May 15, 2012 · Viewed 15.5k times · Source

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.

Answer

bennie j picture bennie j · Jan 17, 2014

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.

Related questions

How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
What is the difference between Integrated Security = True and Integrated Security = SSPI?

I have two apps that use Integrated Security. One assigns Integrated Security = true in the connection string, and the other sets Integrated Security = SSPI. What is the difference between SSPI and true in the context of Integrated Security?

Read More
How to find SQL Server running port?

Yes I read this How to find the port for MS SQL Server 2008? no luck. telnet 1433 returns connection failed, so I must specify other port. I tried to use netstat -abn but I don't see sqlservr.exe or something similar …

Read More