Scrambled Database refreshes in SQL ServeR


It is common task for SQL Server Database Administrators, for that fact, any DBAs to perform refreshes from Production environment to UAT/CAT/Test/Dev/QA environments. Production databases consists of PII or critical data which unauthorized users should not be able to view, as it violates security and compliance standards. So, it is important for DBAs to make sure that the data refreshed should SCRAMBLE or Obfuscate the critical data so it does not make any sense to the users viewing or making changes to this data. This process is known as SCRAMBLING. 

Normally databases refreshes are performed via scheduled jobs to avoid manual efforts and reduce the time taken to complete the task. As part of the refreshes, the production database is backed up and copied to lower environments and restored and then scripts are run to removed prod users and adding users and permissions specific to the environment. During this process it is important to make sure that the database is not allowed access to any users until we complete the data scrambling process. The data scrambling can be done in below ways.

  1. Identify and change the PII and other critical data by replacing existing values with random values matching the data type and length.
  2. Use third party tools to perform this scrambling process.
  3. Prepare data set for lower environment and replace them on the restored databases.
  4. Encrypt the PII and other critical data, so the non production users cannot decrypt the encrypted data as they would not have access to the encryption keys. But this will cause problems to the applications as they fail to access this data. This will work only if the testing is done on non PII data only.

Next time when you get a request for database refresh make sure to pitch this discussion in with the application teams and management about the importance and compliance and get this implemented. Normally there is no specific responsibility on who should own this task of scrambling the data. Since the application teams are well aware of the schema so they would be the best teams to create the scripts for scrambling the data and DBAs could use that script in the refresh jobs. If application team needs help, DBAs can offer created sample script on how can we scramble the data.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.