March 13, 2024 at 12:13 pm
Hi
I would appreciate your thoughts and suggestions on data security when it comes to the following:
We would like to restore the production database onto our Development environment. But with this comes the exposure of the production data.
We need the transactional data from production for proper testing, etc. But would like to try and obscure the personal / sensitive data.
We could implement encryption on an application level, and we could look at data masking as well - both would obscure the data in the database itself.
But once logged into the application in Development (by QA's and Developers), this data is decrypted and unmasked, thereby exposing it again.
How are you guys dealing with this, without having some complex script running on Development after restore, to scramble the data?
March 13, 2024 at 12:20 pm
the way it is done, on many companies, is to restore the DB on a PROD server (can be same server as the source if server has the required capacity)), mask the data (and I don't mean SQL Data masking as this does not change the data!!!!!), then backup this masked data and restore to the other non prod environments.
March 13, 2024 at 6:17 pm
the way it is done, on many companies, is to restore the DB on a PROD server (can be same server as the source if server has the required capacity)), mask the data (and I don't mean SQL Data masking as this does not change the data!!!!!), then backup this masked data and restore to the other non prod environments.
This is the way we do it - and yes, that means a bunch of scripts to execute that obfuscate the data. I setup an SSIS project that calls multiple procedures concurrently and have the process down to an average of about 7 hours on a 7TB database - which includes restoring the database, obfuscating the data, backing up the obfuscated database.
Restore and backup take about 3.5 hours - so another 3 to 4 hours to actually obfuscate the data.
You could look at purchasing a tool - Redgate has one, but it is a bit expensive.
If you decide to roll your own - avoid using cursors and batch the updates instead of attempting to update the full table all at once.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply