30 November 2017
30 November 2017

SQL Clone v2.4 – run scripts during image creation

SQL Clone 2.4 incorporates a new T-SQL script runner that the team can use to mask sensitive or personal data, or to modify security and other configuration settings, prior to creating a clone.

In SQL Clone, we first create an image, which is a full point-in-time copy of the database, and from this image we can create multiple clones, almost instantly. With the script runner, we can now run T-SQL scripts to modify an image, so that all clones created from this image contain masked data, for example, and are otherwise ‘fit for operational purpose’ in the target environment.

Why is the Script Runner needed?

Sometimes, developers need access to “real” data. By incorporating realistic data sets into their testing cycles, for example, they will find data compatibility issues earlier in the release pipeline, and be more confident that their test results will reflect the behavior seen in production. However, copying databases from production to non-production environments is not straightforward.

Firstly, the Ops team members have a responsibility to prevent access to personal data, and protect sensitive commercial data. This means that sensitive data, such as credit card numbers, must be removed or obfuscated, and personal data modified such that it can no longer be directly attributed to a specific data subject.

Secondly, some configuration changes may be required, to produce a working clone in the target environment. For example, you many need to change the source permissions by remapping users to their non-production logins. Many companies also store environment-specific configuration data like service endpoints in databases, which will need to be changed.

From our own research into DevOps provisioning, we already know that around 30% of companies use scripts to scrub sensitive data as part of their database provisioning process, and we suspect a lot more want to start doing so, to improve their compliance practices, particularly with the increasing awareness of the implications of the General Data Protection Regulation (GDPR).

How the Script Runner Works

We modify the image at the point of creation, simply by selecting the script to run:

This brings up a new window which confirms the modifications, image destination and name:

Following which, SQL Clone goes to work, making the required modifications. As soon as this is done, developers can use SQL Clone’s role-based permissions to self-serve deployment of clones to their development and test servers.

Summary

Introducing the script runner in SQL Clone allows users to run a script as part of their standard image creation process, to mask data or change permissions, and so on. This shortens the workflow, and makes compliance part of the database provisioning process, rather than a hindrance to it.

The script runner is just the first step in allowing users to modify databases as part of automated provisioning. We would love to hear what you want to see next. Are there other features that could make database provisioning easier? For more details on how you can use SQL Clone v2.4 to modify images prior to cloning, check the documentation here. You can download a full-featured 14 day trial from the Redgate website. Give it a try, and let us know what would accelerate your DevOps provisioning.

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

  • Article

    How to create and refresh development and test databases automatically, using SQL Clone and SQL Toolbelt

    In order to be able to deliver database changes more quickly, there are several tasks that must be automated. It can be a daunting job to ensure that the whole team has the latest database build, when there is a proliferation of copies, and the database is big. This article illustrates a solution, showing how

  • Webinar

    How to keep your delivery processes secure with Database DevOps

    Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.

  • Article

    SQL Clone and databases protected with TDE - part 1

    SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Often, these ‘clones’ are copies of production databases, which contain sensitive data that needs to be protected from unauthorized access. Transparent Data Encryption (TDE) is one technique that many organizations have implemented in order

  • Webinar

    How to cut time & disk space for database provisioning by 99%

    In this webinar, you’ll see how SQL Clone cuts out the pain of provisioning development databases, regardless of which database development model you use.

  • Article

    Using striped backups with SQL Clone

    If you’re a Redgate SQL Backup customer, occasionally you’ll need to convert your SQL Backup (.sqb) files to the native SQL Server backup format (.bak), perhaps to perform native database restores on a server where SQL Backup isn’t installed. This produces a striped backup, because each thread used when making the backup will produce a

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant