Recently I have been seeing more and more projects using agile database design techniques in an effort to keep database change in step with software code utilizing the database. Code can change very quickly; especially if accompanied by unit testing. Code can be more easily changed with confidence if the unit tests continue to work after change is completed.
A database can follow some of the same processes depending on how it is used, and the changes that are being made. The more difficult agile changes are schema changes where existing data must be maintained as a part of that change. Some schema changes are simple such as creating default values for columns or changing data types where the previous data may be changed implicitly to a new data type.
More complicated changes such as foreign key constraints or table modifications requiring a new table and transferring the data from a previous table are more complicated. Obviously, this is less complicated in a development database. But when you have to make the changes to existing production data the problem gets more detailed.
How do you manage this change in a repeatable manner? Alexander Karmanov demonstrates a detailed version of how he maintains change in an agile world in his editorial, https://www.simple-talk.com/sql/database-administration/an-incremental-database-development-and-deployment-framework. I have used a similar process successfully. There are a lot of benefits because change can be viewed and compared in version control. Change deployment is automated, and may even be included in Continuous integration.
This is still a new area where manually managing change is difficult and slow; however, this tends to be the most common method implemented. Tools continue to evolve as the need continues to increase. There are some tools having a better fit than others.
What do you think is missing in the tools available to you today? Share your thoughts with btaylor@sswug.org, or leave your thoughts online.
Cheers,
Ben