Database Deployment Automation

Intro to database deployment automation

If you build and deliver software for your company, you know there’s a constant push to go faster while maintaining (or increasing) quality. Things get tough when your team must deliver faster and at higher quality without any additional resources. Add the challenges a database deployment offers, and your database team can quickly become a bottleneck to application releases.

The rise of automation in DevOps

Across industries, software teams have turned to DevOps and Agile processes to get faster application releases and higher-quality code. The name of the game is automation and process re-engineering.

As DevOps continues to spread, organizations are aggressively investing in continuous integration (CI) and continuous delivery (CD) tools as well as specifica CI (or release automation) tools.

While these tools can bring CI/CD to application code, they do not appropriately address the deployment of database changes.

What is database deployment automation?

Database deployment automation is the practice of including databases in the DevOps process and automating it so that the whole pipeline runs more smoothly. It refers to the systematic use of your existing tech stack and specific database tools to manage and execute the deployment of database changes with minimal human intervention, keeping pace with the application and data pipelines that rely on them. This concept encompasses the processes, tools, and practices that enable organizations to release database updates quickly, reliably, and consistently across various environments. It's a critical component of modern software development practices, particularly in agile and DevOps methodologies, where the goal is to streamline the entire software delivery pipeline.

With organizations needing to do better and deliver faster than ever, deployment automation has become key to a competitive advantage. In the broader spirit of CI/CD and application release automation, database deployment automation translates DevOps best practices into the change management workflow associated with application changes requiring database structure or data changes. 

SQL database deployment automation

To bring database deployments into the automated process, each change can be treated as code with relevant change scripts. In SQL databases where this is already happening, the path to integrating the database into the automated pipeline is even shorter. SQL database deployment automation explicitly addresses the automation of deployment processes to automatically apply SQL scripts, schema changes, and data migrations to manage the state and structure of SQL databases across development, testing, production, and other environments.

Database releases without database release automation are a risk

While automated application release pipelines are the norm, most organizations still approach related database changes in a manual, less methodical fashion. Every application change script is checked into version control, providing a single source of truth and initiating a streamlined workflow. These scripts can be checked out of source control and packaged into an artifact, such as a ZIP file with a CI tool like Jenkins. The resulting artifact can be pushed to an artifact repository. Then, a release automation tool can deploy to environments along the release pipeline all the way to production.

While it may seem like database CI/CD can be accomplished with tools like Jenkins alone, organizations are setting themselves up for failure.

The state risk

Since databases have state, database changes must be carefully managed because you do not want to corrupt their state. While it’s possible to replace an older version of an application by overwriting it with an updated version, the same isn’t true for the database. Also, a bad database release can result in data loss for the organization or a major outage for an application.

The data risk

Relying solely on build, configuration, and release automation tools for database deployments puts data at risk. Given the consequences of a bad change, database changes are often handled in a separate, manual process. Organizations are left living with a slower application release velocity and lower code quality inherent with a manual database change process.

CI/CD automation alone is not enough

In the end, if your team relies on build and release automation tools alone, application and database changes will never flow through the release pipeline at the same pace.

Whether it’s developers reworking database changes or DBAs needing to perform a database audit, database deployment automation tools can help eliminate the manual processes that slow releases. Take a deeper dive into how Liquibase fits into the application toolchain.

Database automation in the deployment process (database code)

Here’s how database deployment automation works in the essential parts of your database, application, and data management workflows. 

Manual database change reviews and reworks

It’s not uncommon for a developer to rework a database change prior to a database deployment. Given that databases retain state, reworking a change requires more effort than any other type of code. DBAs often need to manually revert a database environment to allow a developer to rework a bad database change. 

Without manually undoing the change that needs reworking, a different roll-forward is done in lower-level environments such as DEV. Also, a completely different change is applied to higher-level environments that were never exposed to the original change. This breaks the fundamental DevOps concept of “build once, deploy often,” as the deployment to higher environments isn’t consistent with the deployment to lower environments.

Automating database change management and CI/CD

