anaging 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 graphical user interface (GUI)-based tool 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.

The few developers who use a SQL editor to hand code every database object change can use the resulting scripts to make any future database migrations—if (and it is a very big if) in these shared common scripts they save every change in the correct order. When using this process, even one omitted statement can cause chaos.

I believe that using an automated migration tool for database structures is a superior solution to both using the standard GUI-based tools, a process based on paper lists and manual changes, and storing hand-coded statements.

This article details the challenges of pushing database changes from the development database to the testing, staging, and live databases that are used during the development process. It also explains why using an automated tool is the best way to deal with these challenges.

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 onto the next stage, the accompanying database structure needs to be migrated (from a staging database to a live database, for example). Organizations can make this migration in the following three ways:

1. Delete the target database and replace it with the new one. The advantage of this approach is that it is simple. The major drawback is that any data in the replaced database will be lost. This could be a disaster in a live database, but even a test database contains lots of data for the required range of test scenarios, which would be tedious and time-consuming to replace.
2. Using SQL Enterprise Manager (or a similar program) to migrate changes from the old database to the new one. In this scenario, a developer or DBA manually adds and modifies database objects via a tool such as SQL Enterprise Manager's user interface. The major problem with this approach is that it is not a documented, repeatable process.
3. Create a script to convert one database's structure to match that of another. This involves creating batches of SQL statements. These statements contain commands to make the database schema of the live database the same as that of the staging database.