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:

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 silos. 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.

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:

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:

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.