Agile Database Change Management
Database Change Management is a complicated skill most DBAs must acquire sooner or later. For that reason, many tools have been created to help migrate code change efficiently. Some tools work from maintaining meta-data of your database schema, and have the ability to generate change scripts by comparing the design to a physical database. ER Studio is a key product in this area. I have also used ERWin, Modelright, TOAD, and Visio with differing degrees of success.
The problem is that the database diagram must be maintained and is difficult to separate changes when multiple initiatives are all in progress simultaneously. Merging change, and knowing what change is made becomes cumbersome. Again, some of the tools do a good job at change management.
Another technique that has become popular was the ability to data a database comparison. Changes to the next release version of your database are compared to another database, and change scripts are generated to modify one to look like the other. This results in no loss of data. However, the process is somewhat time consuming, and not efficient when you need to do this continuously throughout the day in order to share changes from multiple developers.
In an Agile environment there are often many initiatives running concurrently sometimes impacting the work or others, or at least needed to be merged together. One technique I have found for doing this kind of work is to not make database changes directly using the GUI tools built into tools. Instead, create change scripts for your changes first. By creating change scripts for modifying y our database, there is no need to revers engineer them.
How you create those change scripts is open for options. If you know how to write SQL you can simply write them in an Editor. If not, perhaps you use a GUI tool on a sandbox database and capture the changes.
The point is, if you create the change scripts as you go, there is no need to reverse engineer change. Moreover, scripts may be checked into version control alongside of your application code, keeping everything in synch. Version control systems allowing you to associate a change script to a story allows you to pull specific database changes along with the associated bits.
Tomorrow I’ll share some problems you will experience with a script first approach, and some ideas regarding how to address the issues.
This is a ongoing issue for many DBAs. I’m sure that if you support databases for very long you come across these kinds of issues. So why not share your experience by leaving a comment below, or drop men an Email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Creating cloud driven intelligent systems
How can you harness the power of cloud computing for business advantage? Cloud driven intelligence systems are here. Connect up your intelligence applications to your cloud database and see what you can do!
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
dba3_EnterpriseProcedureStandard_demo
Demonstration of various procedure standards, with examples of common kinds of errors and approaches that cause issues… (read more)