Guide to Database Continuous Integration

What is database continuous integration?

Database continuous integration (CI) is the rapid integration of database schema and logic changes into application development efforts and to provide immediate feedback to developers on any issues that arise. As a further evolution, database continuous delivery (CD) seeks to produce releasable database code in short, incremental cycles.

What is database continuous delivery?

Database continuous delivery (CD) is a process that focuses on getting changes for new features, updates, and bug fixes into production and to the users as safely and quickly as possible. This is achieved by streamlining the development process and keeping application code and database code in a deployable state. 

The goal of continuous integration and continuous delivery (CI/CD) is to reduce time-to-market and create a steady stream of end-user value with frequent, high-quality software releases.

To deliver new software experiences more quickly to market, companies have spent the last decade trying to modernize the way they build and deliver software. In their efforts to modernize and improve the continuous delivery pipeline, many tools have focused on bringing CI and CD to application code. However, the same has not happened for database code (see Figure 1). Many organizations still rely on a shared service database team that manually reviews and deploys DB code changes. Given that the end-user experience is incomplete without the full software stack, which includes both the application and the database, there has been growing interest and demand in continuous integration and delivery tools for the database as well.

 

Continuous Integration pipeline before database deployment automation

Figure 1. Continuous Integration: Before Database Deployment Automaton

 

How do database CI tools impact database deployments?

As stated before, the aim of database CI tools is to bring the same integration and deployment best practices to the database and enable SQL code to flow through the software release pipeline, synchronized with application code (see Figure 2). By aligning database deployments with application deployments, teams see a much better return on their investments in the tooling and process updates. This, in turn, helps teams bring new innovations to the market faster and with higher quality.

 

Continuous integration pipeline after database deployment automation

Figure 2. Continuous Integration: After Database Deployment Automation

 

Database Deployment and Database Continuous Integration Best Practices

Implementing and following continuous integration and continuous delivery best practices for databases is easier than you might think. Many of the best practices that apply to application CI and delivery tools readily apply to the database as well. These best practices include:

1. Tracking Database Code Changes

Database code changes should be tracked in the same source or version control system as application code. Database code should not be treated separately or tracked in an entirely different system. A separate system for database code leads to redundant effort, lack of visibility, confusion, and errors. Application and database code will also begin to drift on a separate system and get misaligned. Automated DB deployment tools like Datical allow teams to push database code into the source or version control solution that is already in place for application code.

2. Automated Database Code Validation and Feedback

Once the application code is checked in, a series of automated tests are immediately triggered. These tests assess if there are any issues in the code that warrant rework. Unfortunately, the same isn’t true for database code at most companies. One of the biggest challenges in accelerating database deployments is getting rid of the manual SQL code review that DBAs must perform.

This tedious manual effort can and should be largely eliminated by intelligent automation. That way, developers can get immediate feedback on SQL code – just as they do with application code – and avoid a long wait state in which they move on to a different task. This inefficiency in feedback causes large delays in database deployments and contributes to poor quality. It also forces developers to context switch to make fixes on SQL code changes that they wrote days or weeks ago.

To properly automate the validation of database code, a database deployment automation solution must have an object-model of the proposed SQL code change. Otherwise, functional rules, such as ensuring that all tables having a primary key or unique constraint, cannot easily be validated. This means a DBA will need to give the code manual attention – which also means developers don’t get immediate feedback on changes.

Datical’s Dynamic Rules Engine is unique in that it is an object-based rules engine that can be easily extended. The Dynamic Rules Engine gets rid of much of the tedious DBA review otherwise required.

Stay away from simple, regular expression-based rules systems. It’s impossible for these systems to functionally validate the common organizational rules and standards that DBAs end up spending much of their time and energy on, instead of making progress on more critical value-add projects such as performance tuning, data architecture, high availability strategy, system upgrade planning and more.

3. Packaging Database Code

