If the connection to one of my servers fails in my web,config file, how I do reroute to use the other server using two connection strings?

I have two connection strings in my VS 2005 web.config file. I want to always use the first connection string UNLESS that server is down (aka: a connection cannot be made to the first connection string).. If this happens, I want to automatically redirect all web site traffic to the back up server, which is the second connection string. How do I code this bypass/failsafe into my web.config file?
<add name="Connection1" connectionString="Data Source=111.111.111.111;Initial Catalog=Catalog1;User ID=xxxx;Password=xxxx; MultipleActiveResultSets=True; connection timeout=360;" providerName="System.Data.SqlClient"/>
    <add name="Connection2BACKUP" connectionString="Data Source=111.222.222.222;Initial Catalog=Catalog2;User ID=xxxx;Password=xxxx; connection timeout=360;" providerName="System.Data.SqlClient"/>

Open in new window

LVL 3
utlonghornjulieAsked:
Who is Participating?
 
GiftsonDJohnConnect With a Mentor Commented:
You have to setup Database mirroring in Sql Server

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149593,00.html
http://www.sql-server-performance.com/articles/clustering/mirroring_2005_p1.aspx
http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

then you can use the connection string

<add name="Connection1" connectionString="Data Source=111.111.111.111; Failover_Partner=111.222.222.222; Initial Catalog=Catalog1;User ID=xxxx;Password=xxxx; MultipleActiveResultSets=True; connection timeout=360;" providerName="System.Data.SqlClient"/>  

Primary server: 111.111.111.111
Failover Partner: 111.222.222.222
0
 
spprivateCommented:
you have to have the connection opening part alone as a first module or event.
Do a try catch and in the catch block trap sql exception.
If that exception happens then use another connection string.
Better to hold the connection object in a global level for this approach.
But the best practise is two have the sql server as cluster server where there is a failover server
0
 
GiftsonDJohnCommented:
Hi

You can use

<add name="Connection1" connectionString="Data Source=111.111.111.111; Failover_Partner=111.222.222.222; Initial Catalog=Catalog1;User ID=xxxx;Password=xxxx; MultipleActiveResultSets=True; connection timeout=360;" providerName="System.Data.SqlClient"/>  

But the database name Catalog1 should match on both the servers. If server 111.111.111.111 is down, automatically the application uses server 111.222.222.222.
0
 
spprivateCommented:
Even to use this Failover partner you need to have the SQL Server mirror setup .You cannot give two arbitary servers
0
 
utlonghornjulieAuthor Commented:
Could you give me an example of how I would setup the SQL Server mirror in my web.config file?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.