3 Solutions for Database Drift
Database deployments should be smooth and painless. Even in 2020, they often are not. Sometimes, the culprit is database drift.
What is database drift?
Database drift (or database version drift) is a way to describe when the database schema for a DB in one environment no longer matches the schema for the same database in a different environment. It could describe schemas for the same database in Test vs. Prod or in Prod vs. source control.
Why does database drift happen?
Database drift happens because hotfixes and patches are often applied to staging and production systems in panicked efforts to rapidly restore functionality or address critical system flaws. Often, these late-night, last-minute changes are not properly documented. Nor are these changes applied to lower environments. They are often at the heart of show-stopping surprises during later production deployments. In the worst case, database drift can result in data loss. In the best case, it is a delay and time lost reworking a failed execution.
Some fixes are as trivial as adding a missing index or making a small change to a database object.
Ideally, it shouldn’t happen. But, most of us don’t work with ideal database processes.
3 Solutions for dealing with database drift
So, how does one get rid of or mitigate database drift? It’s the timeless, basic recipe of organizational change, process change, and proper tooling.
1. Create transparent organizational structures
Organizationally, teams need to be more transparent and cooperative as changes flow from development to production. Database administrators (DBAs) might sit on a separate team and act as a shared service to different application teams that all rely on the same database. But you can’t just fire off help tickets and expect that everything will turn out okay. Instead, you have to create open, transparent communication between development and operational teams. You have to build a shared sense of responsibility for database change deployments in all environments – production or otherwise.
2. Address “quick fix” processes
In addition to organizational habits, you have to adjust processes. Hotfixes and patches are often made in a hurry to higher environments because of a mismatch between application and database code changes or because of issues that were discovered very late (often in production). To prevent such scrambling, organizations need to invest in a more robust testing process. Teams need a unified and transparent path for application and database code as it moves from development to production. So long as application and database code take separate paths to production, there’s a risk of mismatch or error. Separate paths lead to the conditions that produce database drift and service outages.
3. Automate systems wherever possible
Database drift can be addressed with appropriate automation solutions. Manual DB deployment processes yield poorly documented, custom, hard-to-reproduce deployments that contribute to chaos and confusion. By investing in automation tools that can verify database code changes and package verified changes into an immutable artifact for downstream deployment. Database deployment automation can also perform transparent, auditable, and repeatable deployment, ending most of the database drift. And, with tools that can snapshot and compare databases as part of an automated and regular process, you can quickly catch any “out-of-process” changes and fix problems fast.
Automatic database drift detection with Liquibase Pro
The first thing to do to automatically prevent drift is to detect when it’s happening at scale. Liquibase Pro offers automatic database drift detection. Here’s how it works.
First, get a free trial to Liquibase Pro. (No strings. No credit card needed. Just try before you buy.) Once you have the trial license key, add it to your liquibase.properties file.
Now you can run the
diff command as usual to compare two database schemas but this time add
--format=json like so:
liquibase diff --format=json
Now, you’ll receive a structure JSON output object that lists the differences between the two databases (as configured in your liquibase.properties file or Maven POM file). By default, the result outputs to
STDOUT, which provides maximum flexibility to parse the result and share it with other tools. You also have the option to output as a collection of files:
liquibase --outputFile=myfile.json diff --format=json
I have the JSON file. Now what?
Given that JSON can be understood by machines, you’ll need to write some code to parse the JSON and act on what you find. There are a lot of possibilities here that can easily match your workflow policies and prevent bad database deployments.
Examples on how to automate with Liquibase Pro and help your team
- Look for missing elements and automate running
diffChangeLogbetween the source and target databases.
- Use JSON to count the number of differences and set a priority to the source-target pairs, pushing those with the most drift to the highest priority.
- Look for a critical column or table. If the critical, high-value element is in the JSON diff report, you could halt your CI/CD process before you run an update and trigger a notification to your team.
Once you have a file, you can process the data to generate reports, trigger actions, set up alerts, or whatever makes sense to make the process more transparent and easily shared with your team to ensure that your databases stay in sync.
Resources for Liquibase Pro’s automated drift detection
- Watch the diff JSON demo on YouTube
- Check out a quick Python setup for automating database drift diff reports.
Need a more powerful solution?
If you’re in a heavily regulated industry or in an enterprise where you need to prevent drift from happening in the first place, learn more about Liquibase Enterprise. It offers a fool-proof database forecast and a full reporting audit trail to help ensure database deployment success.
Get a handle on database drift
Ultimately, market expectations have evolved making it necessary for teams to deliver new capabilities more quickly and at a higher quality. It is critical to ensure that database drift doesn’t creep up while attempting to speed up software releases. Database drift can do major damage in the form of service outages, brand and reputation damage, data loss, and increased time to resolution. Without making the necessary changes in culture, process, and tooling, attempts to speed up software delivery will fail.
Automate BigQuery schema change and version control with database DevOps
Google's BigQuery is a fully managed, serverless cloud data warehouse, or database as a service (DBaaS), that brings unparalleled scalability and convenience to data analytics.