DevSecOps automation & observability for SQL Server pipelines: Speed, safety, & scalability

Safely automating high-stakes database changes

An international health and security services organization stuck in manual database change management practices modernized its workflows to adequately serve today’s requirements and scale efficiently. By automating database schema changes into the CI/CD pipeline, the team went from slow, manual, error-prone change reviews to fast, easy, and standardized change management.  

Keeping their worldwide databases updated isn’t just a matter of business continuity. It's a matter of life, health, and safety for the millions of people they track and monitor. If databases are unavailable, inaccurate, or outdated, it risks the organization’s ability to offer optimal care, emergency, and security services. 

Adding to the stakes are the scope, scale, and innovation of its customers. From over 90 countries, its client list includes the majority of the Fortune 500 plus government, educational, and nonprofit organizations. They needed a solution with peak reliability and the capability to protect countless types of sensitive data.   

Automating its database schema changes, the organization slashed its deployment time and manual intervention for peak efficiency, optimal experiences, and room to innovate. They also solidified a DevSecOps culture across globally dispersed teams along the data journey. 

“Things are much easier for the application development and database teams now,” says the organization’s senior release and DevSecOps manager.  

Understanding the burden of manual changes

The shared services team focused on modernizing the database change management workflow included the:

• Senior release and DevSecOps manager
• DevOps engineer
• Build and release engineer

The team actively manages 250+ databases at a time distributed across more than 20 locations. After requesting their required changes, the company’s application developers would submit changes to the database team’s DBAs, kicking off a manual review and feedback process. 

Data impacted by these schema changes typically includes:

• Medical data
• Highly sensitive PII (personally identifying information)
• Financial data for organizations and individuals
• Travel data and itineraries

To adequately protect sensitive data and safeguard system availability, consistency, and accuracy, DBAs took on an insurmountable burden. 

The challenges of database deployment automation

The most pressing challenges for the database team – and the highest-friction issues in the overall pipeline – centered around their outdated change review process. They were stuck with:

• Heavy manual intervention
• Lengthy triaging
• Multiple interactions for every release

Adding to the challenge, the team faced two distinct deployment workflows supporting:

• Agile sprints with deployments to lower environments every 2-4 weeks
• Quarterly waterfall deployments for major releases, including hotfix support 

Deploying database changes to lower environments took the DBA a full day, while production deployments took 2-3 days or more. At a pace of about 100 releases per quarter, multiple DBAs devoted nearly all their time to manually reviewing changes but only achieved an average pace of less than two deployments per day. 

Database changes disrupt and delay the pipeline

DBAs spent 70% or more of their time manually reviewing changes, while also trying to balance the rest of their workload of meetings and other operational duties. This created an endless pipeline bottleneck as the ticket queue grew faster than it could be manually processed. 

Pressures, inefficiencies, and inconsistencies meant that plenty of errors still made it through. 

Governance that protects but delays

Control and governance over database change management were firmly centralized with the DBA team, which enforced strict standards to protect data security and integrity. The team felt they had governance and database compliance “completely locked down” – except that it took way too long. 

While safe, consistent, and standardized, the process followed by DBAs didn't prioritize speed or efficiency. 

“The team reviews each and every script – and it takes forever,” the team explained. 

Not just blind spots but “zero visibility” 

Throughout the tedious manual change review process, the database team lacked any sort of workflow observability for schema migrations. They couldn’t easily track or audit change operations, let alone take measurements of pipeline efficiency or reliability. 

“We had a major blind spot on the process, so we spent way too much time reading tickets and intensively tracking down root causes,” explained the team’s DevOps manager. 

Managing hundreds of SQL Server pipelines

Managing hundreds of Microsoft SQL Server databases at any one time, the global company’s DBAs struggled with manual change deployment. The legacy process had them:

1. Receiving tickets from application development, database, or DevOps/DevSecOps teams
2. Individually and manually reviewing every script 
3. Adding comments with feedback 
4. Sending back to the team for revision
5. Approving and manually deploying changes
6. Monitoring every change for errors
7. Manually rolling back the database change if it causes errors

