DevOps and Databases — The forgotten automation

How to bring automation into managing stateful databases

Tj Blogumas
Apr 29, 2020 · 8 min read
Image for post
Image for post
Source: https://www.xenonstack.com/wp-content/uploads/xenonstack-devops-for-databases.png

Do you see that guys? Are we really not going to talk about it? That gigantic elephant is literally standing right in the middle of our conference room and everyone is just going to pretend like it doesn’t exist?

I’m of course referring to the Database aspect of your application. That part that no one wants to bring up when we talk about DevOps. I know guys, it’s scary. Databases hold all this data in some form of state and well, the entire DevOps ecosystem has built around the premise of stateless applications.

A 2019 State of Database Deployments in Application Delivery report found that for the second year in a row, database deployments are a bottleneck. 92% of respondents reported difficulty in accelerating database deployments.

Have no fear my fellow technologist, it’s 2020 and we can automate the database! Let’s dig into some of the fun that is to be had when DOing the DB!

Image for post
Image for post
Photo by Campaign Creators on Unsplash

For many DevOps enthusiast such as myself, database stuff is probably the piece of technology you are least familiar with. What the heck is a SQL statement? Why can’t we just do select * from <table>; for every query? We need to know the areas of concern so we can even begin to address them.

Some of the areas in which database automation gets tricky are:

  • Installation and Configuration of a Highly-Available Database cluster in every environment
  • Managing and securing the actual data itself (we can’t just export/import financial or user data from prod)
  • The risk associated with schema changes, particularly data destruction
  • Size of data that needs to move from 1 environment to the next
  • Secrets management for connectivity and permission to databases
  • The potential for conflicting changes introduced from different sources (any developer could need a new change to the DB for what they are working on)

Wow, those are actually some big problems to tackle. Well let’s take those concerns and figure out what our options are one by one, shall we?

Image for post
Image for post
Photo by Obi Onyeador on Unsplash

This is actually probably the simplest issue to address as it relates to automating the database because it is no different than what we do for middleware.

Traditional On-Prem Solutions:

  • I would suggest using Chef to write the configuration of my database server, especially if you are using Windows SQL Server. Chef works great in a stateful environment, which is the very definition of a database.
  • DO NOT concern yourself with automating in-place upgrades. In-place upgrades you’re going to back up the database first before attempting right? Which is time-consuming and pretty risky. Instead, focus on your upgrade logic attaching a shared data mount from the old server, and stand up a new server with a fresh installation of the new version. This allows for a much quicker cutover on traffic and maintains the current running infrastructure in case you need to back out the upgrade.

Cloud Solutions:

  • Cloud is great because a lot is managed for you. Write your Terraform or CloudFormation for standing up your RDS instance with the appropriate security groups and authentication details.
  • Auto-Generate a random string during database provisioning and store the credential in a secrets management/vault solution as to never hardcode a password anywhere. For tooling to access the database, have them lookup in the vault for the specific value.

Local Dev Environments:

  • Regardless of which database technology you use, they can all pretty much be configured in a docker container. Make sure your HA configuration works in a containerized version so devs can make their mistakes locally before touching your static database environments.
  • Have 2 versions of your database containers. 1 that is empty and free of data but a current version of the production schema. 2 that has the production version of your schema but populated with faker data.

You can use a tool like Faker for creating mock data in your database.

Here’s a Javascript example of this:

Image for post
Image for post
Source: https://spirion.com/wp-content/uploads/2019/10/SPIRION-Data_Lifecycle-2k-1024x520.jpg

One of the top security concerns relating to databases is the information getting leaked or misused. This is why I am absolutely against backup and restore processes that run with production data. However, it is vital to have similar data and volume of data in a pre-production environment, especially for proper load testing.

Choose one of these approaches:

  • Export and sanative production data into a QA database. The downside to this is for every new schema change you must also ensure sanitization scripts are updated which makes it hard to bundle those changes together.
  • Have a job that runs a diff in the database size every 24 hours to see how much new data has been generated. Then use a Faker script to populate QA with the same amount of records.
Image for post
Image for post
Source

This is the most important and vital thing to get right when Automating your database. There is actually one simple strategy to getting this right and it’s very easy to implement with a tool such as Flyway.

  1. You absolutely have to version your schema. Every change is a new version. This allows your automation to know what state the database is in and then run each appropriate script from there in order to upgrade it to the most current version.
  2. Never do a straight rename or delete a column. Always create the new column, copy the values, and then delete an old column. This ensures you can run the script backward and forwards without losing your data.