Deployment automation – specifically, database change management automation and DevOps tool integration capabilities – enables database CI and simplifies the process of reworking database changes.  Developers can treat database code just like application code and check an updated version of the database change into source code control during the standardized change management workflow.  A tool like Liquibase gets rid of the separate, manual effort, and allows for a consistent artifact that can be deployed through the pipeline.

Automating database change management involves systematically facilitating database schema and data changes across the development lifecycle. This practice ensures that database modifications are consistently applied, tracked, and documented, facilitating seamless migrations and deployments. Through automation, teams can streamline the process of integrating database changes into their CI/CD pipelines, reducing manual effort, mitigating risks associated with manual errors, and enhancing collaboration among development, operations, and database professionals. 

Similarly, automating database schema migration focuses on managing and implementing changes to the database structure using a migrations-based approach, distinct from the state-based deployment methodology. This technique involves defining schema changes and version-controlled migration scripts that are applied sequentially to transition a database from one state to another. Migration automation streamlines schema changes across different environments, ensuring consistency, reducing the risk of manual errors, and minimizing downtime. Within the broader spectrum of database deployments and change management, schema migration represents a strategic approach aimed at enhancing agility in database operations. It allows teams to methodically manage database evolution in alignment with application development and business requirements, facilitating a more dynamic, responsive IT infrastructure. 

The goal is to make database change management more predictable, efficient, and aligned with agile and DevOps practices, thereby supporting rapid application development and delivery without compromising on data integrity or performance​​.

Validating and versioning database changes as code (shifting left)

Bad database deployments can be costly to recover from and possibly fatal to the organization. Standard CI and CD tools can’t simulate the impact of DB changes before they are deployed. Instead, they blindly apply SQL scripts to environments, which guarantees a SEV1 outage if done all the way to your production server, which can ruin the state of database deployment. Deployment automation provides safeguards in the DB release process so teams can automate database deployments without any risk. In fact, a tool like Liquibase can reduce risk and help achieve much higher success rates on first-time deployments.

Automating version control and validation for database deployments

Once a developer checks in application code, it’s customary for the code to go through a series of automated tests during the build process. Database code is not so lucky — the validation of database code is a completely manual process. Deployment automation tools can codify standards and best practices while automating version control and validation for database change scripts, getting rid of the tedious manual effort otherwise required by database professionals. It also allows developers to get instant feedback on database changes submitted to source code control, just as they currently get with application code.

Automating version control for database deployments involves leveraging tools that seamlessly integrate into development workflows, ensuring that every change made to a database is tracked, versioned, and recorded. This practice facilitates collaboration among team members, simplifies rollback procedures, and enhances visibility across the development lifecycle. By implementing automated version control, teams can efficiently manage database changes, align database state with application code, and ensure consistency and reliability in their deployments. 

Automating validation for database deployments, such as through a feature like Policy Checks, streamlines the rigorous testing and validating of database changes before they are deployed to production. This process includes checking for best practices, performance impacts, security vulnerabilities, and compatibility issues. Policy Checks serve as an early warning system, identifying potential problems when they are easiest to fix. 

By incorporating versioning and validation into the database CI/CD pipeline, organizations can significantly reduce the risk of deployment errors, improve the quality of database changes, and support a proactive approach to database management that prioritizes stability, performance, and security.

Reporting and auditing

After database deployments, you will need to be accountable and monitor problems that may arise. Auditing and reporting on database changes is a best practice, whether operating in a heavily regulated industry or not. Liquibase's database observability capabilities, powered by Structured Logging, give teams the visibility they need to run reports and audits easily. It also enables meaningful workflow insights to empower continuous optimization of the database change process. 

Automation-assisted reporting and auditing, as well as pipeline observability, means database teams can avoid many of the challenging and costly aspects typically associated with the slow, tedious, manual database deployment process. 

Automating auditing, monitoring, and observability for database deployments

