The organization also needed to move away from the shared development model, with every developer accessing and updating the databases in the UAT and staging environments. Instead, it wanted each developer to have their own copy of the production database to test changes against, with the ability to merge the changes later. Every copy, however, would need to be sanitized of all personal data and any financial details.
The hand-rolled method of creating deployment scripts also had to be replaced with a process that would protect data while minimizing potential errors.
Goals
Discussions at PASS on how to resolve both issues led the group to the conclusion that the database development process had to change. Rather than making small changes, though, it wanted to use the opportunity to incorporate a DevOps approach.
The Database DevOps initiative—championed by Wesley Chang, IT architect, PASS, and Craig Ellis, global alliance manager, PASS, was begun in early 2018 in order to be compliant with GDPR (which took effect on May 25, 2018), and gradually rolled out across the teams.
The initial goals were the following:
- To give every developer their own dedicated database environment
- to streamline the deployment process
- To cut down the manual deployment effort
- To protect and improve the integrity and safety of member data
The route was to introduce third-party solutions from Redgate Software to enable dedicated developer environments, introduce version control, automate the database development pipeline, and mask the data in it. This would handle the merging of changes and generate the deployment scripts required and, at the same time, maintain compliance with regulations such as GDPR and the upcoming California Consumer Privacy Act.
The Impact
What started as a conversation about deployment and data privacy concerns at PASS has resulted in a new database development and deployment process that is streamlined, trouble-free, and, importantly, compliant with GDPR and other legislation. Developers 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.
Version controlling the changes has eliminated the merge conflicts that were happening previously, and the automated generation of migration scripts has transformed deployments from long, worrying evenings to a smooth, error-free process.
Metrics
In the past, PASS released major CMS version upgrades that involved critical database changes no more than once a year because it was such a cumbersome process. Specifically, the effort of creating multiple test databases from production backup just for trial upgrades and QA used to take weeks. This usually stretched the entire project timeline into months. Those, as well as smaller, ad hoc releases, involved many people and the organization had to pick the time for those carefully, taking into account expectations about when members would be accessing the website.
Now, given the ability to quickly create cloned databases, the QA process for testing major version upgrades can be shortened into days. For smaller incremental database changes, PASS releases every 2 weeks and it’s a routine, business-as-usual process. There are rarely any problems but more importantly than that, everyone has confidence the releases will just work.
Lessons Learned
According to PASS, through the DevOps implementation it has learned three key lessons.
First, breaking the development process into smaller steps and working in 2-week sprints with regular releases is faster, more efficient, and results in far fewer errors reaching production. Second, automation has taken away a lot of the manual steps that caused frustration, and has made the deployment of scripts far more reliable. And finally, it’s better to introduce Database DevOps in stages. PASS started with dedicated development environments and version control, then moved to release automation, and now it is looking at how it can enhance and improve its automated testing.
What's Next
Removing the database as a blocker in development and deployments enabled PASS to do more, faster, but at the same time it was making it harder to identify and tag sensitive data collected by new features being introduced. To resolve the issue, Redgate’s SQL Data Catalog was introduced, which gives the organization a clear picture of the SQL Server data in its estate, uses automation to speed up data classification, and enables reporting on the scope of sensitive data in its databases. It also enables PASS to verify that development and test data has been masked correctly and provides an audit trail should it need one.
In addition, the new way of developing the database has inspired PASS to look into how it can apply similar approaches to the development and deployment of applications in order to gain the same advantages.