Redgate

Fig 1 Editing a masking rule in Data Masker

Data Masker is a static data masking product that can mask millions of rows an hour via the application of sets of masking rules. It maintains the credibility of masked data by ensuring that correlated values (for instance, age and date of birth) remain consistent after masking. This is done via substitution rules using a correlated data set that contains potential values to substitute. This can be seen in Figure 1. For instance, your data set could contain random surnames, or it could contain random US zip codes with accompanying state, county and town names appropriate to that zip code. These data sets are available out of the box or can be user defined. In addition, this can be done between databases or even database instances. Data Masker also has the ability to generate synthetic data in a limited capacity. This capability is best utilised when production data is either unavailable or incomplete.

Notably, masking in Data Masker always retains relational integrity and includes the capability to mask primary or foreign keys without a join operation. Data Masker also provides a column finder that allows you to search your database based on column name. It automatically generates reports whenever a masking rule is run, making the masking process fully auditable, and allows you to either apply your masking rules immediately via the application itself or export them for use elsewhere. The latter capability is particularly important for interacting with the second half of SQL Provision, SQL Clone.

Fig 2 The SQL Clone dashboard

SQL Clone (as shown in Figure 2) allows you to create and centrally manage images and virtualised clones of your production data. In this case, images are complete point-in-time copies of a database, taken from either a live server or a backup. As images are often quite large (since they are complete copies) they are usually stored centrally. Importantly, you can modify your image during its creation using either SQL scripts or sets of masking rules exported from Data Masker. In the latter case, the masking rules will be applied to your data before the image is created, meaning that the created image will be masked. Moreover, since Data Masker always maintains referential integrity, it will be referentially intact. In addition, if multiple sets of masking rules are used, you can choose the order to apply them in.

Clones, on the other hand, are derived from an image and only store the changes between themselves and the images they were derived from. Since they only store differences, they are small in size (usually less than a hundred megabytes) and can be created and deleted very quickly and at will, wherever they need to be at any particular time. For the purposes of test data management, this means that your testers can provision a clone of a masked image to their local machines whenever they need test data. This can be accomplished using self-service, meaning that your testers can retrieve test data without having to wait on an administrator. The product also provides PowerShell-driven automation for creating and updating images and clones, allowing you to ensure that your testers always have up to date test data.

Redgate’s entire approach hinges on two concerns: compliance (with existing mandates, such as GDPR, as well as forthcoming regulations) and DevOps. Effective test data management is essential for achieving both of these. Desensitising your test data is necessary for compliance with a variety of mandates, as well ensuring data privacy and security (protection from data breaches, for instance), while timely provisioning of test data – delivering the right test data to the right place at the right time – is an important component of any DevOps pipeline. Consequently, SQL Provision provides both of these capabilities.

Moreover, SQL Provision does so using a combination of database cloning and data masking. This has some clear advantages over competing approaches, such as data subsetting or synthetic data generation, most of all that it guarantees that your test data will be representative. It also makes provisioning that test data fast and easy. What’s more, Redgate is uniquely positioned in offering a solution based on database cloning to the mid-market, whereas competing products tend to be targeted at the high-end.

The Bottom Line

SQL Provision is the only solution of its kind aimed at the mid-market. It lacks some of the sophistication of more expensive test data management products, particularly when it comes to masking and discovery, but on the other hand, do you really need to pay for all those bells and whistles – and are they worth giving up the advantages of Redgate’s approach? If your answer is ‘no’, and you’re using SQL Server, then SQL Provision may be an appropriate choice.

Fig 02 - Editing a masking rule in Data Masker

Data Masker (as seen in Figure 2) is a rules-based static data masking product that performs on the level of millions of rows per hour. It maintains the credibility of masked data by ensuring that correlated values (for instance, age and date of birth) remain consistent using substitution rules and correlated data sets that contain potential values to substitute. These are available out of the box or can be user defined, and this process can be actioned between databases or even database instances. Data Masker also has the ability to generate synthetic data in a limited capacity, best utilised when production data is either unavailable or incomplete.

Masking in Data Masker is irreversible, always retains relational integrity, and can mask primary or foreign keys without a join operation. It automatically generates reports whenever a masking rule is run, making the masking process fully auditable, and allows you to either apply your masking rules immediately or export them for use elsewhere (in SQL Clone or the SQL Data Catalog, for example).

Data Masker provides some basic sensitive data discovery functions, but in practice the meat of this is in SQL Data Catalog, which allows you to define your own taxonomy of classifications that your data can be matched against using an extensible library of built-in pattern matching rules. You can then attach actions to these tags in order to, for instance, automatically mask data that is classified as sensitive. To that end, compatibility with HIPAA, GDPR, and other regulations is provided out of the box, as is integration with various test data management processes.

SQL Clone allows you to create and centrally manage images and virtualised clones of your production data. Images are complete point-in-time copies of a database, taken from either a live server or a backup. As they are often quite large (since they are complete copies) they are usually stored centrally. You can modify your image during its creation using either SQL scripts or sets of masking rules exported from Data Masker.

Clones, on the other hand, are derived from an image and only store the differences between themselves and the image they were derived from. Due to this, they are small in size (usually less than a hundred megabytes) and can be created very quickly and wherever they are needed. For test data management, this means that your testers can provision a masked clone to their local machines whenever they need test data without having to wait on an administrator. Team management, permissions, and self-service features are provided to this end, as is integration with Git. PowerShell-driven automation is also available for image creation and management. By leveraging SQL Data Catalog, you can even distribute clones (and thus test data) as an automated part of your business processes.

SQL Provision and SQL Data Catalog together provide a complete test data management solution that includes test data discovery, masking, and provisioning (via database clones) and can readily enmesh itself into your existing processes. Cloning in particular offers significant advantages over both data subsetting and synthetic data generation: for example, it effectively guarantees that your test data will be representative.

Redgate also makes distributing that test data fast and easy. This is always a boon, but it is particularly important for enabling high-speed testing that can easily fit into (and keep pace with) a DevOps pipeline. Moreover, access to self-service and team management features help to make life easier for your developers, testers and DBAs alike, and integration with Git allows you to automatically build, validate and deploy an appropriate database clone whenever a pull request is made. In turn, this means that you can review the request against a live environment that implements it, instead of just raw code. This all works together to help facilitate a “shift left” approach to testing, allowing you to test earlier, faster, and altogether more smoothly.

The Bottom Line

SQL Provision and SQL Data Catalog form a capable, comprehensive, and cost-effective test data management solution. If it weren’t for its limited database support – its full range of functionality is only compatible with SQL Server – we would be singing its praises unreservedly. If that’s not a problem for you, Redgate should definitely be on your shortlist.