There exist a handful of commercial vendors and open-source projects aimed at bringing DevOps automation to the database. Whether you’re opting to build a totally custom solution, extend an open-source project, or to invest in a commercial off the shelf solution, the remainder of this guide focuses on key best practices that your DevOps database solution should meet in order for you to get the most out of your investment.
Best Practice: Track Database Code With Application Code
Just like application software changes are tracked in source control, to understand the evolution of the database, it’s equally important to track database changes with a source code control (SCC) solution as well. Choose a DevOps database solution that allows you to use your existing application SCC solution, such as Subversion, git, Microsoft TFS, etc., instead of a solution that implements a separate SCC uniquely for the database or a solution that simply cannot track database changes in your SCC system out of the box. The goal is to minimize changes to the developer workflow without taking on a large integration or extension project.
By ensuring that the database automation solution integrates out of the box with the same SCC system used for application code, developers can keep their existing workflow. This reduces the complexity, cost, and maintenance associated with having a separate SCC system in place for just the database.

Diagram: It’s counterproductive to have a separate source code control solution for the database code that is independent of the system used for application code. It means that effort has to be duplicated (code needs to be checked in two times to two different systems). It also means that database changes that support application features can drift and get out of sync as they flow through separate processes, adding to confusion and error while impeding velocity.

It’s very efficient to check both application code and database code into a single source code control solution. There is no disruption to the development process, and it’s much easier to keep application and corresponding database changes synchronized through the build, test, and release process/pipeline, as things can always be referenced against a single commit.
Tip: Be wary of solutions that have a separate SCC system in place for the database. With two SCC systems, either two people will follow two separate processes or one person will end up doing two duplicate tasks to achieve a goal. Coupled with the additional costs for integration and maintenance, adding another SCC solution just for the database is a poor investment that fails to eliminate error and improve visibility.
Businesses are keen to minimize risk, cost, and downtime. A firm can go out of business if a critical application slows to a crawl, fails entirely, or worse, if production data is lost or becomes available to competitors or attackers. To maintain system performance and to protect their valuable data, organizations typically have a lot of rules, best practices, standards, and conventions governing the database. Currently, DBAs serve as a manual safeguard and end up reviewing any and all changes that need to be made to the database to ensure they meet all the organizational standards, rules, and best practices.
To eliminate the bottleneck of this manual process, it becomes necessary to automate the very necessary, yet mundane efforts of DBAs. This way, database changes can move at the speed of application changes and DBAs can be freed to address higher value projects such as performance improvements, upgrades, etc. Look for a solution that allows appropriate granularity in rule definition and enforcement. Ideally, this means a solution with an object model that can enforce changes at an individual changeset level so that rules such as “having a foreign key that points to a primary key that does not exist” can be easily defined and enforced.
Tip: Be wary of solutions that simply rely on regular expressions for rule definition. Relying solely on regular expressions makes it nearly impossible to manage simple structural standards. Instead, with regular expressions, a rule will have to be created to reject any changes relating to specific names or keywords and create a lot more review cycles and manual intervention for DBAs.
rule "All tables should have a primary key or unique constraint"
when
$db_model_container : ModelContainer( )
then
String errorMessage = "";
for (Schema schema : $db_model_container.getNewModel().getSchemas()) {
for (Table table : schema.getTables()) {
if (!table.getName().toUpperCase().equals("DATABASECHANGELOGLOCK") && !table.getName().toUpperCase().equals("DATABASECHANGELOG")) {
if ((table.getPkConstraint() == null) && table.getUniqueConstraints().isEmpty()) {
errorMessage += "Table (" + table.getName() + ") needs to have either a primary key or unique constraint.<br/>\n";
}
}
}
}
if (errorMessage != "") {
insert(new Response(ResponseType.FAIL, errorMessage, drools.getRule().getName()));
}
end
This is an example rule definition done in Drools that can be consumed by Datical’s Rules Engine. Datical is one of the commercially available solutions for database release automation that supports rule definition against objects, as recommended by the best practice.
Beyond rules that can be written against database objects at a changeset level, look for tools that can flag destructive changes before they happen. It’s common for DBAs to manually check for things like, “Is there data in the column that this SQL script is trying to drop?” A tool that allows for the automation of such checks will save even more time and will better align the pace of database changes with application code changes.
Tip: SQL cannot simply be evaluated in isolation; it has to be evaluated in the context of the target database state. As such, look for products that can ingest and simulate changes against the target database state to truly free DBAs from the manual process and speed up the database release process.

