question

WaynePrinsloo-2097 avatar image
2 Votes"
WaynePrinsloo-2097 asked TomPhillips-1744 commented

SQL Replication with DDM on subscriber

We have a very large (multi terabyte) Microsoft SQL Server with Transactional Replication. Re-starting replication from a new Snapshot takes multiple days (+/-5 days) - so any changes we make is extremely risky. Because of the size, setting up a test environment is also (nearly) impossible.

We want to enable Dynamic Data Masking (DDM) on the subscriber side only. Will this affect the replication in any way?

I have been unable to find anything in the Microsoft documentation about this, and I am hoping there is someone out there with experience with this who can help me prevent any potential pitfalls.

Any help of advice will be greatly appreciated!
Thank you!

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not your question.

But for very large databases with replication, I highly suggest you use multiple publications and divide the tables in a way to makes logical sense. That way it makes it less likely you must reinitialize the entire database.

0 Votes 0 ·
CarrinWu-MSFT avatar image
2 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @WaynePrinsloo-2097,

Welcome to Microsoft Q&A!

After research I did not find any documents for your situation. But I made a test for this thread as following:
1. created a Transactional Replication, and then alter table to add Mask on an Existing Column in subscriber, the details as below:
89466-node2.png
2. And also check the replication monitor, it seems that the Transactional Replication works find:
89467-node1.png

Because I am not familiar with your product environment, so this answer is for reference only.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



node2.png (80.8 KiB)
node1.png (32.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
3 Votes"
ErlandSommarskog answered

I have not tried it, I have not read any documentation. I need to give that disclaimer.

However, I can't see that Dynamic Data Masking would affect replications. There are two reasons for this: DDM is applied when data is retrieved, not what is stored. And on the subscriber, replication writes. And if it would read, well, then it runs as db_owner and thus have the UNMASK permission.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

WaynePrinsloo-2097 avatar image
0 Votes"
WaynePrinsloo-2097 answered CarrinWu-MSFT edited

Thank you so very much for all your help!!
You are fantastic people and a fantastic community!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You are welcome and thank you for your comment! Have a nice day!

0 Votes 0 ·