Database Version Control for Agile teams using Scrum

The key challenges of maintaining database schemas for agile teams are:

1. providing easy restoration of previous versions
2. repeatable process that supports autonomous work
3. allow concurrent updates to the database schema objects
4. synchronization with the application code to ensure stable builds
5. storage of production regression test data
6. storage of default domain data
7. quick deployment of a specific version of a database schema through reference objects
8. a quick view of what happened to the database schema for a particular release.

Supporting a database-centric COTS or hosted product line typically involves troubleshooting customer/production issues with previous database schema versions that can be many releases old. Central to easily reverting to previous database schema versions is designing an appropriate codeline structure in the version control repository. Codelines are branches of development that contain the application source files/artifacts and the database schema artifacts. An agile version control repository will contain a codeline for each released version of an application, and a single codeline name “Mainline”. All of the active development work is performed in the “Mainline” codeline, and the other release codelines are immutable. The only time a release codeline is not immutable is if a patch or maintenance work is required in a released version prior to the conclusion of the Release Sprint of the “Mainline” codeline. The last release is branched into a new release codeline, and upon conclusion any maintenance modifications are merged back into the “Mainline” codeline. The codeline structure that follows addresses the key challenges identified earlier.

Sample codeline structure:

Project
   2007.1.0
   2007.1.1
   2007.1.2
   2007.2
   Mainline
      Business Logic
      Unit Tests
      Database
         Library
         Reference
         MyDatabase1
         MyDatabase2
            Previous
               Tables
               Foreign Keys
               Defaults
               DML Triggers
               DDL Triggers
               Check Constraints
               Functions
               XML Schemas
               Stored Procedures
               Views
               Options
               Security
               Indexes
               Synonyms
            Data
            Sprint

The Database folder structure in each codeline above is similar in format. The folder contains a Library folder, a Reference Folder, and a folder for each database in the codeline. The Library folder contains the PowerShell scripts and other files necessary for providing a repeatable database schema restoration process. The Reference folder contains pre-built implementation(s) of each database contained in the codeline. The pre-built database(s) do not have any Sprint updates applied, but they are populated with data. The Sprint folder contains the DDL and DML scripts for active development work. Use descriptive script names for DDL and DML scripts to provide a quick view of database schema modifications in previous releases. I also include a reference to the Sprint Backlog Item in the name for traceability.

I will post PowerShell/SMO scripts to support this codeline structure shortly.

Posted at 12:34 AM  Permalink