Managing SQL Server database structure migrations using GUI and automation tools

This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

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:

Figure 1: A typical manual script collation process for managing database changes.  A group of developers make changes to the development database. Those changes are then collated, and a script is manually generated to synchronize the databases. This is then followed by a lengthy debugging process.

1. Manually migrate the database by hand-coding SQL statements into a SQL migration script (as shown in Figure 1), 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.

   

   Figure 2: A low overhead process for comparing and synchronizing SQL databases using SQL Compare. Developers make changes and the development and live databases are compared and synchronized without any manual script generation.
   
    

2. Using SQL Compare to create a script to convert one database's structure to match that of another (see figure 2). 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 (SQL Toolkit)to automate the management of your SQL database changes. This involves programmatically 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 (see figure 3).

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

Figure 3: An automated solution for migrating databases. Write a SQL Toolkit application, which includes SQL Compare APIs, to set up comparisons and synchronizations to occur at times that you choose, and save snapshots and scripts in SourceSafe (or similar) for future use.

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

• Integrate with SourceSafe.
• Common script for all database migrations.
• 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.

This is only a very brief overview of what SQL Compare and the SQL Toolkit can do.  Visit Red Gate’s website for further information, reviews, testimonials, case studies, and a fully functional, free trial.