Browse DevX
Sign up for e-mail newsletters from DevX


An Automated Solution for Migrating Database Structures : Page 2

Migrating database changes from the development database to the testing, staging, and live databases poses plenty of challenges. Learn why using an automated tool is the best way to deal with these challenges.

Creating Migration Scripts
Of these three options, creating a script is clearly the most acceptable. It allows changes to be pushed from a single source database to multiple target databases without any data loss. What's more, by documenting the changes between database versions, the process provides an audit trail that developers can track and reproduce.

Following this process, however, poses a number of problems. First, the organization must determine when to create the migration scripts. The scripts can be created either as each database object is modified throughout the development lifecycle or just prior to the actual migration, after database development is complete.

The first option relies on developers and DBAs remembering to create accurate, debugged batches of SQL statements whenever they change a database object, and then somebody collating all the changes for the ultimate migration. Unfortunately, in the real world this does not happen, so creating the scripts at the end of development is often the easiest and best practice.

The second problem is making sure the scripts are complete. If not all of the schema changes are pushed from one database to the other, the resulting application errors will be hard to find, reproduce, and fix. If the migration scripts are inaccurate, an application might work fine on the developer's system but not on the systems that the QA team or customers use. The "it works fine on my computer" excuse will kick in and recriminations will follow.

With a large database and a complex application, tracking down issues caused by mismatched database schemas can take weeks. Since these problems are often not detected until very late in the software development lifecycle, they are especially frustrating and hard to manage and fix.

The third challenge is the sheer complexity of spotting differences in databases that might contain thousands—or even tens of thousands—of tables, stored procedures, views, and other database objects. If you've ever tried to track down differences in database schemas using printouts, a pen, and lots of paper, you no doubt recognize the potential value of an automated tool that excels at that task. Table 1 presents a list of such products for migrating database structures.

Table 1. Products for Migrating Database Structures
Product Name Vendor Database Systems Supported Migration Features
SQL Compare Red Gate Software SQL Server Compares database schemas; synchronizes database schemas; easy to use; GUI
Schema Manager Quest Software Oracle and DB2 Compares database schemas; synchronizes database schemas; documentation features; scheduling features
Change Manager Embarcadero Technologies SQL Server, Oracle, DB2, and Sybase Compares database schemas; synchronizes database schemas
AllFusion ERwin Data Modeler Computer Associates SQL Server, Oracle, and DB2 Compares database schemas; synchronizes database schemas; very large feature set for database management

For example, Figure 1 shows how Red Gate Software's SQL Compare, an automated tool that can compare database structures, displays a comparative list of differing database objects.

Click to enlarge
Figure 1: SQL Compare Displays a Comparative List of Differing Database Objects

Thanks for your registration, follow us on our social networks to keep up-to-date