Grant Fritchey

9 August 2022

1 Comment

DevOps 101: The role of automation in Database DevOps

This is the fifth part in the DevOps 101 series and it’s time to talk about automation. Before we get into it, I just want to recap what DevOps is. Microsoft’s Donovan Brown sums it up nicely in a single sentence: DevOps is the union of people, process, and products to enable continuous delivery of value to our end users. The important thing to remember here is the order in which he talks: people, process, and products. That’s the way DevOps works.

Now that’s covered, we’re going to be talking about automation, and specifically the role of automation within database DevOps. In my mind, there are five goals you want to achieve by introducing automation to database development and deployments:

• Help your development team: By allowing them to get more work done, faster.
• Provide a testing framework: Because it’s not enough simply to automate deployments, it’s also your ability to automate testing.
• Implement common practices across teams: The more you introduce common practices the better, because once you learn how to automate something for one team it enables an easier rollout across your organization.
• Improve auditing efficiency: Automation allows you to ‘defeat the audit’ because you can show the documentation and processes used to manage changes and workflows.
• Add protection to your production environment: You want to empower your development team to deliver faster, but you need to do it while protecting your production servers and your data.

To achieve these goals, we need to gain a deeper understating of automation, including what you need to automate and why, and what the core process is which will set you up for success.

What do you need to automate?

1. Self-service refreshes. Your developers, testers and QA team all need to be able to work with the latest clean and protected data from your production environments, and the latest code. You need to have a simple, effective mechanism that lets these teams refresh their database copies and local development environments, and automation is the key. If you’ve got to rely on your admin or DBA team to refresh copies manually, especially for more than one person or multiple teams, it will slow things down and be a blocker. Self-service refresh is a fundamental aspect of getting this whole idea going.
2. Environment refreshes. Again, talking about the QA team, you need a Continuous Integration (CI) environment with the ability to reset to a known point in time. Whether it’s the current, next, or a previous product release, you need to be able to set up environments in an automated fashion, so that they too are clean for development and testing moving forward. Environmental and database refreshes are fundamental to automation.
3. Deployment changes. Ideally, you want to move code from your laptop into a CI process and from there to testing or QA, then into pre-production or staging and finally production. That requires automation at each step.
4. Testing. You want to build tests that validate your deployments worked, and confirm the new code is working correctly and not breaking your data. You’re going to build testing around all of that.
5. Validation. You’re going to be able to tell through automation that you did this deployment, here’s what it looks like, and yes, it was successful. All of that is going to get you out the door faster and better, and that’s pretty much everything we’re going for.

Why do we need to automate?

We know what we need to automate, but it does raise the additional question: why? There are four powerful reasons to introduce automation.

Empowering development teams

I can’t emphasize enough how important it is that development teams are able to get work done, and move quickly, and that starts with the self-service refresh of up-to-date database copies. Using an old copy of the production database, or a shared copy, means that they can’t guarantee their changes will work when they reach production. The refresh process should also be automated because the more manual steps there are, the more it’s going to slow developers down.

The whole goal here is to move fast, but also allow the development teams to safely experiment and break stuff in a place where it doesn’t affect production. This is the beginning of adding protections to your production environment, setting up a place where they can make mistakes, without affecting the code and processes of other people.

Testing

You’re also going to work with your development teams to introduce CI. This is the one thing that I always stress because once you have CI in place, you can automate the rest of your database development workflow. CI empowers the development teams because they know that as they, and their teammates, make code changes, they are automatically validated against each other through the process.

We should talk about testing rollbacks here as well because, while I’m not a big fan, a lot of places do insist on having the ability to roll back changes. However, what I find is that they don’t think about the idea that the rollback is a deployment, when actually it is. It’s just a deployment in reverse, and you don’t want any of your deployments to be run for the first time in your production environment. You always want to test things, preferably through CI, QA, and pre-production processes. So if there is an insistence on rollbacks you need to have rollback testing as well to validate that it works.

Documentation

Automation requires certain standards and processes; you cannot automate chaos. Just by its nature, developing processes also provides documentation. Every piece of code you write goes into source control and, if you commit the change, it triggers a CI build which is run independently of your development team.

This CI process validates the code and, if successful, you can deploy the build to the QA team, who do their own validations before approving the code. The code then goes to a staging environment with another set of automated tests, and finally the artifact is used to deploy to your production environment.

This process is completely automated and you can hand it to an auditor and say this is how changes get to production, here are the protections and security mechanisms around those changes, and here are the tests that validate them. The whole idea here is that by creating an automated process, you also create documentation that you can share with audit teams, development teams, QA teams and across the business.

Protect your production environment

I want a suit of armor for my production environment, no joke about it. I’ll never run a script for the first time in my production environment, for example. If I’m automating my deployments and processes though, then I also automate all of my script tests, so I don’t have to worry about my production environment as much. When everything is going through testing, even rollbacks, you can set up mechanisms to ensure that you protect your production environment from human error.

To do this, you’re going to have to look at a ‘shift left’ approach. This is a concept that pictures the development pipeline going from left to right. You need to make your non-production environments on the left look as much like your production environment on the right. The idea is that the more they look like production, the more your automated tests validate prior to production, and the more protection you get.

I’m an enormous fan of this whole idea of automation because it protects production environments. This is my driver and what got me to love DevOps: the fact that it gives me protection for my production environment. I come from a DBA standpoint and that means I care more about protecting production than I do about deploying new code, but implementing automation enables you to do both, and that’s the magic.

What is the core process of automation?

All I ask of you is to set up CI. Whatever code you’re working on, make the changes and check it into source control, and let CI do the rest.

The actual code processing is all very manual but, ultimately, let’s say you push multiple changes up to your source control, and that triggers a CI process. The process takes the changes and automatically applies them to a new database that is either a copy of production or a blank database. This part is completely automated and outside of human control and it takes those scripts and deploys them to the database. If it succeeds, it lets everyone know. If it fails, you know immediately so you can fix it.

This is where I implore everyone to get your code changes into source control and create an automated process that pulls it from source control and deploys to a database. If you can do this, you can do everything else. Yes, everything else is a massive amount of work but this is the conceptually difficult part and when you get over this hump the rest is straightforward.

What is the role of automation in database DevOps?

The role is to empower your developers to do their jobs successfully and quickly, and to build the infrastructure that’s going to allow you to automate testing. Automation means you have a documented process and you’re going to be developing common practices that you can apply across teams and expand to the wider organization.

All of this results in a production environment that is safer, and I will never under-emphasize that goal. You can deploy code fast, but also safely, so let’s make our production environment safe, shall we?

If you haven’t read them yet, you can catch up on the other posts in this DevOps 101 series:

• DevOps 101: What, who, why, and how
• DevOps 101: How do you get buy-in from people?
• DevOps 101: How to kick-start your DevOps initiative
• DevOps 101: Introducing Database DevOps
• DevOps 101: Unlocking the value of frequent deployments

Read next

Product learning article

How to Automate Cross-Platform Database Development

In order to focus on their primary task of developing databases, the development team need to automate as many as possible of the routine tasks that are essential for database delivery, such as testing, scripting, version control, documentation, code review, reporting and so on. This article gives some advice on how to do it, faced with the added challenge of needing to use several different relational databases.

Go to the product learning article

Database DevOps Rocks

US TOUR 2023

Learn how to bridge the gap between development and operations - spend the day with our experts at a US city near you...

Reserve your space