1

We used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments with a backup of the production database backup. Sometimes we need to share the database backup with the external vendors as well.

How to Apply Static Data Masking With Replication of Databases , i.e for every change the replication database to to apply mask and save masked records in staging, test, UAT etc.

Is there any tools or process or methods available ?

Improve this question
2
  • Can you please explain what you mean by 'Replication of Databases'? Do you mean transactional replication?
    – SqlWorldWide
    Apr 9, 2021 at 17:18
  • Yes @SqlWorldWide
    – Ramakrishna.p
    Apr 9, 2021 at 17:49

1 Answer 1

Reset to default
2

These are out of the box features of SQL Server so you'll essentially need to follow the Microsoft Books Online and other resources on setting them up. Replication has a lot of small steps and basic prerequisites but it's essentially a wizard, so it's rather simple if you just follow the documentation carefully.

Here's some helpful references for setting up Replication:

  1. Microsoft Books Online - SQL Server Replication

  2. Quick walkthrough on setting up Replication

  3. More in depth walkthrough for setting up Replication

And here's some good resources on implementing Static Data Masking:

  1. Microsoft Books Online - Static Data Masking for Azure SQL Database and SQL Server

  2. SQL Server Static Data Masking Example

  3. Static Data Masking in SSMS 18

Please note that Static Data Masking is only available in SQL Server 2019 (or equivalent Azure SQL Database version). So if you're on an older version of SQL Server, the alternative option available is called Dynamic Data Masking.

Improve this answer
6
  • @JD Is Static Data Masking For Azure SQL Database And SQL Server available , we are tried same version as you mentions but we did not see any options in SSMS
    – Ramakrishna.p
    Apr 9, 2021 at 16:01
  • @Ramakrishna.p I think it's available, in a somewhat beta version, but you need to have the latest version of SSMS installed for the option to show up in the UI.
    – J.D.
    Apr 9, 2021 at 16:05
  • @JD Do we need enable any Settings in SSMS , we installed as per your shared article , but we could not see options in tasks.
    – Ramakrishna.p
    Apr 9, 2021 at 16:46
  • @Ramakrishna.p I'll have to take a look when I get off work later. I don't believe you need to enable anything special, but I'm not positive. You're definitely using SQL Server 2019?
    – J.D.
    Apr 9, 2021 at 18:23
  • 1
    SSMS Static Data Masking was removed. One option is to apply changes with replication using custom stored procedure that masks data (learn.microsoft.com/en-us/sql/relational-databases/replication/…) or configure Dynamic Data Masking and write an ETL job that reads the masked values.
    – David Browne - Microsoft
    Apr 10, 2021 at 15:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.