MSBuild SQL database synchronization tasks (for RedGate SqlCompare and SqlDataCompare)

Phil just posted a couple of MSBuild tasks that he uses to synchronize the schema and data of two databases, using the API's for RedGate's SqlCompare (for schema) and SqlDataCompare (for data).

The next project build server I set up I will try these out for size. It would be nice to be able to synchronize the schema from a dev database to a unit testing database, and the data from a unit database to a test database to a production database. The data synch task he wrote would need to be enhanced a bit to be more selective and flexible about the tables to synchronize and how to synch them.

The project I am currently working on has issues with Unit Tests that fail because the data they depend on gets reloaded on the database, or the unit test changes the data and it becomes unrepeatable. I can see this task being useful to have a "clean" unit test database that never gets tests run against it, and using that database as the source to synch the actual unit test database to.

These tasks could help in staging deployments too - by separating the tasks into a "diff" task and a "synch" task - a build process could "compile" the output (ie. SQL script) from the diff of one database to a snapshot of the current production (or test or staging or whatever) environment database, then another task could run a test deployment of that script to the target environement that needs updating. If that is successful, the final production environment deployment should be reproducible from the compiled output.

BTW, these RedGate products, SqlCompare and SqlDataCompare (they have others, but these are the ones I use), THEY ROCK!

 

2 Comments

  • Mike,

    I use them in my Web Deployment projects to sync up any schema changes from my local box to the staging server.

    I don't use the Data sync but yeah - unit testing is a fantastic use for them.

    You could even: -

    1. Backup what's on the staging server.
    2. Overwrite with sample data
    3. Delete sample data
    4. Restore what was on the staging server.

    Cool huh.

  • I think that Team System for Database Developers has this sort of functionality built in. Also allows you to run update and create scripts as part of the build so you can enforce that the 'one true' schema is the schema uses.

    Ta.
    Steve Porter

Comments have been disabled for this content.