SQL Server 2005 multiple database deployment/upgrading software suggestions

Question

We've got a product which utilizes multiple SQL Server 2005 databases with triggers. We're looking for a sustainable solution for deploying and upgrading the database schemas on customer servers.

Currently, we're using Red Gate's SQL Packager, which appears to be the wrong tool for this particular job. Not only does SQL Packager appear to be geared toward individual databases, but the particular (old) version we own has some issues with SQL Server 2005. (Our version of SQL Packager worked fine with SQL Server 2000, even though we had to do a lot of workarounds to make it handle multiple databases with triggers.)

Can someone suggest a product which can create an EXE or a .NET project to do the following things?

* Create a main database with some default data.
* Create an audit trail database. 
* Put triggers on the main database so audit data will automatically be inserted into the audit trail database.
* Create a secondary database that has nothing to do with the main database and audit trail database.

And then, when a customer needs to update their database schema, the product can look at the changes between the original set of databases and the updated set of databases on our server. Then the product can create an EXE or .NET project which can, on the customer's server...

* Temporarily drop triggers on the main database so alterations can be made.
* Alter database schemas, triggers, stored procedures, etc. on any of the original databases, while leaving the customer's data alone.
* Put the triggers back on the main database.

Basically, we're looking for a product similar to SQL Packager, but one which will handle multiple databases easily. If no such product exists, we'll have to make our own.

Thanks in advance for your suggestions!

Answer 1

I was looking for this product myself, knowing that RedGate solution worked fine for "one" DB; unfortunately I have been unable to find such tool :(

In the end, I had to roll my own solution to do something "similar". It was a pain in the… but it worked.

My scenario was way simpler than yours, as we didn't have triggers and T-SQL.

Later, I decided to take a different approach:

Every DB change had a SCRIPT. Numbered. 001_Create_Table_xXX.SQL, 002_AlterTable_whatever.SQL, etc.

No matter how small the change is, there's got to be a script. The new version of the updater does this:

1. Makes a BKP of the customerDB (just in case)
2. Starts executing scripts in Alphabetical order. (001, 002...)
3. If a script fails, it drops the BD. Logs the Script error, Script Number, etc. and restores the customer's DB.
4. If it finishes, it makes another backup of the customer's DB (after the "migration") and updates a table where we store the DB version; this table is checked by the app to make sure that the DB and the app are in sync.
5. Shows a nice success msg.

This turned out to be a little bit more "manual" but it has been really working with little effort for three years now. The secret lies in keeping a few testing DBs to test the "upgrade" before deploying. But apart from a few isolated Dbs where some scripts failed because of data inconsistency, this worked fine.

Since your scenario is a bit more complex, I don't know if this kind of approach can be ok with you.

Answer 2

As of this writing (June 2009) there's still no product on the market that'll do all this for multiple databases. I work for Quest Software, makers of Change Director for SQL Server, another database change automation system. Ours doesn't handle multiple databases like you're after, and I've seen the others out there. No dice.

I wouldn't hold out hope for it either, given the directions I've seen in SQL Server management. Things are going more toward packaged applications being contained in a single database, and most of the code is focusing on that.