roseanna.mumby

25 May 2021

Overcoming Database DevOps Challenges: Part 1

As part of our research for the 2021 State of Database DevOps report, we asked 3,000+ recipients what they consider to be the greatest challenge when integrating database changes into a DevOps process.

According to the respondents, these are the most important challenges facing database professionals when introducing DevOps practices to database development.

Three of these relate directly to the need to standardize and coordinate, where possible, the parallel approaches to application and database development. They are:

• Synchronizing application and database changes
• Overcoming different approaches to application and database development
• Keeping up with the speed of delivery of applications

This blog post will focus on these challenges and outline key Redgate training resources that will help you and your team to overcome them.

The two challenges related to data privacy, protection, security, and compliance will be covered in part 2 of this series.

Synchronizing application and database changes

The difficulties with synchronizing application and database changes have continually shown up in our annual surveys as the top challenge in bringing DevOps to the database.

We often hear of teams that still track database changes manually and don’t know which version of a database supports what application version. As a result, they are more likely to experience deployment failures and delayed releases. Redgate tools, especially Redgate Deploy, enable you to control the database source for every version of the database, so you can be sure which version of the database code is required to support an application release.

Where there is just a single database and matching application, you can adopt a unified approach to your development. All project resources will be organized side by side in the same source code repository, with a unified strategy for versioning. However, for a database that supports several applications, the challenge is more complex. Each application should have a well-defined interface with the database, and a shared Semantic Versioning system between database and applications, showing which application versions are compatible with which interface versions.

By automating database builds from a known, source-controlled version, and frequently integrating and testing changes, you’ll then find it easier to coordinate database upgrades with changes to the associated application. You can automatically deploy the tested and verified database changes, ensuring that the database and application are always at compatible versions. By testing changes earlier and more frequently, you’ll also uncover problems at a point when they are easier to fix and prevent them from becoming issues later in the pipeline.

Key resources

Moving from application automation to true DevOps by including the database
This article explains the challenges of DevOps automation for databases, starting with how to manage the database, as a set of SQL scripts, in the version control system, and then how to start building an integrated and automated script pipeline for continuously testing and deploying database schema changes, alongside the application code.

Detecting Database Drift during Flyway Database Development
If you’re upgrading an existing database to support the latest application changes, database drift, meaning the target database is no longer at its stated version, is a common cause of integration failures.

Database DevOps for Pragmatists: A Datafaction Case Study
Ami Adler, a Software Development Manager at Datafaction, describes their pragmatic approach to introducing DevOps processes for application delivery. He explains how this allowed them to dramatically reduce deployment failures and improve collaboration, without overturning existing workflows, or disrupting the everyday operation of the business.

Overcoming different approaches to application and database development

The second biggest challenge for database professionals – difficulty overcoming the different approaches to development within multi-function teams – is primarily a concern about process and team culture.

Redgate’s DevOps tools make it possible for teams to treat database changes in a similar way to application changes. This is done by providing a systematic way to handle the factors that make database development ‘different’ from application development. For example, you need a way to save in source control the migration scripts that will safely preserve existing data during deployments. For development and testing work, you also need an automated way to produce the latest build of the database, filled with the required test, static, and development data.
By allowing you to standardize your approaches to development and testing work across both the application and database, Redgate tools make it simpler for you to collaborate and align on processes. It also becomes easier to prevent software errors from reaching production systems by building review steps in the pipeline that fully harness the skills of different teams.

Key resources

Getting Started with SQL Source Control
Learn how to version control schemas and reference data, commit database changes during team-based development and then generate a migration script to update the production system.

Getting started with SQL Change Automation
These training videos walk you through how to automate your database change management process using SQL Change Automation.

Introduction to SQL Clone
Introducing DevOps techniques for database development requires an easy way to provision many copies of a database for development and testing work. Ideally, these databases should be disposable and easy to create. These training videos show you how to create copies of your databases in a couple of clicks using SQL Clone.

Keeping up with the speed of delivery of applications

The final, but important, challenge in adding database changes into a DevOps process is to keep up with the speed of application delivery.

The obvious way of helping database developers is to provide better tools for coding SQL, but it also requires ways of supporting more accurate up-front planning of the data requirements of the application. Tools that make it easier to provide a comprehensive, readable documentation of the tables and the modules that comprise each interface will help here.

Redgate provides the tools you need to speed up the database development and delivery processes and respond quickly to business needs. SQL Prompt will help make developers more productive when writing SQL, and to adopt good SQL coding standards and code quality checks.

Redgate Deploy’s change automation tools, along with SQL Clone, will allow developers to work faster, in parallel, provisioning and resetting databases quickly, using branches to split up the work while minimizing problems associated with code conflicts or dependencies during merges. They also support easier unit testing and integration testing as a routine part of development, to help maintain and improve quality, while increasing speed.

These tools will automate your database change management pipeline and make it easier to perform code reviews along the way, drawing attention to potential issues in the code and database as early as possible.

Key resources

Driving up database coding standards using SQL Prompt
Explains how your development team can use SQL Prompt to speed up SQL development, by establishing and sharing SQL coding standards through code analysis rules, formatting styles, and code snippets.

Getting Started with Automatic Database Branch Switching
Alexander Diab demonstrates how a team of developers can work on and test features in different branches of a SQL Server database development project, while their local development database automatically remains ‘synchronized’ with the current branch in version control.

How to create and refresh development and test databases automatically using SQL Clone and SQL Toolbelt
A PowerShell automation script to build a SQL Server database from source control, seed it with dummy data, document it, and then deploy copies to any number of test and development servers.

You can find more training resources to help you successfully adopt Database DevOps on the Redgate Hub.

To discover more insights from the 2021 State of Database DevOps report, download your free copy.