True DevOps: including databases in automated deployment

The recent State of Database DevOps Report revealed that within two years, 80% of companies will adopt DevOps. That’s an interesting finding in itself, but the report also showed that 75% of companies already have developers in their team who work across both applications and databases, and the biggest driver for including the database in DevOps is to increase the speed of delivery of database changes.

While there’s a big interest in integrating database changes into a DevOps process, the greatest challenge is synchronizing application and database changes, and overcoming different development approaches.

In a typical application automated deployment pipeline, for example, deployments are linked to source controlled versions of the application code so the changes made in development can be associated to what has been deployed:

automated deployment 1

Databases are more problematic

While it would be great to simply regard the SQL code that builds, maintains and updates the database schema as just that, code, and deploy changes through source control, continuous integration, and release management, four issues arise.

  • Deployments must preserve the existing data
  • Static data vs production data
  • DBAs aren’t always part of DevOps
  • Databases can drift

Persistent data

Application code is pretty easy to change, but with a database the data has to persist. You can’t simply drop and replace the database, like you would an application. Instead, you have to create some sort of upgrade script, preferably from source control, and preferably in an automated way to simplify the process.

Static data

While the database contains customer and perhaps transaction data, there is other data too, outside the realm of production. Reference data, lookup data, static data – all the data that makes your system work – needs to be deployed alongside schema changes. And you also need to think about how to transfer data in the other direction for testing. How do you test the latest build in development with production or production-like data, for example?

DBAs

The word DevOps refers to the problems associated with Dev and Ops teams working in siloes. Nowhere is this more apparent than in the land of the database. Is there anyone who has never heard of any problems between Dev and DBA teams?

But it goes further than that. With application source code we use source control, we invented distributed source control systems and we debate about the optimal branching strategies and strategies for implementing continuous integration.

We’re now beginning to have these conversations about databases. We need strategies for how to provision individual developers with their own copies of databases to use in sandbox environments. Different developers work in different ways, some working off scripts and others working directly on the database, which calls for a better way of working together.

Drift

When we talk about DevOps and Continuous Delivery, the term cycle time often comes up. How long does it take to make a one line change, run it through the normal testing process and get it to production?

If the cycle time for your database is measured in days, weeks or months, then when you hit a production issue, you don’t have time to go back to your source code. The business is hemorrhaging money and the DBA will often make a decision to perform a hot fix on the production database.

This drift causes problems. Environment inconsistencies undermine tests and can cause failed deployments, either because code clashes or because important fixes are accidentally rolled back. Drift and poor DevOps processes are a vicious circle that needs to be broken.

The deployment pipeline starts with source control

Everyone is already doing source control for application code, and any deployment, whether for the application or database, needs to be linked to a source controlled version so we can associate the changes made in development to what has been deployed.

automated deployment 2
It looks tempting, doesn’t it? The changes to both the application and database are committed to source control, which can then trigger a continuous integration process to test the changes, before they go through to release management, where the DBA can check the database deployment scripts before they leave development.

But it also begs the question, what database artifact should be in source control? The database code or the upgrade scripts? There is no right or wrong answer because both can be the right answer at different times.

In state-based deployments, the deployment script is generated dynamically at deployment time using a tool like SQL Source Control. A plug-in for SQL Server Management Studio (SSMS), it aims to enable a single step between the current database state and the desired database state.

The current state of each object in the database is versioned as a CREATE script. SQL Source Control compares the source to the target and auto-generates a script to synchronize the two states using the industry-standard SQL Compare engine:

automated deployment 3
In migrations-based deployments, the deployment script is a concatenation of migration scripts generated at deployment time, using a tool like ReadyRoll, which plugs into Visual Studio.

ReadyRoll auto-generates numerically ordered migration scripts, again using the SQL Compare engine. Changes (often ALTER commands) are then organized in SQL scripts and run in order to migrate a database from one version to the next:

automated deployment 4

There’s no right way – and there’s no wrong way

People often talk about state- or migrations-based deployments as if it’s a hard choice. It isn’t. Sometimes, you’ll need the per-object history and rollback advantages of the state-based approach. Other times, you’ll want the closer control over the deployment code or the dependency ordering that the migrations-based approach offers.

That’s why SQL Source Control and ReadyRoll have been developed to offer a flavor of both worlds with a hybrid approach.

SQL Source Control is referred to as a state-first tool. It’s perfect for state-based deployments, but it also provides the ability to add custom SQL scripts to deployment scripts to avoid losing data during complex deployments.

Similarly, ReadyRoll is referred to as a migrations-first tool. It generates numerically ordered SQL migration scripts that sit inside Visual Studio projects and take database schemas from one version to the next. It also, however, enables stored procedures, triggers, views, and functions to be source controlled as well, avoiding conflicts at deployment time.

So whichever IDE you prefer – SSMS or Visual Studio – and whether you like state-based or migrations-based deployments, you can version control database changes alongside application changes confidently.

And after that? Once your database is under source control, it opens the door to including it in continuous integration and automated release management as well. Making true DevOps for the database possible.

And finally …

This post is based on a presentation I gave at the Computing DevOps Summit in London on March 22, 2017. In the Q&A session afterwards, I was asked what the biggest hurdle to implementing database DevOps was.

The answer is simple: people. The right people have to talk to each other, work with each other, and collaborate with each other. As Donovan Brown, Senior DevOps Program Manager at Microsoft, puts it: “DevOps is the union of people, process, and products to enable continuous delivery of value to our end users.” Note that he puts people first.

You can read more about Tom’s presentation in Computing magazine. And you can find out more about how Redgate can help you extend DevOps practices to the database in our solutions pages.

Tools in this post

ReadyRoll

Develop and deploy databases in Visual Studio with migration scripts.

Find out more

SQL Source Control

Connect your databases to your source control system.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

SQL Data Mask: now featuring configurable masks

SQL Data Mask is the latest prototype to come out of the Foundry. It copies your database while anonymizing personal data. You can use it to mask your databases right now, free of charge.

What we’v...

Also in Database DevOps & DLM

Using striped backups with SQL Clone

If you're a Redgate SQL Backup customer, occasionally you'll need to convert your SQL Backup (.sqb) files to the native SQL Server backup format (.bak), perhaps to perform native database restores on ...

Also in Redgate products

How to document multiple SQL Server databases using SQL Doc and PowerShell

You can use SQL Doc's command-line parameters to automate database documentation, but when you try to automate the process of documenting a group of databases on a server, they sometimes don't give yo...

Also about Database DevOps

Why people flocked to the SQL Clone live stream event

Live streaming events is something that’s rather new to Redgate, the first one being SQL in the City back in December, which was a great success. So with the launch of our new tool SQL Clone looming...

Also about ReadyRoll

Solving Visual Studio database delivery problems with ReadyRoll

For .NET developers working with Visual Studio (VS), the introduction of Database Projects with SQL Server Data Tools (SSDT) brought to VS the ability to manage changes to the database schema and cod...

Also about SQL Source Control

How to version control a database using GitHub and SQL Source Control

Are you interested in version controlling your SQL Server database in GitHub? This article explains some simple steps to create a GitHub database repository and get your database code in there, using ...