PASS Implements Modern Database DevOps Approach with Redgate Software

Page 1 of 2 next >>

An independent, not-for-profit, user-run association, PASS was co-founded in 1999 by CA Technologies and Microsoft to promote and educate SQL Server users around the world. Since then, PASS has expanded and diversified its membership and is now a global community for data professionals who use the Microsoft data platform. Its mission is to empower members to connect, share, and learn through networking, knowledge sharing, and peer-based learning. It encourages the exchange of information through local and virtual groups, online events, local and regional events, and conferences; delivering high-quality technical content for in-depth education and professional development.

PASS has a large website which is used by its 300,000 members around the world to access webinars, recordings and resources, book conferences and events, and get involved with the community. The website is constantly evolving and a team of developers work behind the scenes to support it, add new features, and improve the experience for users. Developers also work on maintaining and updating the SQL Server database at the back end which collects, processes, and stores the constant stream of data generated by member activity.

PASS is a 2020 DBTA Database DevOps Innovation Award Winner 

The Challenge

While the group was confident in its ability to enhance and improve the events, content, and learning opportunities PASS offers, it had two big concerns with the database.

First, making changes to the database was a cumbersome process. Small ad hoc changes to items such as 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 scripts were used, which posed a risk that breaking changes could make it through if there was a mistake.

Second, the implementation of the EU's GDPR was fast approaching. PASS was already familiar with the challenges of meeting requirements of legislation such as Canada’s Anti-Spam Law, which prevents the distribution of unsolicited or misleading emails, social media posts, and text messages. The reach of the GDPR, however, was understood to be far wider and deeper, and would require personal data to be protected in development environments with measures such as pseudonymization.

For PASS, this was a potential problem because, like many other organizations, its development team was using up-to-date copies of the production database to test proposed changes against. Using a copy with a limited, anonymous dataset is possible, but inevitably this means changes are tested with a database that is neither realistic, nor of a size where the impact on performance can be assessed. This could result in the increased likelihood of breaking changes hitting production, which would further exacerbate the issues it already faced.

Page 1 of 2 next >>