Making production data accessible to developers via masking

Question

We want to provide developers in our organization masked data from production to help troubleshoot production issues. What would be the best way to approach it?
I've read this article https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server.

Is it a reasonable approach?

Answer 1

Is it a reasonable approach?

Yes. That's what the (removed) "Static Data Masking" component in SSMS did. A simple way to implement this is to copy data from production using an identity for which Dynamic Data Masking is enforced on the production database, using the Copy Database Wizard or a custom script.

Answer 2

You will not be able to do this using built-in replication. There are no hooks in the replication chain that allow you to manipulate the data as it passes by.

What has worked for me in the past is a copy, mask, publish approach. Take a backup of the production DB and restore it as a new DB. Since this still contains sensitive data I would keep it in a production environment. Run your masking routines against this newly-restored DB then take a backup of this. Itis this second backup, containing masked data, which is restored to development. The cycle can be repeated as often as neede, though ask yourself how fresh do you really need dev data to be.

I looked at putting the masking in a continuously running ETL job, but the extra work wasn't worth it for our use case.

Answer 3

Taking a backup copy, running custom scripts, then moving this backup to a lower environment is both fragile and can be very time consuming when working with a large database.

There are several 3rd party tools available that will mask (or even create synthetic versions of your data) inflight as it's transferred to a lower environment. You can hook it into a CI/CD pipeline (or just scheduled on a cron job). (full disclosure I work for a company that makes one of these tools, tonic.ai, so I am a bit biased)

Regarding refresh frequency, depending on how fast your development cycle is, having an up-to-date dev environment can be very important. Slow moving enterprises may get away with a monthly refresh cadence, but if you're pushing code weekly or daily, you should be updating your development environment to the latest production schema and refreshing data as often as possible.