Use source control for your database

Just like with your application code, any database deployment needs to be linked with a source control system.  Database schema needs to be in source control, so that any database changes can be tracked, regulated and procedures can be set.

However,  CI/CD pipeline processes often don’t reach the database. As a result, database delivery is still managed manually, bringing with it all the problems of manual deployments: configuration drifts, errors, downtimes and unauthorized code deployments.

When planning your source control for the database, make sure you capture everything! Don’t forget to include:

    • Tables or Collections
    • Constraints
    • Indexes
    • Views
    • Stored Procedures, Functions, and Triggers
    • Database configurations
    • Roles and permissions policies

Data that controls business logic such as lookup tables also need to be in source control.

In addition, developers need a frictionless way to easily create local databases, while shared databases should only be updated via a build server as a matter of policy.

Track and document database schema changes

Database schema changes should always be documented and tracked. For example, when migration scripts change the database with every version release, you should be able to review previous deployments and revert back to any previous version if necessary. Just like you do with your application code.

Let’s look at another case. In order to deploy a change that adds a NOT NULL constraint to a column with existing data, you’ll need to write a migration script that loads a default value to any NULL columns in the table. But what if you need to know the previous value in the column later on?

Just like with code, the history of any database changes must be stored in source control. Tracking should be incorporated into the automated deployment process.

Monitor and enforce roles/permissions and policies

There are other things that need to be monitored beyond the obvious schema and static data. The security configuration of any database needs special care and roles/permissions have to be managed closely.

At any given time you need to be able to trace any change to the database code, knowing how, when and how to deploy the code.

Prevent the database configuration drifts in advance

Nobody likes to deal with configuration drifts. Unfortunately, configuration drift is the leading reason for errors when introducing database changes rated so by 70% of the companies, according to our survey of over 250 database professionals.

Database configuration drifts increase failure rate of database or application deployments, and causes headaches for both the development, and the DBA teams. Unfortunately, with manual management of database deployments it is nearly impossible to prevent such problems.

Related: The State of Database DevOps in 2019

Automating Database Schema Changes

Changes happen very fast in DevOps environments. Unfortunately, the database cannot keep up. As DBAs are mostly dealing with lots of manual workflows to manage database deployments, it becomes painfully obvious that in order to solve database delivery problems we must automate the process.

Often development teams write code that requires database schema changes in order to execute, without intending to do so or realizing how much headache they just created for the DBA team.

These changes matter a lot to the database structure, as it makes changes like adding new tables and table columns, while modifying data types.  It is easy to make schema changes in a development environment. But when these changes are pushed to production – all hell breaks loose.

When it comes to migrating schema changes from the application code to the database, we are still relying on manual workflows to manage these processes.

This is also where database automation tools come into play. These tools ensure that code developers write is compliant with the database structures, simplifying the communication between the teams and ensuring that if schema changes are required, that they are applied as quickly and accurately as possible.

Top Database DevOps Best Practices

When it comes to DevOps best practices, they must be extended to the database in order to connect the dots throughout the entire release process. By including the database into your CI/CD you can significantly reduce risks, downtime and the need for manual workflows.

  • Automatically Tracking Changes in Database Code 

Automated DB deployment tools, help DevOps teams push database code into source control systems. By keeping track of what, when and how of every change in your database you can ensure a smooth database delivery process.

  • Database Code Packaging   

Developers need to build immutable packages for consistent, repeatable and predictable downstream deployments. To serve this purpose, code packagers enhance applications to create and release automation.

  • Automatic Database Code Validation and Feedback 

Manual SQL code reviews are one of the most hated DBA tasks. The solution is to get it resolved by intelligent automation to get immediate feedback on SQL code (i.e – solutions with review engines).

  • Add Visibility into the Database 

Database deployment automation solutions enable tracking of changes in database code, providing integration with ticketing systems like JIRA to provide database state feedback and stakeholder visibility.

  • Avoid Pipeline Bottlenecks by Including the Database

A smooth CI/CD pipeline that includes the database helps get into the automated testing cycle, the staging environment, and finally to production faster.

  • Build One, Deploy Many for Your Database

If the software requires a building step, then that step is executed only once, and the output repeats throughout the pipeline. It helps prevent the problems that arise when the same software gets packed many times.

  • Dry Run ‘Well Before Pushing To Production

Before pushing code to your database pipeline, especially to production, ensure that it works smoothly with a test database in order to detect errors before any damage is done.

  • Release Often 

Frequent releases are only possible if tested in a production environment and are in a release-ready state using testing methods like A/B testing.

  • Security First 

You have to stay compliant and secure. Make sure you are managing your permissions and policies with a centralized governance solution, which also documents all database activities for future audits.

  • Use On-Demand Testing Environments

This approach allows the QA teams to reduce the number of environment variables. Its prime advantage is that it adds agility to the CI/CD cycle.