Deployments to lower environments typically included two to three change requests plus an additional one for the production environment. In total, each release saw four to five tickets – if everything ran smoothly. Errors and revisions often extended the process even further. 

Elsewhere in the pipeline, the team also uses DevOps tools including:

• CircleCI (Build & CI/CD automation)
• JFrog (artifact repository)
• ServiceNow (ticketing)
• DBeaver (SQL editing)
• Bitbucket (source control)
• Grafana (Observability)

While rollback rates stayed below 1%, the team worked through substantial reworks for neary every change. Agile deployments stressed the team, but major waterfall releases with 10+ tickets took that pain to another level. With even more at stake, scrutiny dialed up but speed hit a crawl. 

For its database DevOps initiative, this global database team needed to turn this completely manual process into a reliable automated workflow – then repeat that success across hundreds of additional and evolving targets. 

With complete automation, governance, and observability for database change management in the team’s sights, they turned to Liquibase. Working with database DevOps specialists, the team laid out its business and technical goals before implementing the automation platform. 

Goals and requirements for database DevSecOps 

In conversations with Liquibase’s solution engineering team, this global health and security services company outlined its priorities and requirements for database change management automation. 

Business Goals

Achieving the following goals would help this shared services team best support their business’s bottom line. 

1. Automate the database change process to reduce release timing from days to hours
2. Significantly reduce manual DBA efforts to free up the 70%+ of DBA bandwidth dedicated to it
3. Increase release frequency to go from one release per day to smaller, more frequent deployments
4. Streamline governance to accelerate reviews without sacrificing quality, security, or integrity of highly sensitive data
5. Establish database change tracking and monitoring for easier audits, resolutions, and analyses

Requirements 

The multi-perspective team of build, release, and DevOps engineers and managers rallied around a tight yet critical set of requirements, including:

• Centralized management of database change automation with DBAs
• Scalability to grow with expanding data sets, additional databases, and new data store technologies
• Efficient integration into the CI/CD pipeline for optimal visibility and ROI

Technical Goals

The team’s goal cleary translated into technical considerations for the implementation and rollout of Liquibase as their database DevOps solution. These included:

• End-to-end database deployment automation that entirely replaces the manual workflow
• Minimized deployment time with a duration constrained by necessary tooling and testing, not individual DBA workloads
• Reliable validation and error detection with automated testing scripts that eliminate errors, provide immediate feedback, and align with compliance guidelines
• Database observability through change operation tracking and integration with existing CI/CD monitoring dashboards

Requirements

Bringing the database deployment process closely in line with application releases drove the team’s technical requirements. As they planned implementation, they carefully strategized to:

• Support the 2-4 week agile sprint release schedule
• Support the quarterly waterfall release schedule
• Maintain optimal SQL Server environments and best practices
• Leverage and integrate CricleCI and JFrog for CI/CD inclusion
• Package and deploy in one step in Jenkins and ServiceNow
• Align with robust data security and compliance programs

These business and technical guidelines posed no problem for Liquibase, which slots in neatly to existing automated pipelines to support rapid software delivery and modern data management. 

Automated self-service deployments for 250+ SQL Server pipelines

Targeting automation and DevSecOps solutions for their database change management workflow, the shared services team (database, build, release, and DevOps) analyzed their options and selected Liquibase. Starting with five Liquibase Pro licenses, they initiated a seamless implementation process that aligned tightly with the organization’s broader DevOps ecosystem. 

The organization used Liquibase to implement automated, standardized, end-to-end database change processes. Each database now follows a documented process with clear pre-requirements. Each team uses templates, where a few lines of code are added to each SQL script, ensuring consistency across deployments. 

A checklist is provided to ensure all standards are met, streamlining the workflow and reducing errors. This structured approach significantly improves compliance and governance, automating much of the previously manual validation and review process. The team also worked with Liquibase’s Professional Services to outline and develop custom Quality Checks that suited their unique use cases. 