An important DevOps mantra is to “build once, deploy often.” Effectively all continuous integration tools allow application code to be built into an immutable package for consistent, repeatable, and predictable downstream deployment. Should anything go wrong, its errors can immediately be traced either to the application code or the environment when working with an immutable artifact.

Database continuous integration tools bring this same advancement to database code. As an example, Datical’s Database Code Packager creates an immutable, idempotent artifact from validated database code so automated DB deployments can enjoy the same consistency, repeatability, and predictability as application code releases.

4. Providing Visibility into Database State

Another key DevOps tenet is to “amplify feedback.” Continuous integration solutions focus on providing visibility and feedback readily and immediately. As such, a database continuous integration solution should have an accessible web interface. This interface should allow all stakeholders to quickly understand the status of every database. Furthermore, it’s important for the solution to integrate with ticketing systems such as JIRA and TFS, and to support parallel development strategies commonly found across enterprise development teams.

DB deployment automation tools like Datical provide a web interface and have labeling systems that integrate with ticketing solutions to provide necessary visibility and feedback to appropriate stakeholders. With Datical, all DB code changes can be traced back to source code control. This quickly links the changes to the business value that they are meant to deliver.

Continuous Integration and Database Deployment Automation Tools

In order to build a good foundation and implement CI/CD for the database, you need to have the correct database integration and deployment tools. There are four categories of database deployment tools you should have in your automation framework to achieve database continuous integration:

Database Development Tools

When you think of database deployment tools, it’s unlikely you’ll consider application development tools as part of that category. However, all your tooling should work together to achieve database continuous integration. Just as application development tools have grown to integrate with CI solutions, the same is true for database development tools as well. 

Quest Software’s Toad has some great features for supporting Agile database development. Databases are different than the application and have state, so having a database instance to develop against as part of a team is key. Toad’s Team Coding allows users to leverage existing Source Code Control (like Git, Subversion, and others), and a live database to support database developers. Not only can they check their SQL scripts in and out, but they can do so with the database objects like tables and stored procedures, as well.

Database Release Automation Tools

Beyond an appropriate development tool, database CI requires a DB release automation solution. Database release automation tools like Datical deliver automated validation, build, test, and deployment of database changes. These core capabilities ensure that any database code pushed to source code control is pulled out into a CI process that can provide developers with near immediate feedback. With a database release automation solution, software teams can consistently deliver a continuous stream of value to end users without getting slowed down by database deployments. Plus, Datical integrates with DevOps tools your team is already using, like Jenkins

Application Release Automation Tools

As organizations add new features and enhance existing software, the number of components and the complexity of the software stack have only grown. As new trends emerge that allow functional isolation and which avoid single points of failure, orchestrating and aligning the release of all the necessary components requires application release automation (ARA). Tools like CA’s Automic, IBM’s UrbanCode Deploy, Serena’s Deployment Automation from MicroFocus, and XebiaLab’s XL Deploy integrate with Database Release Automation solutions like Datical to enable Continuous Integration for the full software stack.

Test Data Management Tools

A key trend in software development is test-driven development, in which the CI process is enhanced with production-quality test data. It’s common for applications to change functionality based on the data stored in the database. Consequently, providing data that mirrors (or matches) production data is essential to a database continuous integration process that can generate high-quality test output.

DevOps Tools like CA’s TDM, Delphix Data Virtualization, and IBM’s Optim, populate test databases with data. Some solutions, such as Delphix, allow users to request masked self-service copies of production databases for integrated testing environments.

Continuous Integration and Continuous Delivery are best practices for accelerating the speed and quality of application code changes. Similarly, database Continuous Integration is important in accelerating the database release process while reducing risk. By including a database release automation solution and adding database CI to your existing application delivery toolchain and process, you can increase the pace and quality at which the entire software stack – including both the application and database – can be delivered to market.

Check out the DZone DevOps for Database Ref Card to learn more about including databases in your CI/CD process. 

Seeing is believing.