I have a talk that I give on Automated Build and Test for databases. This was put together by one of the engineers at Red Gate Software, and I've presented it at a number of events. I enjoy it, and it's one of those presentations that really gets people thinking. I enjoy getting people to think about software, and there are always good questions that come out of the session.
The talk is essentially about how to set up continuous integration for databases. Continuous Integration is an extension of the daily build, which companies have been using for years to try and ensure some discipline in their software development practices. They want to limit the amount of code that can potentially break software. The whole idea is to test changes developers commit to version control as quickly as possible and give feedback developers immediately so developers can fix issues while the issue is fresh in their minds.
For databases, this can be a challenge. We have this stuff called data that has to be maintained between, during, and after builds. We can't drop our structures and reload them as a developer does with an executable or DLL. As we work in a CI environment, we need to handle new objects, as well as alterations to existing structures. That means our scripts and code need to handle creation as well as upgrade scripts.
This means that a CI process for databases must not only check out the DDL code from a Version Control System (VCS), but it must also handle the CREATE or ALTER code based on the state of the database. You can always build a database from scratch in your CI process, or you can restore a known version (usually the last version) and apply upgrade scripts. The way this happens depends on how you version your DDL code in a VCS and how your build process is structured.
At Red Gate, we've built a number of scripts to help with this process for various CI servers. We have articles that discuss CI for Team City, Jenkins, Bamboo, and probably more by the time this piece appears. We've learned a lot about CI over the last few years and we are trying to share both our knowledge and code with you, as well as incorporate those ideas into our products.
Whether you use our products or not, we do believe that you will build better software if you implement CI. We just know that we can make the process a lot smoother with our tools for developers and DBAs.