This is an excerpt from a report generated by Datical DB, which has found some SQL that was checked in by a developer that violates the rule that tables should have a primary key or unique constraint. By automating and enforcing rules, tools like Datical or Red Gate make it easy to spot issues in SQL change scripts and facilitate quicker remediation.
Another fundamental tenant in addressing the database bottleneck is to enable developers to self-serve. Today, developers need to wait for days to hear back from a DBA about whether their change is valid or not and then repeat the cycle of waiting every time rework is necessary. In many organizations, the SLA between the DBA team and development team is defined in days or weeks. This means that a database change submitted by a developer at the start of a sprint may end up getting rejected at the very end of the sprint or possibly even after the sprint is completed, entirely throwing a release off track. The manual database change review process breaks DevOps and is simply unsustainable.
This is another issue that can be addressed with automation and Continuous Integration. It’s a manual and tedious effort to involve DBAs in every change that a developer wants to make. An extension of the previous best practice on defining rules and automating enforcement, this best practice is to look for a tool that can automatically evaluate changes submitted to SCC by developers and provide feedback in delivered in minutes instead of the typical days or weeks that it takes with manual review by a DBA. Even better, if the tool integrates out of the box with build tools such as Jenkins or in-house build systems and fails, the build database code doesn’t pass validation.
Tip: Avoid introducing process changes that lock the database down further. In order to realize an improvement in database change velocity, it’s necessary to allow developers to retain the workflow for application changes, where there can be simultaneous development against a given feature area. Merge conflicts are identified when pushing to source code, and functional issues that result in build failure or automated test failure are returned to developers for remediation. The database workflow needs to be able to mirror this, with checks running when code is checked into a source code repository and any failures getting immediately reported back to the developer.
Once the DBA team codifies organizational policies and checks into rules, the best database release automation tools allow developers to benefit directly from the automated rule enforcement. By integrating the automation with build tools, database changes that are checked in can immediately go through validation. If there is a bad change, it will break the build and developers will be immediately notified. This allows DBAs to be free from the constant development churn and focus their efforts on higher-priority projects instead of getting overrun and working overtime to keep up with development while allowing developers to self-serve and immediately get feedback on the database code they are checking in.
CREATE TABLE T_SocialMedia (
[Socialid] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[URL] [nvarchar](200) NULL,
);
This is some sample SQL that is responsible for creating a new database table. With database monitoring tools, when code like this is checked in and built, it results in build failures, as the automated rule checking can catch that this table doesn’t have a primary key or unique constraint.

When tools like Datical are integrated with build automation tools like Jenkins, the build will fail if bad SQL code (like the example code above, creating the T_SocialMedia table) is checked into source code control and is included in the build pipeline. By integrating database code directly into the existing application release pipeline, any bad database changes can be immediately discovered and developers can be notified to make prompt fixes.
CREATE TABLE T_SocialMedia (
[Socialid] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[URL] [nvarchar](200) NULL,
CONSTRAINT PK_T_SocialMedia PRIMARY KEY (Socialid)
);
Once the build fails, the developer can make a fix and check in SQL that meets organizational standards and rules. In this case, it’s simply a matter of not violating the rule that all tables should have a primary key or unique constraint by defining Socialid as a primary key.