Liquibase seamlessly integrates with CI/CD tools like CircleCI and JFrog, enabling automated, continuous database changes that mirror modern application release pipelines. By automating database version control with Liquibase, these teams track every change across environments, providing full visibility into who made what change and when. This versioning capability not only improves collaboration but also ensures easy rollbacks and compliance audits when necessary.

Liquibase’s ChangeSets further empower teams by allowing controlled, incremental changes, which are tested and validated automatically in the pipeline. This reduces manual, error-prone processes while ensuring all changes are properly tested before reaching production. Combining these features with existing CI/CD tools, Liquibase enhances the overall reliability and speed of database releases, offering both immediate feedback and robust error detection at each stage of the process.

The Liquibase-enabled change management process now flows as:

1. Developers modify the SQL scripts
2. Development lead creates and merges the pull request (PR)
3. CircleCI triggers and extracts database changes, then runs validation scripts
4. JFrog validates packages, and logs are created and shared with development and DevOps teams
5. If any criteria fail, development teams are notified for review
6. Once approved, the DBAs validate the log, and the application team creates a ServiceNow ticket
7. DBAs deploy the changes, and CircleCI automatically closes the ticket

This resulting workflow brought the team massive reductions in errors and deployment time, achieving their goals of a faster, safer, more reliable, and easier-to-use database deployment workflow. 

“Liquibase introduced a standardized and automated process for database deployments,” the shared services team says. “It was a simple implementation that aligned with our DevSecOps strategy.”

Results: Database DevSecOps enables better, faster deployments 

After automating database change reviews and deployments across 250+ SQL Server environments with Liquibase, this global health and security services firm achieved game-changing reductions in errors and deployment timelines that boosted overall business results and team satisfaction.

Primary results and benefits

Automation

• Change management automation for 250+ databases
• 70% less DBA time spent on change reviews

Acceleration

• 8x faster deployments
• 4x more frequent deployments

Quality

• 60% fewer changes needing review
• Less than 10% error rate

Deployments that used to take days now take merely an hour or two – 8x faster. Previously rampant, errors and reworks are now down to only a 10% error rate. Most importantly, there’s no longer a disruptive bottleneck at the change review stage. 

In their first fully automated quarter, the team launched more than 400 releases – 100 production deployments and 300+ lower-environment deployments – using the Liquibase-managed workflow. Compared to only 100 total deployments a quarter previously, the team achieved a roughly 300% (4x) improvement in deployment frequency.  

The shared services team has strong trust in database compliance thanks to customized validation scripts that shift-left DevSecOps and checks against high-stakes regulations and privacy concerns. The consistent, standardized processes also mitigates disruptions and simplifies resolutions when something problematic does occasionally slip through. 

Creating space to innovate and observe

These build, release, and DevOps/DevSecOps also established database observability with Liquibase, thanks to detailed Structured Logs. They can easily analyze metadata for every change operation and build out automatic reports to support monitoring and continuous workflow optimization.

All of these speed, quality, and optimization benefits stack up to allow DBAs – plus the build, release, and DevOps teams – to focus on innovation and efficiency, not the burden of manual reviews, revisions, and deployments. Now, they’re focused on the next phase of their database DevSecOps journey: leveraging Liquibase’s observability capabilities to bring deployment metrics to pipeline monitoring dashboards. 

With database changes automated, governed, and tracked, the DevOps, build, and release teams can visualize and monitor success/failure rates and deployment frequency over time. With this data, provided by Liquibase’s Structured Logging, they easily communicate the value of Liquibase to senior managers and teams who have yet to automate. By customizing their logs and adding custom labels, they’ve even been able to track groups of deployments, too, for nuanced perspectives. 

Not only does this international health and security organization have strategically controlled database change management automation – they have a database DevSecOps observability that quickly shows their improvements:

• Faster change processing
• Fewer errors
• Higher success rates
• Increasing volume of releases

Ready to start your own journey to complete database change automation, governance, and observability?  Get your on-demand demo of Liquibase.