28 February 2017
28 February 2017

Team-based database development with SQL Clone

Team-based database development 1For most development teams, the database provisioning process involves some element of compromise. Often, developers will work with the up-to-date database schema but only a small sample of test data, for example.

It’s a fast, lightweight approach that is sufficient for most simple tests, but it doesn’t necessarily reveal exactly how the application will behave when dealing with millions of rows of real production data. It means you’ll sometimes encounter some ‘unexpected results’ in production, which are hard to reproduce on the sample data in the test environment.

The alternative is to use ‘real databases’ in the development and test environments. You can perform more realistic testing much earlier in the cycle, spot bugs earlier, and it’s much easier to generate the particular data state needed to replicate an issue.

The compromise, of course, is that it can slow down development and testing processes dramatically. Large databases can take hours to restore from backups, and it may not be possible to have many copies available because of storage requirements.

This often leads to sharing a small number of central, shared development databases, which are infrequently refreshed by a DBA. If you hit a problem, it’s hard to know whether it’s a problem with your code, or the result of someone else’s change. It also makes it very difficult to, for example, set up different databases for different branches or to quickly reset the database to its original state to retry a migration.

This article explains how SQL Clone can allow the team to work with a real database in their development and testing work, but without the compromises. It shows how SQL Clone makes the task of creating new copies of databases near-instant and avoids using up lots of additional disk space.

This is possible because each clone database has a connection to a shared image, which is a copy of the database at a particular point in time. The DBA in the team can use the tool’s PowerShell interface to automate the creation, update and deletion of these clones, as well as deal with issues such as data cleansing, or adjusting logins and permissions for each environment.

Developers can even create and delete their own database clones, whenever they need to, based on images the DBA provides. Suddenly, the database is just a lightweight resource they spin up, integrate into their development workflow, and then tear down again, as required. It’s a big leap from how development teams have traditionally been forced to work with ‘heavyweight’ databases.

Setting up SQL Clone

Setting up SQL Clone for your team involves deciding what you want to install, where. SQL Clone consists of two components: one Server and one or more Agents. The agents do the actual cloning work, and because this requires working with the Virtual Hard Disk service, you need to have an agent installed on each machine running a SQL Server instance you want to use to create images, or which will have clones.

The SQL Clone Server is responsible for coordinating everything that happens, and it should be installed on a shared machine that all the machines running agents can see (as well as any users who are permitted to control the system). The images that you want to share need to be on a file share which all the machines with agents can see, too.

Imagine, for example, that you’ve installed SQL Clone Server on a virtual machine called svr-sqlclone. You might want to install agents on your shared staging and QA machines, svr-staging and svr-qa, and also on some developer machines, dev-sam and dev-claire. All of these machines can see svr-sqlclone on the network, and they can all access \\fileserver\images.

Once you have the image, you can create as many clones from it as you want, either on one server or on any machine with an agent which can see the image. Each clone takes only seconds to create and only uses the disk space that’s required to store the changes you make. The image file is accessed when reading all of the data you haven’t changed. As this keeps clones independent from each other, you can work on each clone in isolation.

Managing clones and images with PowerShell

DBAs can manage images and clones either by using the SQL Clone web UI or by using the PowerShell cmdlets, both of which they can access from a laptop simply by pointing at the SQL Clone Server. In the team, the DBA can be responsible for creating images and for deploying and managing clones on the shared staging and QA databases.

Each image is a point-in-time copy of a database which can then be used to create multiple clones. The DBA can use SQL Clone’s PowerShell interface to automate image creation from production on a regular schedule. The following PowerShell script shows, minimally, how to automate the creation of images of a sales database, every night, after having set SQL Clone up so that the QA server can access backups in \\fileserver\backups:

This image can then be used to rapidly refresh the copies of the Sales database on svr-staging and svr-qa using the New-SqlClone cmdlet.

If the database needs further modification, such as changing permissions or performing data masking, the DBA can extend these PowerShell scripts to make these changes and then create a new image from the clone. The alternative is to restore a backup to a temporary environment in the normal way, perform these changes, and then create an image from that database.

