Creating a Stable Process for Managing Database Changes
Wednesday, June 30, 2004
Posted by: Kevin Jetton
Microsoft SQL Server is now thought to be Microsoft's third most important product after Windows and Office. Microsoft SQL Server's phenomenal success is being reflected in industry estimates of SQL Server's use in the developer community; current surveys show that 70-80 percent of software developers who use Microsoft technologies are also using SQL Server as a data store.
The trend of using a standardized approach for storing and managing data is lowering costs and increasing productivity not only for the development phase but also for the post-production management of the data. Key elements of this approach are the third-party tools that automate the tasks of developers and DBAs in their SQL Server database tasks.
Red Gate Software is the leading supplier of tools that help developers and DBAs manage the changes in database structures and data. This article explains how Red Gate's tools fit into the workflow of developers and DBAs who use Microsoft SQL Server.
Dealing with Database Changes
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 used to create 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 Red Gate's 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 okay.
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 machine dies?
3. Using the SQL Comparison and Synchronization 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.
This is the ultimate solution but it does require more programming expertise and has a slightly higher cost.
Solutions 1 and 2 are reasonably well known, but the third solution is worth examining in more detail. Projects that use the Toolkit often have the following design goals:
- Integrate with a source control system
- 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.
If you would like to review one of Red Gate's products and/or present it to a user group meeting, send details to Kathryn Lye at firstname.lastname@example.org.