DevOps-minded teams can use automation tools to continuously track, record, and analyze database activities, performance metrics, and system health in real-time. These tools also enable trend analysis and easier, more comprehensive audits. By automating workflows and unlocking database observability, organizations can proactively identify and resolve issues, optimize performance, ensure compliance with auditing standards, and gain deep insights into database operations. This enables a more resilient, secure, and efficient database infrastructure, empowering teams to quickly adapt to changes and maintain high levels of service quality and availability​​.

Many organizations handle database schema and logic changes using a lengthy and often costly manual process. It’s important to remember that an expensive task doesn’t just cost resources and time. There are hidden and often ignored unintended costs.

Hidden costs of manual database deployments

Here are the 5 hidden costs associated with today’s approach to deploying database changes.

1. Time-to-market delays

When production application releases are delayed due to manual database deployments, it impacts time-to-market. Customers aren’t receiving new features, enhancements, and bug fixes quickly. This delay results in decreased revenue and even job security. In today’s market, competition is only a swipe away on a mobile device. Because of this, companies can’t afford to be stagnant in their approach to the database pipeline. The delay in the delivery of new application features to the market will mean that you’ll lose to your competition.

2. Remediation

With manual database deployment processes, there are always mistakes. To err is human, after all. These mistakes lead to the need for further repairs. When the database deployment process is completed with no errors, you save time spent diagnosing failed deployments and resolving them. Instead of delivering new features, your teams are fixing unforced errors. This is an unnecessary cost that wastes precious resources and time.

3. Finger-pointing and shifting blame

Covering for one’s mistakes is time-consuming and takes away from far more valuable activities. However, being forced to defend the correctness of database deployments when something goes wrong is even worse.

Finger-pointing often happens with database deployments during a failed environment push. Teams will point to the DB change as the most likely source of the failure. When this happens, it’s left to the data team to prove they did the database deployment correctly, which costs time and money.

4. Interruptions and task-switching

Application developers and DBAs are often walled off from each other in their own silos. Because of this, feedback on DB changes is not provided to the dev team until the DBAs review the change. Reviews happen late in the release cycle. Devs are in a hurry up and wait situation. Thus, the feedback loop is too long. By the time the DBAs provide feedback and request for change, the dev team has moved onto the next sprint. This requires an interruption to the current dev cycle and delays to new features. Not to mention a lot of frustration.

5. Quality of life

Poor performing products, out-of-band changes, finger-pointing, and interruptions impact the entire team’s quality of life. In turn, that can lead to staff turnover, in-fighting, and inwardly focused technical teams. These problems distract from creating and delivering great products that benefit the company.

All these elements show that database deployments’ hidden costs are large and impactful. To improve your processes, you must get rid of friction in database deployments and align database code changes with application code changes to guarantee best practices for database deployment. This gets rid of massive costs and helps the company win against the competition.

To learn more about the benefits of database deployment automation and removing these hidden costs read our white paper, The ROI of Automating Database Deployments.

Best practices for database deployment

The database is a bottleneck to application deployments. In many organizations, database deployments are managed by teams outside of the core application development team. Since the database and application must be released as a unit, this two-path approach to deployments is difficult to manage and can create serious visibility problems. While many developers don’t want to admit it, application deployments are very much everybody’s problem.

Create visibility in all processes to anticipate problems

Limited visibility can increase the cost of database deployment and gives less time to react to problems. Without visibility, we don’t understand the impact database changes will have on the application, which causes us to move slower.

The same is true for database deployments. With more visibility into the impact of database change, you can see and fix problems before they become issues. This also has the added benefit of allowing faster deployments.

Measure every bit of code change

Teams should track every bit of code as it moves through the Software Development Lifecycle (SDLC). With the proper tracking, a team can improve application development thanks to data from:

• User story creation
• Estimation
• Builds
• Deployments

Furthermore, if your team sees a large number of build failures that are delaying application artifact delivery, they can determine the issue and fix it.

The same cannot be said with database deployments. Typically, when a database deployment is needed, someone will create a helpdesk ticket for the DBAs. Then, the team may attach a SQL script or describe the change in the body of the ticket. And then… there is a long wait.

