DevOps for Databases

In a recent Continuous Discussions (#c9d9) video podcast, expert panelists discussed DevOps for databases. Our expert panel included: Brady Byrd, DevOps evangelist and Senior Solutions Engineer at DBmaestro; Himanshu Chhetri, CTO at Addteq; Pete Grant, solutions architect at Cloud BI; Robert Reeves, CTO and co-founder at Datical; and, our very own Sam Fell. During the episode, the panelists discussed challenges for building, testing and deploying database changes and patterns and best practices for reliably deploying DBs as part of your CD pipeline. Continue reading for the full recap of insights from the discussion!

What Makes Databases So Complex?

The lack of standardization is what makes databases complex, explains Wallgren: “What makes databases difficult is there’s just a woeful lack of standardization at the API level. We’ve got SQL 92 and some fairly decent standardization on the query side of things, but when it comes to schema manipulation and DDL, there’s really a lack of standardization. Some databases support transactions on DDL and some don’t, and that’s insanely infuriating for those of us that have to support multiple databases.”

Grant emphasizes the complexity of large databases and information gathering: “Large databases can perform quite differently from small databases. It can be difficult to get complete information in a timely manner. Tests can be slow and it can be also more expensive to do the kind of information gathering that you want.”

It can be a challenge to bring automation into databases, says Byrd: “Where is somebody saying, ‘This is the bright line in the way databases should work, this is how much business logic should go into a stored procedure versus into your object relational code above it’? It’s that very blurry approach to how the database is actually utilized that creates a lot of complexity in bringing a tool or automation approach to it.”

The combination of technical and cultural processes behind databases makes them challenging, according to Reeves: “The database has state, so you can’t just blow it away and recreate the structure without losing the data. Managing change in a way that doesn’t impact the data is very problematic. Combine that with the cultural issues, the silos, it creates a really difficult problem.”

Applying general best practices to managing the complexity of databases is near impossible, per Chhetri: “There are some general best practices that you can apply to tackle a lot of this complexity, but any time you try to design the solution and get into the technicalities, a lot of time you end up implementing something very specific to a particular type of database.”

Challenges with Database Releases at Scale

If you change something on one database, you need to make sure that change is mirrored across all your databases, which is no simple task, explains Grant: “You don’t want to be adding a column or changing a data type on one database that doesn’t match on another one. Some of those changes you want to make quickly. Make sure that if there’s any kind of urgent changes you need to make that you don’t have a different process for your urgent changes versus your normal changes.”

Many large enterprises tend to have geographically distributed teams, making it hard to deploy changes to databases, per Chhetri: “If you look at people side of things, especially in larger enterprise environments, you tend to have siloed DBA groups. In some cases, they distribute across multiple geographies where they are either managing the databases across these geographies as well as trying to coordinate changes across multiple individuals and teams.”

Performance at change time is key, per Wallgren: “Performance is important. I don’t necessarily mean performance at runtime, I mean performance at change time. Because that is going to have a pretty big impact on your cycle time if you have to make database changes as part of your rollout.”

Scripts written by humans can increase complexity, says Byrd: “There’s all the challenges of mixing your DDL and DML, destructive vs. accretive changes, and then there’s the whole problem of how you deal with source control, ordering and dependency. The other thing is that they’re written by people, so they’re not necessarily 100% accurate.”

Complete automation will help solve the complexity of database releases, advises Reeves: “You need to look at every point in your release process and identify where it’s manual, where there requires somebody to look at two screens and compare, make decisions, get involved, etc. – that needs to go away. You need to have complete automation.”

Patterns for DevOps for Databases: Testing, Deploying, Rollback...

Use automated tests, suggests Grant: “Having automated tests on the structure of the database can be quite handy to get around basic tests such as, ‘Is the table there with the right columns?’ You can have that as an automated test just reading the system catalog of the database.”

More emphasis on testing from Wallgren: “Testing is one of the more difficult things to do, especially performance testing. Shift that as much left as possible, because the kinds of fixes that you’re going to want to make in response to performance issues are probably not the kind of fixes you want to do right before you release, or right before you deploy to production.”

Getting all team members on board with automation by proving that it works is key. Byrd adds: “The pattern of building things for consistency and then demonstrating that consistency and advertising it is what’s really going to make an implementation successful.”

Reeves gives simple advice: “Every single pattern you’ve seen that has had success in DevOps for the application, you should apply that to the database, period.”

Some of the best practices for CI/CD can be applied to DevOps for databases, says Chhetri  “Like other general best practices in CI/CD, you need to be able to make changes, you need to be able to collaborate on changes that need to be reviewed by your peers and then instantly be promoted. As part of the pipeline, all of your standard tests should run. If you have the option to do some database-specific testing, those should be part of that as well.”

Some final, simple words on the pace of change from Fell: “You want to allow velocity without getting rid of the safety.”

Watch the full episode.