Matt Hilbert

9 June 2020

Can the shared development model work without shared environments?

Discussions about the pros and cons of the shared and dedicated development models have been going on for a long time. As far back as 2011 the well-known Microsoft Regional Director and MVP, Troy Hunt, wrote a long and impassioned article on Simple Talk, titled: The unnecessary evil of the shared development database. You can see what side he’s on.

In the shared development model, every developer connects to the same copy of the production database on the same server. This, however, can lead to problems, the biggest one being developers overwriting each other’s changes. Experimentation is also discouraged because it might introduce breaking changes, and the state of the data becomes unpredictable, leading to unstable integration tests.

In the dedicated development model, each developer has their own copy of the database running locally. This gives them the freedom to develop database and application code changes side by side and supports branching strategies so that different features can be developed at the same time. Importantly, developers can also test new code changes, knowing that if they are breaking changes, no one else will be affected and their personal copy of the database can be restored.

Despite the advantages of the dedicated model, however, the 2020 State of Database DevOps Report revealed that only 30% of businesses use it. It also showed that the larger the size of the development team, the more likely they are to favor the shared model.

Up until recently, this was largely driven by a belief that provisioning database copies to every developer, particularly in larger teams, is hard. It apparently takes hours of time, lots of disk space, and deploying database changes is, by default, a difficult process. If database schemas and permissions are managed properly, and co-located teams have a shared environment where they can communicate constantly, the conflicts that can arise with a shared database can – hopefully – be avoided.

That could all be about to change, however.

Welcome to the new era of remote working

Remote working and distributed teams have been facets of software development for a long time. The recent pandemic, however, has moved them to the top of the agenda for many businesses and some companies like Twitter have already declared that their employees will in future have the option to work from home permanently.

This new norm, however, brings with it some problems of its own. Redgate launched its third annual survey on the state of database monitoring in April 2020, in the midst of the pandemic, and the resultant 2020 State of Database Monitoring Report gives a revealing glimpse into what the new challenges are.

While performance monitoring and backups remain the most common responsibilities for database professionals, for example, managing security and user permissions have leapt up to third and fourth place. Connected with this, the two biggest causes of database issues are human error at 23% and ad hoc user access at 18%. Interestingly, the ad hoc issue has more than doubled since last year’s survey and could well be a result of the increase in remote working, where tasks become more widely distributed.

As can be seen, the glue that holds the shared development model together appears to be failing. With no shared physical environment, developers can no longer tap a colleague on the shoulder to confirm they can commit a change. Communicating is more difficult. Everyone is working in the dark and, as the survey shows, workarounds and ad hoc updates are creeping into the process.

This highlights further the advantages of the dedicated development model and gives businesses the incentive to introduce it to maintain – and improve – the productivity of their developers. Furthermore, whether teams continue to work remotely, part remotely, or return to being co-located, the benefits to be gained are permanent.

Making the dedicated model easy to adopt

In a typical shared development environment, provisioning a new copy of the shared database is difficult. Every developer has to stop work, it can take hours, and the default choice for many businesses is to use the current out-of-date copy to avoid the pain. This inevitably leads to errors with deployments and breaking changes reaching production.

It is also, however, what often stops businesses exploring the dedicated development model because if it’s that much effort to provision a single copy of the database, how much worse is it to give every developer their own copy? That’s not to mention the disk space required when many business databases are now 1TB or more in size.

This is where solutions like SQL Clone come in. SQL Clone creates copies of a source database or backup that are around 40MB in size, in seconds, even for databases many terabytes in size. Those copies, or clones, still look and act like full databases so that developers can work in their own dedicated environments without even knowing they’re working on a clone, and with the benefit of production-like data to test their changes against.

From a developer’s perspective it’s quite magic. Full copies of their databases can be spun up and torn down in seconds, as many copies and as often as needed, to speed up development cycles or test different options in parallel.

SQL Clone enables such agility by virtualizing the data. Each clone is in essence a mount point to an image of the source database. Clones share the data from these images and changes made to each developer’s clone are stored in a local differencing file. As each clone is isolated, developers are free to experiment and break things without disrupting anyone else on the team or risking the integrity of other environments.

Virtualizing the data in this way also provides administrators with complete control over what’s stored in the images – the data can be masked, unmasked, synthetic, a full copy, or a subset, and so on. A streamlined UI provides a rich set of options for defining user access and permissions so development teams access only the data they’re permitted to, ensuring compliance and boosting productivity. Suddenly, it’s safe and easy for developers to self-serve fresh local copies on demand.

A library of PowerShell cmdlets further shrinks the overhead of provisioning development and test databases, allowing DBAs to automate the process of updating images to the latest production version and refreshing development environments with new clones.

Summary

Using SQL Clone, any developer in any location can always have access to an up-to-date database copy in seconds, speeding up development and reducing errors. At a time when it’s harder than ever to make the shared development model work without a shared environment, it makes the dedicated development model an attractive option.

Importantly, it also opens the doors to modern software development practices. Database version control can be adopted, for example, and workflows for databases can mirror those for application code with Pull Requests and Git automation to manage databases with branches.

To find out more about the dedicated development model and how SQL Clone can enable database DevOps, Redgate has a selection of other resources:

Watch the webinar, How Clones Enable Fast Onboarding for Database DevOps.

Download the case study: Moving to a dedicated database development model.

Read the blog post: 4 steps to laying the foundations for standardized database development.