Rebecca Edwards

25 February 2019

How PASS introduced compliant database DevOps

In 2018, the Professional Association for SQL Server (PASS) was faced with the challenge of putting into place the processes that would enable it to comply with the GDPR as well as upcoming legislation like the California Consumer Privacy Act (CCPA) and New York’s SHIELD Act. Each of the regulations affects any organization that collects, processes and stores the data of European, Californian and New York residents respectively. Personally identifiable information has be protected, access to it restricted, and organizations have to demonstrate they have made every effort to comply.

With over 300,000 members and more than 250 local PASS chapters around the world, PASS has a large database that is constantly being accessed and updated through its various websites. That database contains the very data the new legislation is designed to protect.

The IT team were already looking at how they could improve the process for deploying changes to their database, and meeting the new regulatory demands added another burden to their workload.

Their database development process was, like many organizations, slow and problematic. Small changes were tested locally and in testing and staging environments, and then reviewed before deploying to Production. At each step, hand-rolled scripts were used, which posed a risk that breaking changes could make it through if there was a mistake.

And then there was the issue with the new requirement to protect the privacy of personal data and restrict access to it. Like many developers, the team at PASS like to use a copy of the Production database in development to test proposed changes against. Using a copy with a limited or anonymous dataset is possible, but inevitably means changes are tested with a database that is neither realistic, nor of a size where the impact on performance can be assessed. This can result in errors reaching Production and causing unintended problems when they’re deployed.

As the Professional Association for SQL Server, they also wanted to do more than simply improve their database development process. They wanted to demonstrate best practice and be a role model for others to emulate.

Their goal was to take a DevOps approach and introduce a database development pipeline which would automatically merge changes and generate the required deployment scripts and, at the same time, be compliant with data protection regulations.

That’s quite some ask.

They were already familiar with Redgate’s SQL Toolbelt, which contains many of the industry standard tools for database development, and they were particularly interested in Redgate’s SQL Provision solution. SQL Provision enables fully masked copies of Production databases to be provisioned in seconds and, importantly, also uses Microsoft’s built-in virtualization technology to reduce the size of those copies to around 40MB, even for a 1TB database.

Working with Redgate, PASS introduced version control to the database development process, integrating the new tool with Team Foundation Server (TFS) which was already being used in application development. This stopped the merge conflicts that were happening previously, and Redgate tools were then used to automate the generation of migration scripts, transforming deployments from long, worrying evenings to a smooth, streamlined process.

Importantly, the team also decided to simplify the provisioning of database copies with SQL Provision. Developers can now test their changes against their own copies of the Production database which, while masked, are fully representative of the real database and can be provisioned to them in seconds.

This new way of developing the database has transformed the way the IT team works. They have moved from frustrating, worrying and time-consuming database deployments every few months to error-free releases every two weeks.

It has also inspired them to look into how they can apply similar processes to the way they develop and deploy applications in order to gain the same advantages. So at PASS, rather fittingly, it’s the database that’s taking the lead when it comes to adopting modern development processes.

If you’d like to see how a compliant database DevOps approach can help you release changes faster while keeping your data safe, visit Redgate’s solutions pages.