Managing database updates

I've been thinking of ways to improve managing changes to our database structure. I have a build server that creates nightly builds, so I was thinking we could somehow create database dumps, backups, and scripts from the test environment as part of the build process. Then when deploying an update to the client we could use a tool like DBDiff to create the database update script.

Is anybody doing something similar? Is it even a good idea? Maybe 开发者_如何学JAVAsome good tips what to use to create these dumps on build server?

Rather than identifying the differences, I recommend to keep a proper script that creates a database from scratch.

We are quite satisfied with using Liquibase to manage all DB migration in our projects. It knows which "patches" have been applied and ensures that only those that are missing will be applied to the target database.

this is possible.

the differencing is the hard part. once you identify the differences, you need to construct the appropriate sql, then apply it. you can either apply it directly, or create some script that you can run after review.

when both sides change, then you need to decide if the target system should keep its change or if that should be completely removed.

remember that when the target system changes also include data, and if you remove some table or column, then your referential integrity might be completely ruined.

one more thought. you will need access to the target system in order to determine the diff. if this is a generic utility, you will need to make it an executable after the fact, not part of the build.

You will find the Visual Database Tools very useful here.

http://msdn.microsoft.com/en-us/library/y5a4ezk9.aspx

There is a schema compare built right into Visual Studio (it can also be run from the command line). There is also a database project that contains a complete set of scripts for the database and the objects that it contains. This can be checked into source control along with your source code.

You can deploy a new database based on these scripts with a context menu click.

Have a look at http://www.codeproject.com/KB/architecture/Database_CI.aspx and http://www.martinfowler.com/articles/evodb.html - there's a fair amount of thinking that's already available.

We are currently looking at the Juneau CTP release, SQL Tools for Visual Studio. It has a snapshot and schema comparison feature. Basically, it can auto-generate scripts between two schemas for you. If you use this against two versions of your database, it will give you an upgrade script.

http://msdn.microsoft.com/en-us/data/gg427686

Here at Red Gate we're close to releasing a solution which solves that precise issue using SQL Source Control and SQL Compare. We have an early access program which will allow you to try this out. Please visit the following link for sign-up details.

http://www.red-gate.com/MessageBoard/viewtopic.php?p=46951#46951