27 February 2018
27 February 2018

SQL Provision adds fully integrated data masking

SQL Provision launched in January, offering users blazingly fast database copying, with a light storage footprint, centralized management, and the ability to mask any sensitive data, prior to distribution.

This new release takes compliant provisioning one step further, by integrating data masking directly into SQL Clone’s image creation process, rather than running it as a separate step prior to the image creation.

Having defined your masking set, you can ensure that SQL Clone will apply it during image creation, and then create all clones from the sanitized image. This means that you can be confident that all clones distributed outside of a secure ‘production’ environment will comply with requirements for pseudonymizing and obfuscating sensitive and personally-identifying data.

The Need

Many organizations have adopted DevOps practices for the database, to help them make more effective use of the data they collect, for business analysis, as well as for faster development, effective testing, and ultimately to enable them to deploy reliable databases at the frequency they need.

However, in many cases, advances in an organization’s ability to collect and analyze data have outstripped advances in their data governance procedures, which are supposed to ensure that sensitive data remains protected and secure as it travels outside the production environment.

While legislation such as GDPR and HIPAA refer to data minimization as a way limiting the use of data to the purpose for which data was collected, this doesn’t mean an organization needs to avoid using their data entirely. However, it does mean they must have a process to sanitize any sensitive or Personally Identifiable Information (PII), prior to distributing data around the business. The whole process will need to be automated, and auditable so that it complies with national and industry regulations.

The Challenge

Organizations need an effective “gateway” between production and non-production environments, to ensure the safe distribution of database copies, from one secure and centrally-managed location. The challenge is in designing this gateway efficiently, such that that it protects data, but without blocking the team’s development and release processes and without introducing an unrealistic administration burden.

As described in our previous article, a traditional, manual process for distributing sanitized database copies is no longer fit for purpose. The process is difficult to audit, it does not scale, and the administrative burden is considerable.

The Solution

SQL Provision offers a different approach to the problem, and we’re continually developing it to become the ideal ‘gateway’ tool between production and non-production environments.

SQL Clone uses virtualization technology within Windows to provide the fast, lightweight database copying component, with centralized management of all data movement.

Data Masker provides effective data obfuscation and anonymization. We define the required set of data masking rules once, describing how each column and row should be obfuscated and anonymized. We can even define rules across tables, to maintain existing relationship in the data, so that the masked data will look and behave as much like the real data as possible, for development and testing purposes. We can then apply the masking set consistently to the target database, every time we need to distribute a fresh copy.

What’s New?

In the initial release of SQL Provision, data masking was still an additional step. We had to restore a database backup, run Data Masker, and then use SQL Clone to create the image from the masked database, and manage provisioning.

With this new release, users can now run masking sets within SQL Clone, as an integral part of the image creation process. This shortens the workflow, removing the need to perform a database restore prior to image creation, and so makes the provisioning process much quicker, and uses less disk space.

We simply ‘drag-and-drop’ into the image creation process the required data masking sets. We can also run other T-SQL Script to change configuration settings and permissions.

SQL Clone applies them all, in sequence, during the image creation process, ensuring that all clones created from the resulting image are correctly and consistently sanitized. As always, the entire process can be scheduled and automated using PowerShell

Database Provisioning for Compliance

This release of SQL Provision both supports the DevOps approach to database development and testing, and makes data protection more central to the process. Managing provisioning from one central system is more transparent, consistent, repeatable and auditable.

This SQL Provision integration is supported by Data Masker v6.0 and SQL Clone v2.5.0. If you’ve not yet tried SQL Provision, download your free 14-day trial now to get started.

Share this post.

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

Related posts

Also in Hub

When to use the SELECT…INTO statement

We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table.


Also in Product learning

Beyond Formatting: Improving SQL Code using SQL Prompt Actions

In this article, I'll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in...

Also in SQL Clone

Create, protect and manage non-production databases with SQL Provision

SQL Provision allows users to create copies of SQL Server databases in seconds, using a fraction of disk space, and mask any sensitive data to help address data privacy and protection concerns. It se...

Also in SQL Provision

Masking Dates in a Non-Production Database

Since 2004 I've helped more than 800 companies incorporate data masking into their database deployment strategy for non-production databases, mainly using the Data Masker tool, which is part of SQL Pr...

Also about data masking

SQL Data Mask: new masking features, constants and composition

SQL Data Mask is the latest prototype to come out of Foundry, Redgate’s research and development division. It copies your database while anonymizing personal data. You can use it to mask your databa...

Also about data protection and privacy

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 ...

Also about database provisioning

How Redgate use SQL Clone internally to provision databases

A common thread behind the software we develop is a problem or issue we've come across in the software development process. We then adopt the software ourselves, which gives us a real understanding of...

Also about GDPR

So what is a Data Protection Impact Assessment and why should organizations care?

If you’ve read anything about the upcoming General Data Protection Regulation (GDPR), you’ll probably have seen the phrase Data Protection Impact Assessment (DPIA) used. It’s similar to the curr...