When the updated database code is checked in and a new build is kicked off, DevOps tools for the database can validate that no rules are violated and can allow the build to proceed. With integration with build tools like Jenkins, it’s possible to see, in this case, that the change was successfully built and has been deployed to higher environments.
Best Practice: Build Once, Deploy Often
Just as with application code changes, it’s important to follow the mantra of “build once, deploy many” with database changes, as well. If it requires custom or manual work to deploy a given database change into each environment along the pipeline, it becomes very difficult to isolate issues. Fundamentally, without building the database change into an artifact along with the application code, it becomes very difficult when things go wrong to tell if it is a bad database change that somehow worked in lower environments or if is it proper change that’s failing because there is something abnormal with the environment itself.

Tools like Datical integrate with artifact repository solutions such as JFrog Artifactory, and allow database code to be included in a single artifact that also contains the application code when the build is successful.
Solutions such as Datical allow database changes that have been checked into source code and that have passed the automated rule-checking to get built into an artifact along with application code. This way, the workflow automation and process can be maintained across the rest of the pipeline, and any issues can quickly be traced back to the environment or the offending code change.
Best Practice: Automate the Database Release
Beyond rules and artifacts, it is essential for the database release automation tool to actually automate the database change! However, before blindly deploying a change that has managed to pass through all the rules that have been created, look for tools that allow you to simulate the impact of a change before actually committing to the change. After all, there may be nuances to the environment in question and there may not be rules to safeguard against what might actually be a bad change.

Tools like Datical integrate with solutions like XLDeploy from XebiaLabs in order to automate the actual deployment of the single artifact.
Database automation tools simulate changes to the database and compare the simulated database against the original. These tools also allow the rules used to validate changes to be selected based on environment. Lastly, by integrating with the rest of the application release automation pipeline, the best tools increase the overall return and value of the entire Continuous Integration and Continuous Delivery toolchain.
Tip: Be wary of tools that still keep database release automation a separate process from application automation. This can lead to the database and the application getting out of sync, and means that there is duplication in process or people during release. Make sure that the tools you select or augment integrate with your existing application release automation tooling.
Best Practice: Know the State of Each Database
Both for operational sanity and for regulatory compliance, it’s necessary for organizations to clearly understand the state of each of their databases — and certainly at least the production database! Look for a database release automation solution that provides visibility into state of each database in each environment. Look for a solution that can quickly answer, “What changes have been applied to this database?” or “What is the difference between the application database in the staging environment and the production environment?”
With more rapid release cadences and more people involved in the release process, it becomes increasingly more difficult to answer exactly what changes have and have not been applied to a database in a given environment. To avoid nasty surprises, to pass audits, and to ensure better uptime, it’s essential to have a complete understanding of the state of each database.

Tools like Datical provide a dashboard view that summarizes the state of each database environment. Any environments that do not have the latest changes can be quickly identified, and teams can quickly drill into what outstanding changes have been made to each environment, regardless of the release pipeline that the environment belongs to.
Best Practice: Application/Database Synchronization and Flexible Feature Deployment
For a variety of reasons, it is common for an organization to revise the release plan to only include a subset of the features originally planned. As such, any database release automation solution needs to be able to accommodate for the inevitable feature churn that is a reality in high velocity Agile software development teams. Look for solutions that allow database changes corresponding to specific features and releases to be labelled accordingly, with mechanisms to ignore or unignore labels. The best tools integrate with ticketing systems such as JIRA to enable traceability from development all the way through the build and deployment pipeline.
Branch-based development, which is increasingly common across most enterprises, is much easier when the database release automation solution ensures that database changes can remain synchronized with application changes and flow in lockstep with application changes from development through production. Confusion around the question, “What application feature does this particular database change have to do with?” can be entirely eliminated. Release quality and is improved, as any unnecessary database changes that might impact the production version of the application can be left behind, along with corresponding application changes in lower environments until the feature set is ready for promotion to higher environments.

Tools like Datical allow immediate visibility in specific feature sets by integrating with ticketing systems like JIRA and providing a web UI to understand where specific changes have been made. In this particular example, the change JIRA-104 has been deployed to all environments except Production. This ability to quickly understand the state of each database and know what has and has not been deployed can expedite troubleshooting and increase both the stability and the quality of releases.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}