Subscribe to this blog
Briefcase
Print
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.
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:
Situation:
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
--Sreekrishna
mpalaparthi@gmail.com