Business requirements often dictate how changes in release management are addressed. But by following some essential practices and core beliefs, database deployment does not have to result in the headaches once caused.

As a result of software becoming more embedded in business, firms are discovering that the pace of change is now limited by how quickly they can react to shifting business requirements and deploy new releases of their supporting applications. Additionally, as technology evolves, software application architecture is becoming increasingly complex, shifting to distributed and multi-tier models. This architecture poses multiple challenges when deploying new releases.

In order to ensure a complete, error free release process, changes to all application tiers must be coordinated with every release of a new version. A new version should include updates to the binary code, configuration files and changes in the database objects. In order to have a complete and successful release, one should manage the release and the deployment. Much like assembling a car, all pieces must fit together, and assembled at the right time.

As the architecture becomes more complex, the team structures change to support this complex architecture, so much so that it is not uncommon to see several development teams supporting a single application. These teams may be organized by technologies, different geographical locations, or other criteria.

Software release methodologies became part of ITIL, PMBOK, CMMI and other best practices in response to the increased pace and complexity during the last decade,.

In order to understand the software release process, we should remember that application tiers can be divided into three groups:

1. Binary code2. Configuration files3. Database artifacts

Binary codes contain all the binary files created during the build operation, converting Native code, such as C++, Java, C# into binary executables.

Configuration files include configuration files of the application, usually in XML format and infrastructure, such as web servers, transactions servers, and network definition.

Database artifacts contain the portion of the application that resides in databases. This can also be broken into three groups:

Schema structure - the structure of the entities in the database, such as tables, views, indexes, synonyms

Lookup data – parameters and information required for the business logic

Deployment End-to-EndMultiple products address the deployment of binary code and configuration files, ranging from those that automate the build processes through products that automate the packaging, to others that automate the deployment and delivery of binary code and configuration files. You can find commercial products as well as open source solutions across these products. However, a complete release and deploy solution should cover all application's tiers, without focusing exclusively on the binary code and configuration files.

Database objects present unique challenges during deployment, since they cannot simply be replaced, as is common for binary code elements and configuration files. Database changes must preserve the data stored, or the business will lose its information.

To better understand these unique challenges, let's take a quick look at what is required out of good database deployment solutions:

Comparing and syncing

Deployment based on business requirements

Merging of conflicts

Comparing and SyncingComparing and syncing make up the foundation of a deployment solution. This consists of comparing database code, structure, and lookup data between environments (development, testing, UAT and production), in addition to automatically generating a deployment script that will alter the target environment (production sites) structure, as well as lookup data, to be identical to the source environment (development or integration environments).

This process enables us to change the structure of the target environment to match that of the source environment, without losing the most important asset, which is the most up to date content of the database (the only reason for its existence).

Figure 2: Compare and Sync Database schema

Deployment based on business requirementsDatabases have numerous objects, many of which are changed during the development cycle. Improving the release manager's efficiency while preparing the deployment script using the compare and sync solution is the reason for deploying changes based on business requirements.

The ability to filter out the objects and changes by business requirements increases the agility of the organization to respond to a faster deployment cycle. The organization is then focusing on the relevant changes, and ignoring background noise, such as incomplete development efforts or postponed tasks. This can be easily achieved by enforcing version control management policies on the database deployment, and making sure all changes are linked with the relevant change requests.

Figure 3: Deploy database changes by business requirements

Merging of ConflictsIt is the responsibility of the deployment solution to provide a good platform to merge the conflicts in the database code that can happen. Conflicts can occur due to several reasons, such as emergency patches, working on several versions in parallel, and merging several databases, in addition to others.

Conflicts can be in code, such as procedure. In the case described in the image below, it is clear that a change has occurred in both development environment (on the left side), and the integration environment (on the right side), as we can see that both are different from the baseline (previous version, in the middle). Understanding the nature of the change is necessary in order to make an educated decision of how to merge the code on the bottom side of the screen (taking some changes from the development branch, and incorporating them with the changes made to the integration environment by another team).

Figure 4: Merge conflict for database objects

Conflict can also occur in lookup data. A good deployment solution has the ability to drill down and display the origin of the change, thus giving the user the ability to make an educated decision to override or ignore.

Let's examine the picture below in which there are three rows with different keys (ID): 1300, 1500, and 1600.

There is a change between the environments in the CODE column for the row represented by ID,which is 1300. The source and baseline environments have the same value (6656) and the destination environment has a different value (6823). Using the baseline comparison, the team leader resolving the conflict will decide not to include this update in the deployment script since the change origin is not from the source environment

The row represented by ID, which is 1500, has a change in the DESCRIPTION column. All three environments (source, destination, and baseline) have different values. In this instance, the team leader will decide that the correct value is from the source environment and will update the destination environment with that value

The row represented by ID, which is 1600, contains a change in the CODE column. In this row, the origin of the change is from the source environment with the value (501090) and the destination and baseline environments have the same value (50090). In this instance, the team leader will decide to override the destination environment's value with the value from the source environment

Figure 5: Merge lookup data conflict

In ConclusionIT and development are required by the business units to deliver new features faster in order to respond to the changes in the market.

A good deployment solution must cover all application tiers, such as binary code, configuration files ,and database code.

There are many products addressing release and deployment management for binary code and configuration files, but few acknowledge the database artifacts.

Deployment of database changes is based on compare and sync and the team should have the ability to deploy changes by business requirements (which can include enforcing version control management on the database development) and a good merge conflict platform.

About the author

Uri Margalit

Uri Margalit is the Director of Product Management at dbMaestro LTD (www.dbMaestro.com). dbMaestro is a software company that is developing a line of products helping organizations enforce change management policyies on the database development. Prior to joining dbMaestro Uri Margalit lead the development of a line of products at Precise LTD.