>
> Only if we try to create foreing keys... In those cases we aways

have to

> take everyone off. But this is the only case so far. We always

perform DDL

> changes with database active (as far as not droping anything, of course)

Yes, our data model is all linked together in one way or another. We
don't have independent sets of tables within the same database. I have
never modified our existing database and introduced a set of tables
where there is not at least one foreign key constraint to pre-existing
tables involved. Our security module practically guarantees this.

We basically concluded that while some minor updates could potentially
be done online, it was not worth the effort for us to identify which
ones would need exclusivity.

We wrote a Windows application that

1. Extracts .sql files to a temp folder
2. Attempts to rename the database file (will fail if users connected,
and abort).
3. Makes a file copy of the database file (safe because it is renamed)
4. Connect to renamed database and identify latest installed patch
5. Generate bunch of in statements to run .sql files to a determined
level, outputting to a file.
6. Run iSQL with the file we generated in 5. Output sent to box in the
app, so if DBA notice any errors, they can rollback the upgrade (uses
database copied at 3). Otherwise, renames upgraded database back to
original name.


This allows for minimal downtime. Most upgrades can be measured in
seconds (unless the .sql scripts are doing a lot of work). This can
happen in parallel with upgrading binaries, and usually the database
upgrade is faster than putting the latest binaries in the appropriate
folder (requires the user to select the base path).

Adam