Best way to temporarily take app offline during sql agent job

Question

I've got an ASP.NET app that runs against a SQL Server 2005 database. Once a day, I need to run a series of procs & SSIS packages in a SQL Agent job to update data in the tables. The SQL Agent job will run on a schedule unattended.

There are small windows of opportunity for the web app to fail when the SQL Agent Job steps are running, so I want to temporarily disable the app completely, run the sensitive steps, then enable the app again.

I see that I could use the app_offline.htm file in the root of the web app site. To do this, I'd need to provide permissions across servers to the SQL Agent package so that it could do something like ren app_offline.tmp app_offline.htm on the web server, run the steps, then ren the htm file back to the .tmp.

The other option I was considering was to put a 'MAINTMODE' boolean in a table that's updated by the SQL Agent job and have the web app check it on every call to the db to make sure it is available. If it's not, redirect to a page that indicates the site is temporarily offline.

Are there other approaches I should consider? What's the best approach to incur minimal disruption to the users?

Thanks,

Chris

Answer 1

I'd use the 'MAINTMODE' method you proposed. That way it's only triggered if a request hits your web server. Your database servers shouldn't have to talk to your web servers for simplicity sake. Let your web servers handle the presentation let your database servers focus on the database.