Best practices for managing migrations that update several databases?

Question

My team is evaluating tools and processes for managing database migrations/database refactoring as described by Martin Fowler, Pramod Sadalage, et. al. We're interested in automated, repeatable, testable processes, so we're not interested in techniques like manually running SQL Compare every time we deploy. We're currently using CruiseControl.NET for continuous integration.

Our production environment has multiple SQL Server 2000 database servers with replication between them. Our migrations will thus making changes to the schema on both the source and target database server.

To perform such a migration with a tool such as dbdeploy, it seems we would need to run the migration against one of the servers, and we would have to add the other servers as linked servers. A single script running against the main server could thus execute DDL against any of the linked servers.

My question is this: would this approach be considered a best practice, or is there a better technique for applying migrations that touch multiple database servers?

Answer 1

I don't see anything inherently wrong with this approach, however when setting it up be sure to test what happens when one of the linked servers is down. You don't want to be rolling back all the other servers if one happens to be down and you do want to know the changes were not applied to that server.

Of course the first, most important best practice for any migration is making sure you have a solid backup process in place before you start migrating changes.

Answer 2

Visual Studio 2008 (Team Edition , specifically GDR) can handle automated deployment of schema against defined schema/metadata file which you can deploy to servers. This could be included in your build /deploy process. However, I think there is still an issue over replication and schema changes - there is no package that understands/is aware of your replication setup.

For instance, we use custom replication procedures on the subscriber and although schema changes propagate from the publisher we sometimes have to manually script changes depending on any custom replication we have in place. If you do not use custom replication procs, I'd say this was the way to go.

Answer 3

You can try out a combination of Chinchillin and Wizardby: install Chinchillin agents on DB servers and have it execute Wizardby migration scripts during your deployment process.

This integration is in the works, though :)