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:
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:
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:
If I look in the production database, I see this code:
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:
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.
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.
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.
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.
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.
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.
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.
If I scroll to the right, I can see the details of the masking being applied to the two columns in the table.
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.
SQL Compare then performs the deployment (and saves a copy of the script). I can see the success or failure of each step.
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.
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).
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.
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.
Also in Blog
I’ve read through a number of the industry thought leaders to get an understanding of how DevOps is being communicated out there. As with so much else in life, you can start at Wikipedia to get a ge...
Also in Redgate products
My previous article in this series explained why it's important for a development team to adopt a common standard for formatting SQL code. It also gave a broad overview of the styles and actions withi...
Also about SQL Compare
We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if w...
Also about SQL Server 2016
If you’ve read my simple assessment of SQL Server’s In-Memory OLTP tables on Simple-Talk and have decided to implement them, we're in a position to support your move. Redgate SQL Compare can help ...