Without insight into how long it takes to accept the ticket, review it, approve it, and schedule for deployment, the entire database deployment process is a black box. No one knows if the process needs to improve acceptance speed or approval speed. Where does management need to apply more resources to improve our process? The answer is a shrug, which should terrify those with P&L responsibility for the business unit building the application.

Automate database deployment to alert team members for problem

Developers are impatient. As Development moves forward to the next sprint, moving back to a previous sprint to fix a database update problem is painful. This not only slows the progress of the current sprint but also forces dev teams to mentally switch to resolve the database deployment issue found by a Production DBA.

This difficulty can be avoided with database deployment automation. The automation process can alert the necessary team members that the DB change was not sufficient. Until then, we are going to have to deal with “out of band” changes and interruptions.

Encourage your DBAs to tell you “No”

By having insight into database deployments, you can see why changes are being rejected and integrate those learnings into your development process.

Waiting until a Production maintenance window to find out if your proposed database changes are acceptable is inefficient. Understanding what is acceptable to DBAs allows Development to avoid wait states and rework.

If your database team has standards, you need to know what those standards are so you can adhere to them. When expectations are clearly defined, those expectations will be met.

However, there are instances of “DBA shopping” in companies with loosely defined standards and uneven enforcement. Development teams will seek out DBAs that are lax or inexperienced for complex deployments. Thinking that the lack of oversight will improve the likelihood of a successful deployment, the dev teams will actually cause more work. Without a knowledgeable DBA, database changes get pushed to production, causing more work down the line.

Encourage hands-on application work until it’s in the hands of the customer

If your application is not in the hands of the customers, they are seeing no benefit from your development efforts. Thus, there really was no point in your making those code changes in the first place. Moreover, app development requires feedback from the customer to improve. That feedback is impossible to receive until the customer is using the application. By relying on a slow, manual, error-prone process to update the database, you slow down your actual database deployments. You must automate the entire application process, not just the application itself.

Create the best team possible

As software becomes more and more linked to business performance, it also becomes more and more complex. No one has complete knowledge of all software systems that a business relies on. This is seen in the software development lifecycle. The further left you are in the SDLC, the more knowledge you have of the application. The further right you are, the more knowledge you have of the environment. By having insight into the later stage environments, especially the database layer, developers can help Production DBAs. With this relief, developers can deliver changes that deploy to all environments in the SDLC.

There will be times that database standards should not be applied to a specific change. For example, if a new column needs to be added with default values, most DBAs will reject the change. In the past, new columns with default values would cause a DML lock on a table while the default value is updated row by row. Furthermore, this sort of change can be viewed by the DBA as code smell. Instead, the DBA may insist that the data insert should be made only by the application code.

Bring DevOps to the database: treat database code like app code.

One of the most integral aspects of database deployment is the culture of database DevOps. Companies that adopt DevOps have a higher market capitalization growth rate (total stock outstanding X stock price) than their competitors. DevOps adopters also outperform the S&P 500.

With DevOps, we’ve seen high-performing IT organizations quickly attacking new markets and expanding rapidly in existing markets. If your company does not adopt DevOps, you will be looking for a new job because your company will eventually fail.

Development teams may look at database deployments as somebody else’s problem or a mere annoyance. But the truth is that the state of the database deployments of an organization can affect a developer’s paycheck. Thanks to database DevOps and CI/CD for databases, jobs are now at risk due to competitive threats from other companies that are more agile from an IT perspective.

There is a solution, though. Automated database deployments or Application Release Automation can help. To complete the task, you must have your DB changes follow the same path as your application changes. That means:

• Using the same source code repository for both database and application changes
• Providing lights-out and single-click deployments for all environments
• Demanding immediate feedback to proposed database changes like you have for your code

If you have already applied these patterns to your application code, it’s time to bring the database deployment into the future the same way.

To learn more about the role of developers in database DevOps read: Making DB Changes Easy for Application Developers. And to get started on your roadmap to CI/CD integration of database deployments, check out our Guide for Bringing Database Changes into Your CI/CD Pipeline. 

Not sure where to start with database automation? Speak with one of our database specialists to discuss your unique requirements and how to build the best database CI/CD foundation for your pipelines.