?
Solved

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?

Posted on 2009-04-23
5
Medium Priority
?
212 Views
Last Modified: 2013-11-26
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

0
Comment
Question by:utlonghornjulie
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:spprivate
ID: 24220840
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
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24225305
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
 
LVL 15

Expert Comment

by:spprivate
ID: 24226270
Even to use this Failover partner you need to have the SQL Server mirror setup .You cannot give two arbitary servers
0
 
LVL 3

Author Comment

by:utlonghornjulie
ID: 24226545
Could you give me an example of how I would setup the SQL Server mirror in my web.config file?
0
 
LVL 14

Accepted Solution

by:
GiftsonDJohn earned 2000 total points
ID: 24226606
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question