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?



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

you will need a module-level
Private _GoodCon as SqlConnection

Public ReadOnly Property GoodConnection() as SqlConnection
 Get
   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
     else
        _GoodCon.ConnectionString = ConnString1
     end
     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)
  try
   if C.State = Closed then
     cmd.Connection.Open();
   end if
   cmd.ExecuteScalar();
  catch ex as exception
   return false
  end try
  return true
end function

Open in new window

0

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.
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.