28 March 2017

1 Comment

28 March 2017

1 Comment

Moving from application automation to true DevOps by including the database

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 Hub

Tackle common database problems more effectively

While the database is sometimes seen as the blocker in the development process, it doesn’t have to be. With the right knowledge and tools, you can stop problems before they occur, help to speed up d...

Also in Product learning

Get a lot more value from your tools

From provisioning databases through to version control, automated deployments and monitoring, Redgate has a tool for every part of the database development process.

You probably have a favored tool b...

Also in 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 ...

Also about Database DevOps

Where's the Ops in DevOps? Part 3

In this three-part series, guest bloggers from DevOpsGuys look at the real role of Ops in DevOps. Where it changes, how it changes, and why Ops has an important part to play in the brave new world of ...

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

Six reasons to version control your database

For most application developers, it's unthinkable to work without version control. The benefits of tracking and retaining an incremental history of code changes are long understood in the world of sof...

  • Maykel Silva

    Nice Tom, I’m actually searching for the easiest and safest way to change our SVN / self developed script, method of delivery changes for the instances that I manage and your article clarified the things to me, thank you.