Flyway is also cool because you can embed it in the application. As such, when a new version of the application is deployed the Flyway logic will query the database to see what state it is in and run any migrations necessary to get it to match the same version of the application.

A sample of what that looks like is below. Here is my sample migration package:

I can compile my project

bar$ mvn compile

This is now the status

bar$ mvn flyway:info

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO]
+-----------+---------+---------------------+------+---------------------+---------+
| Category | Version | Description | Type | Installed On | State |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1 | Create person table | SQL | 2017-12-22 15:26:39 | Success |
| Versioned | 2 | Add people | SQL | 2017-12-22 15:28:17 | Success |
| Versioned | 3 | Anonymize | JDBC | | Pending |
+-----------+---------+---------------------+------+---------------------+---------+

Note the new pending migration of type JDBC. It’s time to execute our new migration. I can run:

bar$ mvn flyway:migrate

This will give you the following result:

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO] Successfully validated 3 migrations (execution time 00:00.022s)
[INFO] Current version of schema "PUBLIC": 2
[INFO] Migrating schema "PUBLIC" to version 3 - Anonymize
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.011s)

And you can check that this is indeed the new status:

bar$ mvn flyway:info

[INFO] Database: jdbc:h2:file:./target/foobar (H2 1.4)
[INFO]
+-----------+---------+---------------------+------+---------------------+---------+
| Category | Version | Description | Type | Installed On | State |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1 | Create person table | SQL | 2017-12-22 15:26:39 | Success |
| Versioned | 2 | Add people | SQL | 2017-12-22 15:28:17 | Success |
| Versioned | 3 | Anonymize | JDBC | 2017-12-22 16:03:37 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

As expected we can see that the Java-based migration was applied successfully!

Image for post
Image for post
Photo by Kolar.io on Unsplash

This is another one of those problems that can be tricky to solve. Performance testing an application and evaluating a system that has a large subset of data is something you can definitely automate but not typically done in the standard CI/CD release workflow.

Different Approaches:

  • One way to approach this would be to ensure your database is configured in such a way that the actual data is mounted to a different filesystem than the installation of the database. This would allow you to backup/copy/restore a filesystem to another running database executable without the need to mess with the runtime database itself.
  • My recommendation is to again use a tool like Faker for generating significant amounts of data for performance testing runs. It may take a bit longer to set up but you can ensure good, clean data is in your database and your tests are evaluating against the real scenario and not also having to troubleshoot data integrity issues.
Image for post
Image for post
Photo by Kristina Flour on Unsplash

This is actually not that hard to solve if you have a solution like Hashicorp Vault or working in AWS RDS. We use terraform to generate a randomly generated string that populates a secrets manager store which takes the need for humans to ever know or touch the secret out of the equation.

Here’s an example of what that looks like with Terraform:

There are lots of additional complexities that need to be thought through when automating the database in a DevOps world. Too many organizations leave the database as an afterthought and allow DBA’s to continue to manage them the same way they have for decades now.

It’s 2020 guys, we have the technology and we have the people. Challenge your team to think about the most critical and valuable part of any application, the data!

Happy Automating!

By the way, 👏🏻 *clap* 👏🏻 your hands (up to 50x) if you enjoyed this post. It encourages me to keep writing and help other people finding it :)

Please follow 👉 Tj Blogumas for more Awesome DevOps Stories!

Like our Facebook or Twitter page as well!

Stories for anything and everything DevOps

Thanks to Zack Shapiro

Tj Blogumas

Written by

DevOps Architect @ a large financial institution. DevOps practitioner for over a decade. Technology and Automation enthusiast.

DevOps Dudes

A collection of stories that have anything and everything to do with DevOps from horror stories to success stories. If it's about Gitlab, Jenkins, Chef, Ansible, AWS, Azure, Kubernetes, Software Engineer then it belongs here.

Tj Blogumas

Written by

DevOps Architect @ a large financial institution. DevOps practitioner for over a decade. Technology and Automation enthusiast.

DevOps Dudes

A collection of stories that have anything and everything to do with DevOps from horror stories to success stories. If it's about Gitlab, Jenkins, Chef, Ansible, AWS, Azure, Kubernetes, Software Engineer then it belongs here.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store