Table Internal Sync Rule–Substituting the Sync Column

I’ve been playing with Data Masker for SQL Server v6 and it’s an interesting product. I like the way it works, but I do find it a little challenging sometimes to figure out how to mask values. I’ve written a set of posts for different scenarios.

In a previous post I showed how I could change column values across rows in a table. This is useful in that it moves my data from this:

2018-03-02 15_16_35-SQLQuery1.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft

to this:

2018-03-02 15_31_18-SQLQuery1.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft

That’s good, but since I haven’t changed the myid column, I really haven’t masked things well. In fact, it would be trivial to derive the initial mychar values from the set if I knew the original values.

Let’s fix that.

My masking set looks like this for now:

2018-03-02 15_38_48-tableinternal_ Data Masker for SQL Server

I want to add a new substitution rule that will mix up my myid values. I add the rule and configure the correct column. In this case I limit the boundaries of my random numbers to 1-100, but I could choose any value.

2018-03-02 15_40_35-New Substitution Rule

I save this and run just this rule. Now my data looks like this:

2018-03-02 15_42_00-SQLQuery1.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft

We’re partway there, but I don’t have grouping. Elric has two different values for myid, which is wrong. Let’s fix that. Just as in the previous post, we’ll now add a Table Internal Sync rule. We configure this in reverse of the previous article. Now we use the new, changed names in mychar as the group column and myid as the column to sync across rows.

2018-03-02 15_47_18-New Table-Internal Rule

If I execute this, then I get the following:

2018-03-02 15_49_09-SQLQuery1.sql - DKRSPECTRE_SQL2016.sandbox (DKRSPECTRE_way0u (52))_ - Microsoft


Now I just need to ensure these rules run in order (all four) with dependencies and when I run the entire set, I’ll get my table synced. Here’s the final rule set. Notice that I’ve created dependencies.

2018-03-05 07_34_37-InternalSync_ Data Masker for SQL Server

I’ll save the set, and then re-run it. Now I get these results:

2018-03-05 07_35_49-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (69))_ - Microsoft SQL Serv

Data masking is something that’s never been this easy for me. I’ve built lots of large scripts that made perfect sense for me, but were difficult to turn over to anyone else for usage and maintenance.

I’d urge you to give Data Masker a try if you’re looking to ensure compliant, safe data sets for your non-production environments.

I have other articles on Data Masker if you’re interested.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.
%d bloggers like this: