Andrea Giardina

24 March 2020

How PASS won the DBTA Database DevOps Innovation Award for fully automating their database deployments

On March 2, DBTA announced PASS as the winner of its inaugural Database DevOps Innovation Award.

Redgate is extremely pleased with the news because we’ve really enjoyed working with PASS, and their team really deserves this recognition for what they’ve achieved since 2018.

The journey at PASS to a streamlined database change process provides valuable insights for IT and development teams interested in introducing DevOps best practices in their database development lifecycle. In this blog post, we share the key learnings from their story, including where they started, the process they designed, and how they implemented it.

More frequent and better database changes

In 2018, PASS realized that managing changes to its large community database of over 300,000 members was difficult. Small ad hoc changes to items like stored procedures were tested locally, tested again in the user acceptance testing (UAT) and staging environments, and then reviewed one last time before deploying to production. At each step, hand-rolled deployment scripts were used, which posed a risk that breaking changes could make it through to production if there was a mistake.

In the findings of the research for the 2020 State of Database DevOps Report carried out by Redgate, two of the top reasons for adopting DevOps are to increase the frequency of delivery and reduce the risk of data loss during deployment. For the same reasons the IT team at PASS decided to redesign their software delivery process in line with essential DevOps practices for high-performing Database DevOps teams.

The team set the objective of breaking down big releases into smaller steps and scheduling updates and bug fixes into fortnightly releases. Increasing the speed and frequency while maintaining quality, however, naturally meant revisiting the process and tools around three areas.

Dedicated development environments

The first step was to give database development a more agile approach. Each developer was equipped with their own database development environment so that they could test their changes earlier in the development cycle. There are two main challenges to this way of working:

• The efficient provisioning of up-to-date copies of the production database in terms of the time taken to provision them and the amount of storage utilized
• The trade-off between data security and data reliability

There are various techniques for provisioning realistic and compliant data for use in testing and development but, as this Redgate whitepaper demonstrates, the use of data virtualization technologies and data masking is the best approach in terms of delivering realistic data while maintaining data privacy to remove bottlenecks.

In order to balance the need to deliver accurate database copies with the desire to provision them quickly and safely, PASS introduced Redgate’s SQL Provision.

SQL Provision enables full copies of SQL Server databases and backups to be created in seconds, using only around 40 MB of disk space, even for databases of 1TB or more. It also masks sensitive data in order to be compliant with regulations like HIPAA, the CCPA and the GDPR, yet does in a way that maintains its size and distribution characteristics, and referential integrity.

The ability to spin up new masked environments in seconds means that the IT team at PASS could experiment and test more freely, without being held back by delays in provisioning or worries about data privacy.

Version control

With developers having their own dedicated environment to work with, the team needed to introduce version control to the database development process to track changes and identify potential conflicts. Redgate’s SQL Source Control plugs into and integrates with version control systems used in application development like Git, Azure DevOps Server and Subversion, so it was a natural choice for PASS.

In order to quickly identify and fix errors before pushing changes to UAT and staging environments, the team also adopted SQL Compare, which automatically highlights differences between database versions.

Automating deployments

The last step in updating the database development at PASS was to introduce continuous integration and automate the deployment process.

Here, another Redgate solution, SQL Change Automation was introduced, which completes the database delivery process by building, testing, and deploying database changes that are checked into version control. This verifies the structure of the database, confirms the changes can be deployed, and creates a tested package that can be executed against testing environments and ultimately released to production.

Using SQL Change Automation, the team are able to automatically generate migration scripts to deploy changes in a reliable and repeatable way that removes human error and results in greater success and less stress when updates go out.

Results

The introduction of DevOps best practices and Redgate automation tools in the database development process at PASS has resulted in a release cycle that is faster and yet also safer. As importantly, the IT team at PASS – and many others – are delighted with the transformation.

“With help from Redgate, we’ve overhauled our database development life cycle,” says Wesley Chang, IT Architect at PASS and the lead of the project. “Changes are tracked, deployments take minutes instead of hours, and databases are compliant with privacy regulations such as the GDPR. Our developers enjoy using the tools and the leadership team are pleased with the results.”

This modern database DevOps approach is now a benchmark for the SQL Server user community that PASS represents, and internally the updated database development practices are being used as the blueprint to improve the application development and deployment process.

Interested in taking the journey to Database DevOps? Visit our solution pages or contact us.

If you’d like to read more about automating the database deployment process, we also have a number of other useful resources including:

• Case study: The benefits of adopting Compliant Database DevOps at PASS
• The 2020 State of Database DevOps Report
• Essential practices for high performing database DevOps teams
• Test data provisioning for development