Can we do masking on non primary key tables or Table without unique index ?

Amar_v

Hello All,
My requirement is to do Data masking on Table which has neither primary key nor the unique key index.
Because I am getting an error while I am trying to do data masking on non primary key tables. Please guide how to proceed further.

PlantBasedSQL

Hi @Amar_v thank you for your question!

Data Masker can synchronize values using Table-To-Table rules to tables with no PKUK constraint, however to run a masking rule such as Substitution against such table you will require a PKUK constraint.

There are potentially a couple of ways around this issue though:

1 ) You can use command rules to process the table by yourself using an update statement 

2 ) You can mask another table and then synchronize into the table in question

3 ) (Most used method) You can use a command rule to create a temporary UK on the table, then an "Index Refresh Rule" so Data Masker can see it, followed by your masking rule and then a command rule at the end to remove the temporary UK - If you use this option I would recommend making the initial command rule and index refresh dependent on each other in rule block 00 and the final command rule at the end of the set or at the end of the last rule affecting that table.

4) (Slowest Option) You can use a "synchronisation manager" (method here) rule to pull the distinct values out of the heap table into a "mapping table", where they will be masked and then fanned back out into the heap and any other tables where you will need the values

I hope this helps - let me know if you have any further questions.

Kindest

Amar_v

Thanks a lot for your quick response. I am new to this technology so, Is it possible to provide some documentation reference for Method 3(provided above) or an example which helps me understanding the solution in better way. Appreciate your help.

PlantBasedSQL

Thanks @Amar_v - I'll actually go one better for you.

I recorded this video this morning, hopefully with the information you need to get started: https://www.youtube.com/watch?v=eNcIAGn3PzU&ab_channel=RedgateVideos

Let me know if this helps!

Thanks & Kindest

Amar_v

That was an excellent explanation. I have followed your instructions and system thrown an error for duplicate records while creating an index on the table. I checked all the columns of the table but none of them has unique entries. one or the other values are duplicated in each column.
So in this situation, how can proceed further with Masking ? Kindly help.
 

PlantBasedSQL

Hi @Amar_v thanks for this - 
I _think_ there might be 2 potential answers to your question, I believe:

1) Although there are no unique values in any of the columns individually, if you are able to combine a selection of these columns into a composite unique key that could also work (so long as you're not intending to mask any of those columns)

2) If you have a field on the table you're not going to mask but also don't really care about that much for testing / dev purposes, you could run through the table with an update statement and set all the values to unique values, before you create the unique key. e.g. in the example I gave in the video I had a column [NHS number] which if I didn't want to use for testing I could run: 

WITH cte
AS (SELECT NHSNumber,
           ROW_NUMBER() OVER (ORDER BY Customer_ID) RN
    FROM dbo.Customer_Heap)
UPDATE cte
SET NHSNumber = RN

and then apply the:

ALTER TABLE dbo.Customer_Heap
ADD CONSTRAINT UK_CustomerID
    UNIQUE (NHSNumber)

There may potentially be other answers to this but I would need to experiment and they're almost guaranteed to not be 'elegant'.

I hope this helps a little? Thank you very much!

Kindest