Server down redirect

Hey folks,
  I'm using SQL Database as my datasource.  What is the best method to look at a different server if server one is down.

I need to do this in VB code behind inthe Master file I guess.
Larry Bristersr. DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
The best solution is a SQL Server cluster so that your app doesn't ahve to deal with this.  But ... doing this in the code-behind shouldn't be too hard -- as long as you're just trying to read.

Somewhere ... maybe actually in a page from which your content pages inherit ... you could put a Connection property.

This property would:
* Instantiate a SqlConnection, if not already done
* Attempt to connect to the primary DB server
* If that fails, attempt to connect to the second DB server
* Return the connection

Think that will work for you?

Larry Bristersr. DeveloperAuthor Commented:
Yes...that would work.  You have an example?
Daniel WilsonCommented:
Try something like this:

you will need a module-level
Private _GoodCon as SqlConnection

Public ReadOnly Property GoodConnection() as SqlConnection
   if _GoodCon is nothing then
     set _GoodCon = new SqlConnection(ConnString1)
   end if
   if not TestCon(_GoodCon) then
     if _GoodCon.ConnectionString = ConnString1 then 
        _GoodCon.ConnectionString = ConnString2
        _GoodCon.ConnectionString = ConnString1
     if not TestCon(_GoodCon) then
       throw new exception ("Can't connect to either server!!!")
     end if
   end if
   return _GoodCon
 End Get
End Property
Private Function TestCon(C as SqlConnection) as Boolean
  dim cmd as new SqlCommand("Select 1", C)
   if C.State = Closed then
   end if
  catch ex as exception
   return false
  end try
  return true
end function

Open in new window


Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
Thanks.  This put me on the track I needed.
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.