James Murtagh

9 April 2019

Easing the transition from shared to dedicated database development

Working in dedicated development environments for the database is the ideal for many. This is the message we frequently hear throughout the industry from thought leaders, at conferences, and in many written pieces about implementing best practices. Yet 70% of developers still work in shared environments, as highlighted in the 2020 State of Database DevOps Report, despite the rewards that can be gained by having a dedicated copy of the database to work with and test any proposed changes against.

Shared environments can be made to work, provided you’ve made proper use of schemas in the database design, and grouped together database objects that support each logical area of the application’s functionality. Otherwise, developers can often overwrite each other’s changes or code against something written by another developer that is only partially complete. Open communication is crucial for this to really work which can be a challenge, particularly if you have distributed or remote teams.

More importantly, working with a shared environment affects a team’s ability to carry out experimentation and value-added work. Experiments are designed to break things and, by doing so, uncover solutions. All of which is fine if developers are working in a sandbox environment where their work is isolated from the rest of the team, but it can cause huge problems if it affects what everyone else is working on.

As teams look to deliver more value to their customers, faster, and through ever shortening release cycles, they’re increasingly looking at the working practices and tools that will allow them to move to a dedicated development model in an efficient and secure manner.

Source controlling changes, for example, enables developers to work in parallel without the risk of overwriting each other’s work and disrupting longer term development with quick fixes and ad hoc tasks. Similarly, giving each developer their own dedicated, up-to-date copy of the production database brings in the freedom to try out new things, risk-free.

But while the benefits are clear, transitioning from a shared to a dedicated development model raises a number of challenges.

Infrastructure costs

Let’s say, for example, you have a shared development model with a 2TB database and a team of 10 developers. Moving to a dedicated model means expanding that 2TB to 20TB worth of infrastructure which you now have to find, allocate and pay for. This is getting expensive quickly and sign-off for this kind of investment could be difficult. Thankfully, there are options already available in the market to help overcome this first stumbling block.

By using virtualization technology, like that which exists within SQL Clone, you can minimize the network storage footprint and subsequent infrastructure costs dramatically. SQL Clone creates copies of a source database, or a backup, and requires only around 40MB of disk space for each copy, even for a database where the original size is 2TB. Those copies, or clones, still look and act like full databases so developers can work in their own sandbox environments without even knowing they’re working on a clone a fraction the size of the source database.

Ensuring all development environments are standardized

Moving to a dedicated environment model raises the issue of making sure everyone is working with the same version of the database. Having the ability to experiment in a sandbox environment is negated if the changes developers are making don’t work when they’re merged with the rest of the team’s changes. It’s vital, therefore, that each team member is starting on the same page.

Delivering these copies will often involve using a backup and restore process, particularly when the copies are needed for bug fixing or user-acceptance, usability and performance testing, where the data they contain needs to accurately reflect the data in production. This means waiting for the DBA to provide the copies, but what we learned in this article is that, actually, that process doesn’t cut it anymore.

SQL Clone can be used to tackle both requirements – ensuring each developer receives an identical copy of the database, and reducing the time taken to produce multiple copies. With a reliable and repeatable process in place for delivering database copies, SQL Clone also opens the possibility of self-serve. Developers and testers can run their own tests and then quickly reset the database back to the original state, improving the quality of the software in a shorter period. This is a much better option than restoring from a backup every time, as shown in this article by Phil Factor on deploying and reverting clones for database development and testing.

Masking personal data

Synchronizing the database structure across copies is only one side of the coin. Each developer also needs an accurate dataset to work with. As production is constantly being kept up to date with new data, ensuring that changes will affect production in the expected way relies on having data that reflects, as closely as possible, the current state of the live system. The earlier developers can work with production-like data, the earlier they can test their changes and catch any errors before they leave development.

Copying down real production data poses a risk when it comes to protecting personal and confidential information, and more database copies also means a higher risk of non-compliance. In most cases, supplying realistic data to developers can be achieved with masking. Actual data is rarely needed – it’s the size, shape and demographic distribution that’s important, and this can usually be accomplished through a masking model.

Masking has advantages over using synthetic data. Generating synthetic data that accurately reflects production is difficult and time-consuming, and if the data that’s generated doesn’t cover a relevant range of test cases, then it will be ineffective in enabling developers to test their changes adequately.

Combining masking with data virtualization, as can be achieved with SQL Provision, enables lightweight, representative copies of production databases to be delivered quickly and safely. Sensitive data is masked before database copies are provisioned so that personal and confidential information never exists outside the live environment. Copies can be created on demand, in seconds, or scheduled overnight so that a refreshed and up-to-date masked copy of the database is ready first thing in the morning before the team even arrives in the office.

SQL Clone’s built in dashboard also provides a clear overview of the database copies, and DBAs can easily see where they exist, providing a comfortable and auditable way of managing compliance concerns.

Merging changes, and keeping track of who did what and when

In a dedicated setup, there are multiple changes happening all at the same time, so how can you merge those changes and get an understanding of who made what change when? Building a history of changes not only provides the opportunity to look back and make fixes if necessary, but also creates an audit trail, which many data protection regulations require as part of demonstrating compliance.

Source control systems have long been an established part of the development process for application code but not for the database code. Fortunately, this is changing and version control in database development is increasingly being adopted. SQL Source Control is a plug-in for SQL Server Management Studio which acts as a bridge between the development environment and version control repository. With it in place, change scripts no longer need to be passed around, and teams don’t have to figure out how to stitch them together to apply all the changes.

Instead, SQL Source Control allows developers to commit their changes and write a short note detailing why this change is being made, enriching the quality of the audit trail. It also integrates with other tools (like Beyond Compare) to help handle some merge operations, and highlights when there’s a conflict. These conflicts can be viewed in terms of who made the changes, what the changes were, when they were made and, importantly, why. Having access to that information offers a simple path to choosing which of the two versions is the preferred version, or building a third version that can then be committed.

If you’re working in a distributed team, having access to this level of detail is extremely useful. Regardless of location or time zone, collaboration and cooperation is thus encouraged and enabled.

Deploying updates from dev to test

The safest way to promote changes up the pipeline is always to deploy from your source control repository because SQL Source Control automatically works out the right way to build the database, based on the referential integrity constraints that are in place.

Working in a dedicated model with a good version control process in place also lays the foundation for automation and full compliant database DevOps. The next step is to configure SQL Source Control so that every time you push a new commit to the shared repository, it automatically builds the database, and runs the team’s suite of database tests.

Summary

This article has highlighted how tools can help ease the transition from a shared to dedicated development model. Managing multiple database copies and refreshing test data, while addressing infrastructure and compliance concerns is made easy with SQL Provision, and challenges collating and deploying database changes are solved by adopting database version control with SQL Source Control. Teams can work faster and develop more value-added features, while reducing errors and keeping data safe.

If you’d like to find out more about connecting your database to your version control system, or creating and managing masked database copies, visit Redgate’s solution pages.