An Automated Solution for Migrating Database Structures : Page 3
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.
by Simon Galbraith
Apr 11, 2003
Page 3 of 3
Automation Makes Life Easy
A good automated tool features all of the following functions:
Gives you visual indications of differences in database objects. You need to be able to see, at a glance, how your databases differ before you migrate changes. Figure 2 shows the details that SQL Compare displays of the differences between two tables storing information for an ASP application.
Figure 2: SQL Compare Displays Details of the Differences Between Two Tables
Creates migration scripts for all database objects. You will need to migrate more than just tables, stored procedures, and views. If the permissions on objects differ or tables have different triggers, application errors could result.
Creates scripts in the correct order, taking dependencies and foreign keys into account. Database objects need to be dropped, altered, and created in the correct order or database updates will fail. If you alter the data type of a column that is part of an index, then you will need to drop and recreate the index. Figure 3 shows some of the script that SQL Compare automatically generates to make the changes shown in Figure 2.
Enables you to define the criteria for differing database objects. You might deem white space in stored procedure definitions as insignificant, or choose to ignore the collation settings of character columns. Fill factors on indexes, the names of constraints, or the order of columns in tables might not be important to you. The tool should allow you to easily define all of these parameters.
A Life-changing Solution
Trawling through database schema with a pen and paper, spending weeks debugging incomplete, hand-written SQL scripts, and working late nights tracking down irreproducible application errors just days before product deadlines is not something that any developer or DBA likes doing. Nor is there any need for it. An automated cure can be life changing for database application developers and DBAs.