dilettante
MIS
I have a web application that is backed by a Jet database (MDB).
This is meant for administration by unsophisticated users who will not have direct access to the server itself. As a result, I want to provide basic admin functions via web pages.
Many application items are of course stored in the database, and most of these can be "deleted" by marking a Deleted field True. These can be undeleted as well, providing a one-level "undo" to the users.
Users designated as administrators or managers of the site will be able to go to an admin page and request removal of items marked deleted. Options like "remove before today," "remove one week old," "remove one month old," and "remove all" deleted items. That part is easy and can probably be done online.
I also want them to be able to invoke compact and repair, create database backups, and restore a backup. This is where the fun starts.
Because of the nature of this application, the database is repeatedly opened for brief intervals. In this case there are no pooled connections holding the database open. Just the same, it's important to "lock" the database to prevent access while maintenance functions proceed. Here is what I plan to do:
Compact & repair
[ul][li]Initiate a .BAT file or WSH script. The script will perform the rest of the process.[/li]
[li]Create a file named [tt]lock[/tt].
Web hits will check for the presence of [tt]lock[/tt] and if found will not open the database, but report the site "undergoing maintenance since <lockfile creation>."[/li]
[li]Script will pause until the database is exclusively available.[/li]
[li]Run [tt]jetcomp.exe[/tt] with command-line params to compact the database as a new database.[/li]
[li]If successful, copy the new database over the old.[/li]
[li]Remove the new database file.[/li]
[li]Log completion in a text logfile.[/li]
[li]Remove the [tt]lock[/tt] file.[/li][/ul]
Back up database
Same process, but without removing the new database. The backups can be stored offline or removed later through a scheduled process.
Restore database
[ul][li]Initiate a .BAT file or WSH script.[/li]
[li]Create [tt]lock[/tt] file.[/li]
[li]Script will pause until the database is exclusively available.[/li]
[li]Copy the current database as replaced.[/li]
[li]Copy the new database over the current one.[/li]
[li]Log completion in a text logfile.[/li]
[li]Remove the [tt]lock[/tt] file.[/li][/ul]
If the wheels come off it should be easy to determine why. The locked status will continue to be reported and in most cases I'd think merely removing the [tt]lock[/tt] file will get the site back up with the current database.
My questions are:
Is this excessively "clunky?" Is there a preferred approach I'm missing?
Are there any nasty holes I don't see here?
Does [tt]jetcomp[/tt] report useful messages in command-line mode when problems occur? Ones worth capturing and logging?
Are there other database maintenance functions I ought to be providing?
This is meant for administration by unsophisticated users who will not have direct access to the server itself. As a result, I want to provide basic admin functions via web pages.
Many application items are of course stored in the database, and most of these can be "deleted" by marking a Deleted field True. These can be undeleted as well, providing a one-level "undo" to the users.
Users designated as administrators or managers of the site will be able to go to an admin page and request removal of items marked deleted. Options like "remove before today," "remove one week old," "remove one month old," and "remove all" deleted items. That part is easy and can probably be done online.
I also want them to be able to invoke compact and repair, create database backups, and restore a backup. This is where the fun starts.
Because of the nature of this application, the database is repeatedly opened for brief intervals. In this case there are no pooled connections holding the database open. Just the same, it's important to "lock" the database to prevent access while maintenance functions proceed. Here is what I plan to do:
Compact & repair
[ul][li]Initiate a .BAT file or WSH script. The script will perform the rest of the process.[/li]
[li]Create a file named [tt]lock[/tt].
Web hits will check for the presence of [tt]lock[/tt] and if found will not open the database, but report the site "undergoing maintenance since <lockfile creation>."[/li]
[li]Script will pause until the database is exclusively available.[/li]
[li]Run [tt]jetcomp.exe[/tt] with command-line params to compact the database as a new database.[/li]
[li]If successful, copy the new database over the old.[/li]
[li]Remove the new database file.[/li]
[li]Log completion in a text logfile.[/li]
[li]Remove the [tt]lock[/tt] file.[/li][/ul]
Back up database
Same process, but without removing the new database. The backups can be stored offline or removed later through a scheduled process.
Restore database
[ul][li]Initiate a .BAT file or WSH script.[/li]
[li]Create [tt]lock[/tt] file.[/li]
[li]Script will pause until the database is exclusively available.[/li]
[li]Copy the current database as replaced.[/li]
[li]Copy the new database over the current one.[/li]
[li]Log completion in a text logfile.[/li]
[li]Remove the [tt]lock[/tt] file.[/li][/ul]
If the wheels come off it should be easy to determine why. The locked status will continue to be reported and in most cases I'd think merely removing the [tt]lock[/tt] file will get the site back up with the current database.
My questions are:
Is this excessively "clunky?" Is there a preferred approach I'm missing?
Are there any nasty holes I don't see here?
Does [tt]jetcomp[/tt] report useful messages in command-line mode when problems occur? Ones worth capturing and logging?
Are there other database maintenance functions I ought to be providing?