With SQL Clone, keeping the staging and QA servers up to date is now easy, and takes only half the disk space it did before, because both can share the same image. And if it becomes necessary to reset the database on either server back to how it was when the image was taken, that only takes a few seconds, rather than the hours it might take to restore a backup.

Integrating Clone into development workflows

While using SQL Clone in these environments can bring benefits, it’s really when it’s integrated into a development workflow that the biggest benefits become apparent. Before SQL Clone, developers might have had to share a limited number of environments, and if a database needed to be refreshed or reset it could slow things down considerably.

Clones can be created very quickly, and use little additional disk space, so it’s now reasonable for every developer to have multiple copies of the same database accessible from their development machines. This can mean an isolated database for every developer on every branch, and resetting the database after testing some changes takes a matter of moments.

The exact approach depends on the way the DBA and the team choose to work. DBAs who prefer to control deployment of databases onto all machines can configure the permissions of SQL Clone such that only the DBA group can control the system, and create clones on any machine with an agent installed with a few clicks. Dealing with tickets for database refreshes can now take moments, and requests for new environments no longer have to be blocked due to lack of disk space.

Alternatively, DBAs can use SQL Clone to provide a curated set of images from which developers can create clones. For example, they can use PowerShell cmdlets to regularly create an image that contains a copy of the database that’s ready for developer use, having performed any data masking or other manipulations required, and make them available to other members of the team using SQL Clone.

Developers can then log into the SQL Clone web UI and create clones whenever they need them. Whenever they open a new branch, they can make a fresh database based on last night’s image. The schema will be up to date, and they can modify it without worrying about clashing with other team members.

Importantly, because the database has realistic data, migration scripts can be tested against it and, if the migration goes wrong, resetting the database is as easy as deleting the current clone and creating a fresh one.

Alternatively, the DBA might provide developers with PowerShell scripts they can run to create clones. This would allow additional customization to be done to the clone databases after they’re created if, for example, permissions need to be adjusted on a per-developer basis.

Whether using the web UI or PowerShell, it’s always possible for anyone who’s allowed to control the SQL Clone system to see which images are available and where they’re being used to provide clones.

Summary

The best way to integrate SQL Clone into your processes will depend on the way your team works, your network architecture and how you use databases in development. Whichever way you choose to work, eliminating slow and restrictive database restores unlocks new ways of working which can make working with databases just as convenient as working with code.

Find out more about SQL Clone with a 14-day fully functional free trial.

Tools in this post

SQL Clone

Clone SQL Server databases in seconds and save up to 99% disk space.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Webinar

    How to treat compliance in DevOps as serious business

    We hosted this webinar to discuss how, by embedding ‘Privacy by Design’ in your Database DevOps practices, you can introduce a framework that builds a bridge between fast AND secure software development. We arm you with practical tips on how to discover, classify, protect and monitor your SQL Server estate, and therefore ensure that your Database DevOps practices are secure

  • Livestream

    SQL Clone Product Launch

    A livestream event to celebrate the launch of our new database provisioning tool, SQL Clone.

  • Article

    SQL Clone: the aspirin for your database provisioning headaches

    When developing a database as a team, most team members would generally prefer to work with their own, isolated copy of the database, rather than work on a shared development database. Up to now, however, there have been a number of additional management and security problems with the dedicated database approach and these burdens only

  • Article

    Database Continuous Integration with SQL Clone and SQL Change Automation

    When you are working as part of an application development team, it is worth settling down into a routine of doing a daily build from source control, and then provisioning test and development instances of the database. One major advantage is that the code and database can be scrutinized as early as possible, and you

  • Article

    Getting Started with Database Development Using SQL Provision

    Developers, when working on databases rather than the application code, often find they have less freedom to experiment than they are used to. It’s not necessarily easy to set up a database for testing, especially if the process isn’t automated. They’ll need to dig around in source control, build the database at the correct version,

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant