Product articles SQL Toolbelt Database Builds and Deployments
Using SQL Compare with Dynamic Data…

Using SQL Compare with Dynamic Data Masking

SQL Compare works with dynamic data masking, detecting the differences in masks across tables and generating the T-SQL you need to deploy your masking configuration to other databases.

One of the features that I hear many DBAs excited about in SQL Server 2016 is Dynamic Data Masking (DDM). This allows DBAs to centrally set a mask that is returned for a table column instead of the actual data. This means an application that wants to limit what information is returned to certain users can do so easily and consistently without relying on, or assuming, the front end application has implemented the feature.

Redgate SQL Compare works beautifully with DDM, detecting the differences in masks across tables and generating the T-SQL you need to deploy your DDM code to other databases. In this piece, we will examine how easy it is to use SQL Compare alongside DDM.

Working in development

The DDM feature can be added to new tables at design time or included in existing tables that are being modified. The syntax for DDM is in the CREATE TABLE and ALTER TABLE commands, as an additional property that is added to a column.

Note: There is no support in the table designer in SSMS as of the time this piece was written.

If I create a new table, I can add masking inline with a column definition like this:

DDM-new

In this case, I’ve used a default mask, which will mask the string with Xs. If this table already existed, I can alter a column with the ALTER COLUMN syntax like this:

NEW-DDM-4

In this case, I’ve used a partial mask, which will expose some of the values in the field, but replace others with Ys. (For more information on DDM, see Microsoft Books Online (BOL) or the Stairway to Dynamic Data Masking series at SQLServerCentral.)

When implementing a mask, there are multiple ways I can mask data. The masks in SQL Server 2016 are:

  • Default – works with most datatypes
  • Email – works with strings only
  • Custom – works with strings
  • Random – for numeric data

Each of these has its own syntax for configuration, but they are all implemented as part of the CREATE/ALTER TABLE syntax for a column.

Deploying DDM code

SQL Compare has been enhanced to include support for various SQL Server 2016 features, including Dynamic Data Masking. Let’s look at how this works in practice.

I’ve got a table in development, dbo.OrderHeader, where I’ve implemented DDM on a couple columns. The table designer doesn’t show DDM, but if I script the table, I’ll see this:

NEW-DDM-1

If I look in the production database, I see this code:

NEW-DDM-2

Now, I want to deploy these changes from the development environment to the production environment (or some intermediate testing environment). The trick is moving these changes. Obviously in development I implemented this in a manner similar to this:

NEW-DDM-5
However, that was some time ago, and I don’t have this script. SQL Compare makes it easy to regenerate this code with a quick comparison. I’ve created a project here that will compare the objects in my two databases.

DDM3

When I click OK, SQL Compare will run the comparison and give me the results. I can see there are a few items that don’t match up in the databases, and a few that are in only one or the other.

DDM4

I have the option to choose which objects should be changed, and I’ve selected only my OrderDetail table to deploy. In the lower pane, I can see the differences between the tables. There are some index differences, but the important part is that the DDM code is captured here.

DDM5

When I click Deploy, I have a few options available. I am choosing the Deploy using SQL Compare option for this article. If you need to use this script as part of your deployment process, you may choose Create a deployment script instead. Note that I can still save a copy of the script and re-compare after deployment.

DDM6

Pressing Next brings me to the Dependencies step. In this case, I have a Foreign Key (FK) between two tables, and the other table is listed, with the checkbox marked to deploy dependent objects.

DDM7

This can be disconcerting, but if I click the dbo.OrderHeader link, I’ll get the comparison of the object in both the source and target databases. In this case, you can see below there are no differences, so nothing will be deployed.

DDM8

The final step is to review the script and deploy the changes. In this case, I have a list of changes to the dbo.OrderDetail table. As I look at the script, I can see there are actually three ALTERs taking place here.

DDM9

If I scroll to the right, I can see the details of the masking being applied to the two columns in the table.

DDM10

The last step is to click Deploy Now to execute this script on the target database. Once I do this, I get a dialog to confirm my choice.

DDM11

SQL Compare then performs the deployment (and saves a copy of the script). I can see the success or failure of each step.

DDM12

After the deployment is complete, the last step is to recompare the databases, and that works. As you can see below, there is no difference detected for the dbo.OrderDetail table.

DDM13

Did it really work?

Now to test the production database. I can first script out the table from production and see the code. In this case, the script is shown below, with the database name to the lower right (EncryptionDemo_Production).

DDM14

I can also query the table as a normal user, one that has not been granted the UNMASK permission. When I do this, I get masked results. I also have a second query in this batch, as the db_owner, which has been granted UNMASK permissions.

DDM15B

Now watch a demo

I hope I’ve given you a good idea of how Dynamic Data Masking works in this walk-through. To see how it works at the desktop, on your PC screen, I’ve also created a video you can watch. It only lasts three minutes, so why not press the play button?

Smooth deployments with Dynamic Data Masking

The addition of DDM to SQL Server 2016 is a welcome feature I suspect many developers and administrators will want to implement. However, ensuring the correct masks are applied to the correct tables and columns is a tedious, error-prone task when performed by hand.

SQL Compare easily handles your DDM changes and ensures they’re migrated from one database to the next with the same settings, just as easily as you’ve come to expect every other code change detected and scripted by SQL Compare.

Try it now free or ‘Check for Updates’ under the Help menu in-product to make sure you’re on the latest version.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more