Coordinate changes between development and production databases

The Case

You continue to develop your product and for this purpose it is necessary to change a database. When the next development cycle finished it is required to update a production database accordantly.

Besides, usually the database modifications are carried out by various teams and various people (DBA, programmer, project manager etc.) and this is why there is no guarantee, that SQL scripts, will be correctly saved and, not less important, saved in correct order. You cannot simply replace a production database with a copy of modified development database as in this case some live data of production database can be lost.

Alternatively, developers could manually write a script for each database change. The scripts would be collated to produce a migration script, which would be used to update 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.

Main goals to be achieved

• Accuracy and precision of changes which have been made in a development database.
• Significant reduction of human resources time consuming.
• Speeding up development.
• Creating and maintaining an audit trail of updates.
• Excluding situations of kind ‘I thought, I forgot’ that sometimes occurs when databases are different.

Our solution is

When development has finished, use Cross-Database Studio 6.0 to compare development and production databases and generate synchronization SQL scripts to migrate your changes:

1. Compare development and production database with CDBS product in order to display or generate detailed report about differences between two databases. The report optionally will include the differences in tables, table structure, primary key, indexes, foreign keys, data etc.
2. Analyze received reports, choose database objects you need to migrate and generate synchronization SQL scripts. You can create SQL scripts on indivudual schemas objects only(at level single cell value even) or to produce migration SQL script for all objects.
3. Save SQL script and edit it, if required.
4. Run synchronization script on the copy of production database to migrate the changes.
5. You may compare two databases again to reconcile the changes.
6. When you satisfied with migration results, run synchronization SQL script on production database and if required compare databases to reconcile the changes.

Advantage 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.