quote:

---

Originally posted by Kristen

If you have one database then ALL clients MUST update at the same time. That's not necessarily a bad thing ... but if something goes wrong ALL clients will be hammering on your door wanting a fix!

Your worry about rolling out upgrades to multiple databases is probably a bit unfounded; but you do have to tool up for it. Hopefully you are planning to have a multi-step release procedure - so you start on DEV, then apply the changes to TEST for some manually user-testing, then apply to QA for a formal set of tests, then apply to STAGING (a recent copy of the PRODUCTION database, so you can rehearse the rollout and make sure nothing goofy happens) ... and then finally release to the PRODUCTION database.

So you want a single (or, at most, "very few") script that you run that makes all the database DDL changes, any data twiddling DDL changes, and updates all Sprocs etc.

Once you have that script (for your testing and rollout procedure) its a small step to be able to run that on any number of Client databases)

What we do is as follows:

EVERY (and I mean absolutely every single one, absolutely no exceptions) change to the database structure is scripted; we save each script in a sequentially numbered filename system. Ditto with any data-twiddling scripts - e.g. where you change the Structure and also need to change the nuance of the data to match.

We store every SProc and Trigger in its own file. We NEVER (not ever, absolutely without fail) just change an Sproc in the database (e.g. by using RightClick - Modify). All those source-code files are stored in a Revision Control System (we use SVN and Tortoise, they are free)

To create a rollout-script for the new version we concatenate all the DDL scripts (since the last rollout) into a single "Upgrade" script, and we concatenate all the Sproc and Trigger scripts, etc., that have a MODIFY date newer than last time into a second script (i.e. we can just pull all the ones that have changed out of the Revision Control System).

Then we try running that "upgrade script(s)" on a restored copy of the TEST system and see what breaks. Rinse and Repeat until its OK, then we push that out to QA system and so on ...

Rolling that out to N-client databases is just a question of running the scripts. Whether you do that manually, or automatically, is up to you - and perhaps depends a bit on what the length of downtime is and the complexity of the updates (whether a human watching the process is more likely to spot issues than it just "running blind").

We use a bug-tracking system called FogBugz (I'd recommend that if you don't current use anything; up to 2 developers is free if you want to make a trial). They have one database per Client, and they have several upgrades a year. I've known them use two systems (that I can remember). One was that when you tried to log in (after some cutoff date) you got a message that your database was being upgraded, and to try again in N minutes. That probably works well if numerous of your client-systems have become dormant [as I suspect is the case with Fogbugz]. Other times I get an email to tell me that there will be scheduled maintenance on XXX at YYY. You could probably let client's choose an upgrade slot (using your application ) and have the APP Insert that "request" into a table of "Upgrade Dates & Times". (Obviously you need some sort of backups and a route by which the APP can be reversed back to the previously version if it all goes wrong)

With one-database-per-client you can move client database to different machines as a machine becomes overloaded. That's harder to do with multiple-clients-in-one-database. You could decide that you might have 50 clients per physical database, but if 10 of those clients get particularly busy you may need to split individual clients out to less busy machines ... and if all 50 have minimal activity [at the moment ...] that server is underloaded

---