Preparing SQL server database for profiling and masking

Before masking data, the database(s) must be prepared. This page provides recommendations on configuration, database memory requirments, table keys, and roles and permissions.

Transaction Logs

Important: Transaction Logs will likely contain the unmasked copies of the data. This is the key reason for setting SQL Server to Simple Recovery Model and the Transaction Logs should also be cleared (if not done through Simple Recovery Model).

Reducing the overhead of transaction logging and the size of the files before checkpoints significantly increases the masking speed

SQL Database Simple Recovery model - This will automatically reclaim log space to keep space requirements small and remove unmasked data copies. Database operations that require transaction log backups are not supported by the simple recovery model.

How exactly do I make this change?

Either (a) use SQL Server Management Studio to open the DB properties dialog box and select the “simple recovery model” or (b) issue the SET RECOVERY SIMPLE statement from a SQL query tool. Please see this reference for more details.

SQL Server Memory and Tuning

The SQL Server needs to be tuned for high transactional load (all records in all masked tables will be modified).

The SQL Server should be configured to use the maximum available on the server.

Table and Masking Configurations

In-Place (IP) masking and table keys
A key is a unique, non-null value that identifies a row in the database table. When masking using In-Place, this key looks up the row to update the masked record. For optimal performance and operation, the best is to have a Clusterd Table where the Primary Key is indexed with a Clustered Index.

Primary Key (PK): If there is a Primary Key, the Continuous Compliance Engine will use this key.
IDENTITY column: If there is no PK, but there is an IDENTITY column on the table, the Continuous Compliance Engine will use this. If this column is not indexed, the Continuous Compliance Engine will add a non-clustered index called i_MASKING_GENERATED_IDENTITY_TMP.
Logical Key (LK): You can add a Logical Key to override the key used. The Logical Key can be one or multiple columns. The Logical Key has to be unique and can’t have NULLs.
No Key: If there is no Key, the Continuous Compliance Engine has an automated feature that will add an IDENTITY column called MASKING_GENERATED_IDENTITY_TMP. This column is removed at the end of the masking job execution. This column will be indexed with a non-clustered index called i_MASKING_GENERATED_IDENTITY_TMP.

An alternative would be using On-the-Fly (OTF) masking.

Dropping Indexes, Constraints, and Triggers

For optimal performance and operation, it is important to use Enable Tasks to Drop Indexes, Constraints, and Triggers.

In-Place (IP): Only objects on masked columns and tables are dropped.
On-the-Fly (OTF): All objects in the Rule Set will be ‘processed' (even if no algorithms are assigned. All Indexes, Constraints, and Triggers will be dropped.

Masking Unique Constraints, Primary Key, and Foreign Keys

It is possible to mask Primary Keys and Unique Constraints. When masking these, Drop Indexes and Constraints must be enabled and the Algorithm must mask to a Unique Value (such Dlpx-Core; CM Alpha-Numeric).

When Foreign Keys are masked, both the Parent and the Child table must be masked with the same algorithm and the algorithm must mask to a Unique Value (such Dlpx-Core; CM Alpha-Numeric). Drop Indexes and Constraints must be enabled.

WARNING! Not following this will (99.9% chance) cause the job to fail with Constraint Violation Errors.

Limitations

It is not possible to mask Identity columns - special steps are required. For details see KBA.

Masking User

Creating a masking user and privileges
It is highly recommended to create a database user, and possibly a role, for use by the Masking Engine. This user should be created in a Non-Production environment. When using Continuous Data and Provisioning a Masked vDB, this user has to be available on the source that which the vDB is created.

The following permissions are needed:

CODE

db_datareader
db_datawriter
db_ddladmin

SQL commands to add a user with the required privileges:

ACTIONSCRIPT3

USE [mask_db];

CREATE LOGIN [mask_user] WITH PASSWORD=N'delphix123';
CREATE USER [mask_user] FOR LOGIN [mask_user];

ALTER ROLE [db_datareader] ADD MEMBER [mask_user];
ALTER ROLE [db_datawriter] ADD MEMBER [mask_user];
ALTER ROLE [db_ddladmin] ADD MEMBER [mask_user];

For more information, read the Masking an Identity Column KBA article.