This post is part of the 119^th #tsql2sday blog party, which was started by Adam Machanic and is co-ordinated by Steve Jones. For more info, check out the website. 

Our host this week is (ahem…) me. Here’s the topic: Changing your mind.

For my contribution, I want to talk about continuous database deployment to production.

Defining Continuous Deployment

A point of clarity, because the terminology is complicated and sometimes used in different ways. I’m referring to this definition of CI vs CD vs CD articulated here by Atlassian:

Continuous integration vs. continuous delivery vs continuous deployment

The important detail is that with “continuous delivery” you deploy to production on demand, typically by clicking a button, but with “continuous deployment” every commit to source control is deployed to production automatically with absolutely no human interaction. Only a failed build or test, or some other automated validation, will stop the production deployment.

Scary huh?

What was my opinion on Continuous Deployment – for databases?

I was terrified. (And I’m an enthusiastic advocate of “Continuous Delivery” and DevOps.)

I was afraid because database roll backs are hard. As a result, Mean Time To Recovery (MTTR) is generally poor for failed database deployments.

I was afraid because even the best automated deployment pipeline, regardless of whether you are using the out of the box stuff, third party stuff or home-grown stuff, was never going to be 100% risk free. And I was afraid because I’ve heard so many DBAs talk about developers who break databases. And I was afraid because most organisations don’t yet have effective automated database testing. This led me the conclusion that the error rate would likely be quite high.

A high error rate, combined with a poor MTTR, isn’t a particularly enticing prospect. I didn’t think I would ever meet any organisation that would be mature enough to make it work. If they did exist, surely they would be inspiring, demonstrating a unique blend of skill, discipline and wisdom.

If I ever did meet this mythical company – I’d be taking notes.

What made me change my mind?

I’ve been working with a customer to improve their change control processes. Here’s a few things you should know about the customer:

• They had no formal change management.
• They worked directly on production.
• Most of the team had never used Git, and were terrified of the command prompt.
• They had no automated tests, and no plans to start.

It sounds scary right. And yes, sometimes things go wrong, and they need to revert to a backup. And they sometimes lose work and data. That’s not ideal.

But let me tell you a few other things about this customer:

• They are in manufacturing, and their databases are monitoring real time, bespoke processes with eye-wateringly expensive machinery.
• Responding quickly, while the expensive machinery was working on a particular order, was essential. The SLA was often measured in minutes, with developers working hand in hand with the engineers on the shop floor.

Sure, it’s easy to jump into our ivory towers and deride them for their direct work on production and their lack of source control – but can you honestly say you can beat them with regard to lead time to production? I doubt it. And for them that’s the key point.

That’s not to say that the production outages and lack of change control isn’t a problem. That’s why they called me in. But a solution that involves complicated branching processes and a huge investment in testing isn’t what they need in the short term. They simply want to know who changed what and when, and they need to be able to roll back without restoring a backup and losing work/data.

I was trying to wrap my mind around the best way to achieve these goals. However, I was set in my opinions about Continuous Deployment. It didn’t even occur to me that this organisation, where they had no automated tests and no real experience with source control, and where the consequences of a screw up in production were so significant, would be that imagined inspirational organisation that might actually be able to crack continuous database deployment.

Each of my proposed solutions was thrown out. They didn’t want to manage the code for different pieces of work in different branches. They could see the benefit of automated testing but didn’t have the time or resources to invest in it right now. They could see the value in setting up automated pre-production/staging deployments, but they didn’t want to implement these things yet because they weren’t the highest priority thing right now.

What they were actually doing, I’ve realised, was being Lean. (They were a manufacturing company. The Toyota Production System was just assumed.) Lean, along with Culture, automation, metrics and sharing (Calms), is one of the pillars of DevOps.

They weren’t saying no to my ideas – just that they were too big. They wanted to make smaller and simpler changes to their working practices, to solve the most important problems, before measuring effect, and considering the next small change. They were forcing me to reduce my batch sizes with regards to developing their dev processes. They had recognised a problem (failing database updates and poor MTTR) and they wanted me to help them to make the smallest and most effective change to help them solve that, before considering next steps.

It turns out, that for this company, the smallest and simplest change was to let them restore production on to their local machines (the data was neither big nor sensitive), check their change into Git using Redgate SQL Source Control, and let TFS (I know, an upgrade to Azure DevOps is on the list) and Redgate SQL Change Automation deploy the code to production automatically.

This was a relatively simple change for the average developer. As far as they were concerned, they just did their work as if it was on production and hit commit and push. Their change would pop up in production a few minutes later with no human intervention. In other words, they were practicing continuous database deployment. This offered them various benefits:

• Several fast and easy rollback options to improve MTTR.
• Ability to lock down the production environments so fewer people had direct access.
• Full audit trail of changes on production, helping them to understand issues, enabling roll-forward and improving MTTR.
• Opportunity to review all changes before committing, helping the team to manage changes and to spot errors.
• Foundations of a more mature process.

Ultimately, compared to where they were (working directly on production), continuous database deployment was (unintuitively for me) the simplest change, offering massive advantages over their previous process with relatively low cost/disruption.

Well I’ll be blown. I had always imagined the company that would make continuous database deployment work. But in reality, it wasn’t that mythical, perfect organisation that demonstrated a unique blend of skill, discipline and wisdom. It was a bunch of earnest folks who recognised that they weren’t perfect, but who were focussed on delivering business objectives and keeping a relentless focus on the most important objectives.

They were inspiring sure enough, just not in the way that I had expected.

What is my opinion now?

Continuous database deployment might not be right for everyone. For some people there might be complicated compliance issues to navigate. For others the risk profile might be different. The solution we put together was made easier due to the size and public nature of the data. But it worked for these people. I no longer put continuous database deployment to production on the utopian pedestal that I used to.

The experience has prompted me to reflect on my ideas about “maturity models” and the order in which things *should* be done.

It’s important to remember that all these labels and concepts aren’t real. Extreme Programming, Agile, DevOps, CI, CD and CD… We made them all up! They are just ideas. Good ideas for sure, but just ideas.

What’s important is to achieve our business objectives. Let’s focus on that. For sure, let’s read the books – there are plenty of good ideas out there. But they are only worthwhile if they help you to meet your business objectives.

I think I was guilty in the past of starting with my ideas about CI, CD and CD, and then applying them to a business in the pre-defined order. What I should have been doing is starting with an understanding about the business, and then considering whether any of the ideas from any of the books would help to solve the business problems – without pre-judging which solution would be the best fit or what order to do the things.

I still think my customer needs to invest in automated testing. But I’ll fight that battle tomorrow. Today they are able to deliver business value to production in minutes, and they have a vastly improved MTTR compared to where they were.

As a first step… I’ll take that.