question

rehaan avatar image
rehaan asked

How to mask a column data for transactional replication at subscriber

I need a copy of my production database to be loaded to Warehouse daily. I am planning to do it through transactional replication. I have tables in my production which has credit card numbers. I don't want this card numbers to be visible in my warehouse. With replication, how can I mask my credit card numbers at subscriber. What is the best way to achieve it? I need a different unique number foe every credit card so that the users can use them while doing joins for their requirements. I have been said that we can modify replication stored procedures for Insert and Update. not sure how to do this. Can someone help me in achieving this please
replicationtransactional-replicationmasking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnSterrett avatar image
JohnSterrett answered
With transactional replication you can deselect columns to be included in your replication process. By default all columns will be selected but you can easily just deselect your credit card numbers column(s) and they will never go over.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rehaan avatar image rehaan commented ·
Hi John, Thanks for your suggestion. I need that column with masking. I am able to replicate Card1 at publisher as Card A at subscriber. But my issue is if card 1 is having 3 transactions at publisher it is replicating as Card 1 , card 2, card 3 at subscriber. I am not able to get as card 1 for all 3 transactions. Can you suggesting anything please
0 Likes 0 ·
JohnSterrett avatar image
JohnSterrett answered
Hi Rehaan, I don't think I am following you. Unless you change the code that happens on inserts update and deletes which is doable but then isn't supported by Microsoft for every record that changes you would have those changes sent down to the subscribers. For example if you change column b on row ID 1 in your publisher that change gets directly replicated so column b on row ID 1 in the subscribers is changed as well. Could you provide some data examples of what you are looking for?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.