SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database Mirroring Connection Strings - Automatic Failover

I saw a post recently from Hugo Shebbeare that reminded me of something that I’ve seen asked often on the forums. One thing that I preach to people is that they should use their blog to show what they know, and in this case I want to do that. Also, I remind people to give credit to the inspiration, as I’ve done with the link to Hugo above.

Database mirroring has automatic failover if you have newer SQL Server clients. Those of you with Vista or Windows 7 should be fine, though XP might need an update. SQL Server 2005 started distributing a client that would handle automatic failover, and it does this through the connection string. Here’s a typical one:

Server=DBServer01;Database=Sales;Connection Timeout=30;Integrated Security=SSPI;Failover Partner=DBServer02

In this string we have our main database instance (DBServer01) and the database (Sales). Our mirror server is called DBServer02, and the database name on this server would be the same. Note that you can use IP or named instances as in DBServer01\Sales as well for the connection strings.

If the client has issues connecting to the primary, when the Connection Timeout passes, it will try to connect to the mirror server and start working there. Note that if you had a transaction in progress when the failure occurred, your application has to reconnect to the other server and resubmit the batch.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by mpalaparthi on 23 June 2011

Hello Steve,

Thanks for the post, I have the similar situation here that I am facing issues and below is the information:


I am trying to configure Sync Mirrioring (With out witness) between 2 SQL Server 2008 R2 boxes with same OS and same edition (both are default instances). If I do the manual failover (alter database <database name> set partner failover) from database side it is wotking perfectely. I have a JDBC application that is trying to connect to the database and below is the Connection string:

I am struck at the connection string and below is the scenerios that I had

Connection String                                                                                                                                                             status

1) jdbc:sqlserver://serverA;databaseName=ILSBEBS;failoverPartner=serverB\\defaultinstancename      -       working usually when no failover to server B

2) jdbc:sqlserver://serverA;databaseName=ILSBEBS;failoverPartner=serverB\\defaultinstancename      -      not working when failover to server B

3) jdbc:sqlserver://serverA\\defaultinstancename;databaseName=ILSBEBS;failoverPartner=serverB\\defaultinstancename      

-     working when failover from Server A to server B

4) jdbc:sqlserver://serverA\\defaultinstancename;databaseName=ILSBEBS;                                            failoverPartner=serverB\\defaultinstancename         -     not working when failover back to server A from B

I am really sorry but I dont have any script for this question.

If you can guide me that would be really really helpful. I am sorry for troubling you

Thanks & Reagards



Leave a Comment

Please register or log in to leave a comment.