17 October 2018
17 October 2018

How to reset your development database in seconds using SQL Clone

Let’s say you’re making experimental changes to your development database and, to explore a hypothesis, you’ve just dropped a table. How long does it take you to restore the database to its previous state, so you’re ready to continue testing? If it’s long enough to go fetch a coffee, then it’s too long.

When developing software, it’s essential to be able to iterate quickly. The shorter the time it takes to get from having an idea to realizing either its genius or its flaws, the better. Anything that slows me down is, in that sense, an enemy, whether that’s compiling code or running a test suite, or rebuilding a database. If developers are being held up by their processes, then they can’t be fully effective.

This article will demonstrate how SQL Clone can help you minimize the ‘drag’ on your database development and testing cycles, even when working with very large databases.

A quicker way to refresh development and test databases

For reasons that Steve Jones explains in his article, Building better test data with SQL Provision, it is useful to be able to develop and test with databases that retain the data volume, characteristics and distribution of the production database. Traditionally, this might involve waiting for a DBA to restore a backup of the current production database, run data obfuscation scripts to protect any sensitive and personal information, and then make available the backup of that sanitized database.

However, each time I make a destructive change to my development copy of that database, I’ll need to restore the backup again. If that backup is large, and my workflow involves frequent experimental changes, then I’m suddenly in an agonizingly long iteration cycle, spending far too much time waiting for a progress bar.

One way to solve the problem is to automate database builds from source control, then stock the database with standard data sets for testing, but then you’re probably not catching all the bugs you would be if you were working against a properly-representative database.

SQL Clone offers a new approach. Instead of a lengthy full restore of a huge database each time we want to “reset”, SQL Clone will create an image of the source database, using data masker to automatically sanitize the data as part of the image creation. This done, we can spin up a new clone databases, from this image, in just a few seconds, even for a 64TB source database.

By using clone templates, which the DBA could also set up for the team, each developer can run the T-SQL modification scripts defined in that template, during clone creation, to make sure the clone database is configured exactly as required for their environment.

Therefore, each time your current clone has outlived its usefulness, you can simply drop your old clone and build a new one. Even then, though, the need to break from your current task, drop the clone and recreate a pristine version of it, is disruptive. You’ll need to make sure it has the same name, targets the right instance, runs the correct template modifications, and so on. If you’re repeating this several times an hour, the time stacks up, and mistakes can creep in.

Refreshing development and test databases in seconds

To make the process even quicker, SQL Clone (v3, and later) has a “clone reset” feature. With a push of a button in the web UI, or with a single line of PowerShell, SQL Clone will drop and recreate your cloned database for you, using the same name, instance, image, and modifications (as defined by the current template). Trashed half the tables in your test database? Push the button.

Suddenly everything is back, including all the data, just as it was when the clone was first created, and it only took a few seconds to restore it (depending on how long your template modifications take to run). Alternatively, you can use the PowerShell cmdlet like so:

Reset-SqlClone (Get-SqlClone -Name 'MyClone')

The following image shows it in action:

Summary

With SQL Clone, you aren’t waiting around for your database anymore, and you don’t need to tiptoe around destructive database changes, because it’s trivial to get back to a working state.

Share this post.

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

You may also like

  • Article

    Basic data masking for development work using SQL Clone and SQL Data Generator

    This article describes a lightweight copy-and-generate approach for making a sanitized version of a production database available to development teams with SQL Clone and SQL Data Generator. We build the latest database version (schema only), then copy into it, from the production database, the data for any tables and columns that don’t require masking. For

  • Webinar

    Data privacy & protection: A logical extension to DevOps

    Are you considering data privacy and protection as part of your DevOps process? In light of legislation like GDPR, making sure that any personally identifiable information (PII) is protected as it moves through your development and testing environments, is now an essential part of the process to ensure that your Database DevOps practices are compliant.

  • Webinar

    Is HIPAA impacting your data delivery processes?

    The HIPAA legislation has been around for several years, and in order to remain compliant changes to your data delivery processes have been necessary. But how has this impacted the speed and performance of your work?

  • Article

    Masking Data in Practice

    Even small extracts of data need to be created with caution, if they are for public consumption. Sensitive data can 'hide' in unexpected places, and apparently innocuous data can be combined with other information to expose information about identifiable individuals. If we need to deliver an entire database in obfuscated form, the problems can get harder. Phil Factor examines some of the basic data masking techniques, and the challenges inherent in masking certain types of sensitive and personal data, while ensuring it still looks like the real data, and preserving its referential integrity, and distribution characteristics.

  • Article

    The Database DevOps Challenges SQL Clone Solves

    SQL Clone helps to accelerate the delivery of databases updates, by enabling an organization to provide database copies, and the right data, to all parts of the deployment pipeline that need it, with a light footprint, and securely. It removes much of the administrative burden of providing a ‘database refresh’, while still allowing operations teams

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant