Managing the design and creation of database objects such as tables, stored procedures, and views is a fact of life for database developers. Because applications grow and change continually, additions and changes to database structures (or schemas) are a constant part of the development process.

Developers generally create the requisite database objects using a tool, based on a graphical user interface (GUI), such as Microsoft's SQL Enterprise Manager for MS SQL Server. GUI-based tools offer a powerful, rapid way to create database objects because they automate the details of the work, allowing the developer to focus on improving performance. However, they also hide the SQL commands they use to create the new objects from the developer, which can cause problems when a developer needs to migrate structures between different databases.

Database structure migration between development stages

Many organizations use a range of databases for different stages of their development processes. They might have a development database, a test database, a staging database, and a live production database. Once an iteration of the application is finished and moved on to the next stage, the accompanying database structure needs to be migrated (from a staging database to a live database, for example). Organizations generally use one of the following three processes if they are using SQL Server as their database:

1. Manually migrate the database by hand-coding SQL statements into a SQL migration script, or by using SQL Enterprise Manager for each individual change, to migrate changes from the old database to the new one. The SQL change script, or list of changes, is generally produced collaboratively by all developers, using a common file in SourceSafe, with the DBA having ultimate responsibility for checking and making the changes.

The major problem with this approach is that it is not a documented, repeatable process. One single mistake, made by one developer, can cause chaos. This drawback can be mitigated by using Red Gate's SQL Compare^® to verify that the two databases do, indeed, share the same structure after a migration has been attempted.

2. Using SQL Compare to create a script to convert one database's structure to match that of another. This involves using SQL Compare to visualize the differences between the two databases and automatically create the SQL script to make the change. This is typically done by the DBA, but often developers have their own copy for making sure that it is all OK.

This is a great, very low-cost, low-overhead solution to the problem, but it can have implications if you want tight control of managing the process. Who keeps a record of the changes just made? What happens if your DBA's machine dies?

3. Using the SQL Comparison and Synchronization Toolkit to automate the management of your SQL database changes. This involves programatically using the APIs that provide the functionality of SQL Compare to create a tight, automated process. Once the automation is complete, the changes can be run on a daily basis, with a record made in SourceSafe.

This is the ultimate solution but it does require more programming expertise and has a slightly higher cost.

Solutions 1 and 2 are well known, but the third solution is worth examining in more detail:

Projects that use the Toolkit often have the following design goals:

1. Integrate with SourceSafe.
2. Common script for all database migrations.
3. Scheduled to run at certain times and/or integrate with build processes.

Integrating with SourceSafe is generally done via SQL Compare's snapshot file, which provides a complete description of the database schema at a given time, but also allows you to compare any future databases with how it was on that date. The generated scripts are also automatically stored in SourceSafe and tested independently.

Scheduling is set up using a command line tool of which there is a good example within SQL Compare. Comparisons can then be run on a regular basis – particularly when a back-up server is being used to protect development efforts, or when large numbers of changes are being logged.

Please contact us if you have any questions or would like to discuss how the SQL Toolkit can work for you.