Database packages and multiple databases

joteke

Hi

how does database packages work when we have multiple customer databases in single installation which need to be updated?

Does it require creating one package per target database, or is there a chance to specify multiple databases as target for a single DB package and does it (update) happen in a transaction?

We have one web application with multiple customer databases, and we need to ensure they either update in one go, or then not (rolled back in case something goes wrong)

justin.caldicott

Hi,

At the moment, the easiest thing to do is to have a package for each of the databases that you want to deploy. You can release them together when more than one has changed, but as you mentioned it's not transactional across databases.

It's a useful bit of feedback, and we'll add it to the board. How do you currently run the upgrades? Do you put the upgrade scripts for each database into one script with a begin/end transaction around it?

Thanks,

Justin

joteke

Well currently we run it against all the dbs separately...same script created with Sql Compare however...

we are just to start to evaluate DM and these were just initial questions.

Capability to specify multiple target databases for update as a list in same package would be VERY important feature (for us), we need to ensure that after the installation is done all the dbs are in same state (since web app expects it)

justin.caldicott

I see, so to you these databases are actually just one component of your software, and thus you'd like them all in the same package?

Having them in separate packages however does allow the ability to update just a single database, and around an updated packge for just one database. Is this something you would need?

joteke

From schema standpoint there can't be differences in the customer databases of the same site installation. Of course in static data such as settings, there can and most likely is (customer-specific settings) and taht's fine.

Single package or multiple packages, is nuance (except that a installation can have tens of dbs so copying the db packages probably is a tedious task), point is that we need to ensure the upgrade goes smoothly at the installation level (web site plus all databases). If one DB is left behind for some reason, that leads to availability and such issues, and we are in hurry next morning to fix it to the same level with other customer dbs.

That's why we'd need entire site update which happens transactively from web package and one (or multiple) database package point of view. If one of them fails, whole update should be rolled back.

justin.caldicott

Great, thanks for the feedback!

dhtucker

Like joteke, I need the same database package deployed to multiple target databases, only instead of tens of databases I'm dealing with thousands of databases (hundreds per server, eight production servers).

Presently I'm using SQL Compare to generate a change script, then running it through SQL Multi Script to execute that script against each target database. This is an error-prone process - the MultiScript target lists must be edited by hand prior to each deployment, and if a target database is out of schema alignment there's no "update" resynchronization available.

(BTW: This suggestion has already been registered on the UserVoice site - please vote it up!)

justin.caldicott

Hi Doug,

Do you typically deploy these databases all at once, or are there different release schedules for different groups?