Changing SQL connection strings

[dzoltok]

OK, here's the problem:

Our local intranet has a bunch of web applications that access data on a server (running MSSQL 2K). We're currently creating another synchronous server so that if one goes down, we can switch to the other without losing any data. What we'd like to do is have the local websites automatically redirect to one server if the other one isn't available (crashed, down for maintenance, etc.) when accessing the data. The connection string used to access this server is stored in the webapp.

The question is, is there any way to alter the connection strings in the pages without going through every page and changing it manually? Is there a way to check whether a server is available before it attempts to connect? Any help would be appreciated.

 

[Nagrom]

Make the connection string an Application level variable. Then you can change it once and every page can just access that updated variable. Decide what is the most efficient level for creating the connection object (Page, Session, or Application) and build your code to use it at that level. It's hard to give good advice here without more info on your app. Typically, I create it at a Session level since that is usually the most efficient level without worrying about multiple simultaneous users having to share it (of course, that requires cookies to be enabled, so you may choose not to do that yourself). Rather than checking the connection status every time, just enable error handling so you only have to worry about switching connection strings if an error actually occurs while attempting to use the connection object.

Try it like this - at the Application level have 2 constants: strConnectionString1, strConnectionString2. At the Session level have 1 variable: strConnectionString. In the Session_OnStart set strConnectionString to strConnectionString1. Create the connection object at the session level using strConnectionString to connect to the database. If there is an error using the connection object, check to see if strConnectionString = strConnectionString1. If it does, set it to strConnectionString2 (and vice versa). Close your connection object and re-open the connection using the alternate connection string. Sometimes connection objects go a little nuts when they have errors, so you might actually have to recreate the object (rather than just close and reopen it). I just thought I'd throw in that caveat.

 

[dzoltok]

Hmmm... yeah, actually that does help. It sounds like that will let me assign an alternate connection string in the event of an error with the first one, and that's exactly what I need. Minor questions, then:

1) Would I set that up in global.asa (under Session_OnStart, for example)?

2) How frequently do Application_OnStart and Application_OnEnd code run? Would it be every time the start page is opened, or just the first time while the client's computer is on? (i.e. if someone visits the site, then closes the browser, then visits again, would the code run a second time?)

Thanks for the help!

 

[Nagrom]

You would initially create the connection object in Session_OnStart, but you would perform the error checking in each page where you actually use the connection.

The Application_OnStart event fires when someone starts using the web application and no one else is already in it. Application_OnEnd fires when everyone who is in the web application exits the application (which paves the way for Application_OnStart to fire when someone goes into it again).

Hope this helps!

 

[gsc1ugs]

Wrap somthing like this on each...connection

On Error Resume Next
//do create stuff here
if Err<>0 then
strErr = strErr & &quot;<br>Error is <b>&quot; & Err.description & &quot;
Else
// go for it.
End if Regards gsc1ugs
&quot;Cant see wood for tree's...!&quot;