There are two key requirements to building software with a team of developers. First, you must have the ability to isolate the software modifications that are not dependent. For code, we do this by branching. For databases, the separation is more difficult to implement.
The second key requirement is that we need the ability to merge new, complete, functional changes into the rest of the code. For software, we do this by merging branches from feature branches back into a main code branch. Again, for a database, this process is more difficult.
Without having implementations for these two key requirements, it is quite impossible to consider using an agile methodology to build software. So, what makes this so difficult for database software, when it has been solved with code for decades? If you are a database professional, you know the answer is that change control of a database is more complicated, because change can result in un-acceptable data loss.
We have had the capability to maintain version control on database objects for a long time. You could roll your own, maintain scripts, use third party database design tools, or even use tools provided by the database vendor. However, applying changes to database objects has never been easy, especially when you are modifying existing tables in a drastic way. It is even harder to maintain a change capability allowing you to roll back your change, should it be required.
Without respect to the SDLC being followed, here is an implementation I have found works really well for me. Since I do primarily SQL Server and Dot Net programming, I like to use Visual Studio database projects. This provides me with a database unit test framework, and version control for database objects. It’s great to be able to execute unit tests right in the same tool as the rest of my software, by including the database project in the same solution as my code. I find it very helpful to compare tables or procedures at different versions with ease in Visual Studio.
In contrast, database projects work ok when you don’t modify existing objects, if you have it configured to not allow data loss. Database Projects allow you to write all of your own migration code with the exception of objects, including tables. What it does not allow you to do is to roll back an existing database to a previous version. Moreover, if you do not allow data loss, and you have to modify a table in a way that is not simple, then you have to delete all the data from the table in order to make the change. I have had that process take hundreds of lines if I need to modify a table containing relationships that span out very deeply.
The best solution I have found at this point is simply to write my own migrations. Fluent Migrator, a project you can get using NuGet, allows you to write migrations in Fluent syntax, embedded SQL, or from source files containing SQL commands. The neat part is that you can put as little or as much as you like in a single migration, consisting of a single C# or VB.Net class. A migration can contain an UP and a DOWN method. The UP method applies the changes in the class to alter the database to the new version. The DOWN method reverts the database to the previous state.
So, you can specify what version you want your database to be brought into compliance, UP to version x, or DOWN to version x, and the engine will apply whatever migrations are needed to bring the target database into compliance. This makes deployment of database change automated, testable, reproducible, and reliable. The beauty is that you have to generate or write this code anyway. Using Fluent Migrator, it is done as you are designing the change, so you don’t have to do any comparison to figure out what needs to happen. You define the work up front, and the engine determines what migrations have not yet been applied to the target.
Those are my two key tools allowing me to work quickly, successfully, and incrementally in any SDLC, while segregating my changes from other developers until they require the new functionality.
Cheers,
Ben