Database Task-Based Deployments – Part I

In today’s agile world, development teams must respond quickly to ever changing business needs. Common practice is to use a task management system, such as JiRA or TeamForge or TFS or RTC, or any number of similar solutions.

These task management systems manage the business requirements underlying the need to make a change, both to the application code as well as the database code.

With such a system in place, you can take this organizational structure a step further by establishing a weekly, bi-weekly or monthly committee meeting to decide which tasks are approved to be promoted to production.

If an automation infrastructure exists, the change can be promoted automatically after the check-in, as Facebook, Twitter and other companies do.

database-version-control

The Challenge

This system presents a new challenge to the organization: how to build (compile) the application such as to include only the approved tasks, while at the same time ignoring unapproved changes that were checked-in the version control repository.

Native Code Solution

The leading file-based version control tools today have solved this challenge by providing several methods. One popular method entails branching and linking the check-in to the reason behind the change via the task management system. Such integrations also provide a system to extract the files based on the selected tasks.

The build server extracts the application code for the approved tasks, compiles it and then the binaries can be deployed to testing/production environments.

This system relies on an important assumption that the database version control repository is the single source of truth for the code. Only changes that are in the version control repository are available to the build process, changes made locally on the developer PC and were not check-in are not to be promoted.

Of course, the database is a crucial component of any application, and the standard approach to integrate database development and deployment using file-based version control just doesn’t works very well for the database.

The Database Challenge

There are several approaches to managing database changes:

  1. Focusing on the development –Save the CREATE and the ALTER scripts in the file-based version control. The CREATE script is used to create the database from scratch and the ALTER script is used when upgrading the application.If there are many ALTER scripts (one for each change), than the file-based version control provides the ability to extract the relevant ALTER scripts based on the approved task.If there is one accumulated ALTER script then it needs editing every time there is a change and it needs to include only the approved tasks.The problem with this approach is that file-based version control inherently lacks any hierarchy of authority for changes.  Anyone can login to the database and make changes that will not find their way to the file-based version control – and pass quality assurance tests since they’re technically valid.
  2. Focusing on the deployments –Using tools that compare two database environment or file(s) with another database and generate the deploy script.With the simple compare & sync today, this method is risky because it compares the source to the target, without deference to version control, and is liable to generate a scripts that revert critical fixes.For example, if there’s an index in production (which is the target) and not in the version control repository (source), compare and sync tools are unaware of whether an index was added in production (and needs to be protected from outdated revisions in development)or removed from development (and needs to be deployed to production).

The Solution

The solution is to integrate database development and deployment  into a single process à la enforced change management.

In so doing, you would be wise to make sure that your version control repository is the “single source of truth” for projects progress, using an enforced change policy that guarantees every change to database objects are documented.

Of course, such a management infrastructure should be combined with a sophisticated impact analysis that uses baseline-aware algorithms to identify the nature of the change whilst discarding changes that made in a different branch, parallel environment or conflict with a critical fix.

 

——

This is part one of a two part series examining the ideal structure and governing progression from database development to deployment. In part two of the series, we’ll look at why and how developers should document the reasons behind changes as part of the check-in metadata of the check-in when deploying across environments.