Coordinating database development changes

The situation

You have a development environment in which changes made to the development database schema need to be migrated to a staging database, and then a production database.

You can't simply delete the staging and production databases and replace them with a copy of the updated development database, because you would lose all of your test data in the staging database, and you would lose your live data in the production database.

Developers could update the staging database, using SQL Server Enterprise Manager as and when they make changes to the development database. However, this is time-consuming, prone to human error, and provides no audit trail. And the updates would have to be documented in some way so that they could be repeated for the production database.

Alternatively, developers could manually write a script for each database update. The scripts would be collated to produce a migration script, which would be used to update the staging database, and then the production database. However, for this to work, the scripts must be complete, accurate, and merged in the correct order. Any mistakes or omissions could cause the chaos of a malfunctioning database, arguments between test and development personnel, and problems with dependencies.

Equally, tracking down the updates at the end of the development cycle and producing a viable script at this stage can be extremely time- and labor-intensive.

Key challenges

  • Accurately capturing all the changes that have been made to the development database.
  • Preserving data on the staging and production databases.
  • Producing a repeatable update process.
  • Reducing the development time spent on database versioning.
  • Creating and maintaining an audit trail of updates.
  • Avoiding the "blame game" that sometimes arises when there are differences between database versions.

How we can solve it

When development has finished, use SQL Compare to migrate your database changes:

  1. Use SQL Compare® to compare the development database with the staging database.
    SQL Compare displays a table of results detailing the differences between the database objects in the two databases.
  2. View the results of the comparison, and create a report if required.
    You can also create "snapshots" to record the database schema prior to synchronization.
  3. Choose which database objects you want to migrate.
    You may not want to migrate all of the objects. SQL Compare allows you select individual objects to update.
  4. Use the Synchronization Wizard to generate a SQL script for the migration.
    SQL Compare generates the script so that updates are made in the correct order and dependencies are preserved.
  5. Save the script, edit it if required, then run it to migrate the changes.
    You can then compare the databases again to check the results.
  6. When you have tested the staging database, use SQL Compare to compare and synchronize the production database with the staging database.

To learn more, take a look at our worked example.

Benefits of our solution

  • Changes do not need to be captured manually, reducing development time overhead and eliminating human error.
  • Data is preserved on the staging and production databases.
  • High-quality script generation ensures accurate, repeatable results.
  • Automated process dramatically reduces developer's time investment.
  • Reporting features and saved script provide an audit trail.

Case studies

Technical articles

Online help