I have multilple web servers running classic asp and
asp.net applications that all connect to a single MS
SQL2000 database server with multiple databases via SQL db
connection strings defined their global.asa or web.config
files.

If I need to restart the SQL server or take it offline for
maintenance all the web applications fail because they do
not have access to their data.

I am familiar with SQL replication but do not know if
there is a way to automatically have the web applications
switch from one SQL Server to another if the primary
server is offline.

Any insight or help would be greatly appreciated.

Mike

There are a couple of different options:

1) Have a HTML page you can substiute for your main web page saying "down
for maintenance" when SQL Server is shut-down, for low-priority web sites,
a simple, cheap solution until your database server is back up and running.

2) Use database replication technology (Microsoft solution or 3rd party
product like Double-Take http://www.nsisoftware.com/pro/doubletake) then
modify the global.asa or web.config to the standby SQL Server as needed.

3) Use cluster technologies allow SQL Server to automatically restart on a
backup node, should one node of a cluster have a hardware failure. The web
apps connect to a virtual SQL Server name or IP. That name and IP are
automatically reassigned to the new node. No change in the connection
string is required.

Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.

This posting is provided "as is" with no warranties and confers no rights.

Thank you for your prompt and helpful answer Chris. I am
leaning towards Clustering which seems like the best
overall solution since modifying dozens of global.asa and
web.config files could be time consuming and cumbersome.

Can your recommend any resources on the Microsoft or other
sites than would discuss the hardware/software needs for a
small 2 node cluster. We do not need terabytes of storage
and of course the cost is of great concern.

Again thank you for your insight on this.

Mike