Before running discovery or masking operations, ensure that the SQL Server database is properly configured. Proper database configuration helps improve masking performance and prevents failures during large update operations.

The following recommendations help prepare SQL Server databases for Continuous Compliance operations.

Transaction log configuration

SQL Server transaction logs record all database changes. Masking operations update large volumes of data, which can cause transaction logs to grow quickly.

Transaction logs may also temporarily contain unmasked copies of data during masking operations.

Recommendation

Configure the database to use the Simple Recovery Model during masking operations.

The Simple Recovery Model automatically reclaims transaction log space and reduces logging overhead, which can significantly improve masking performance.

Example command

ALTER DATABASE <database_name>
SET RECOVERY SIMPLE;

You can also configure the recovery model through SQL Server Management Studio (SSMS) using the database properties dialog.

Consult your database administrator before making this change, as the Simple Recovery Model disables transaction log backup capabilities.

SQL Server memory configuration

Masking operations generate a high number of database updates and transactions. SQL Server should be tuned to handle a high transactional workload.

Recommendation

Ensure that SQL Server is configured to use the maximum available memory on the server, or a memory allocation appropriate for the workload.

Database administrators should evaluate and configure memory settings based on expected masking activity.

Table key configuration

Masking operations require a reliable key to uniquely identify rows when updating records.

For optimal performance, tables should have a clustered primary key or another unique key that can be used to locate rows efficiently.

Continuous Compliance uses the following order when selecting a key for masking operations:

  1. Logical key – A user-defined unique key.

  2. Primary key – If a primary key exists, it will be used.

  3. IDENTITY column – If no primary key exists but an IDENTITY column is present, it will be used.

  4. Generated identity column – If no key exists, a temporary identity column may be created during masking operations.

If a primary key column is masked, the primary key index may need to be temporarily dropped during the masking process.

Indexes, constraints, and triggers

Indexes, constraints, and triggers can slow down large update operations during masking.

Recommendation

Temporarily disabling or dropping indexes, constraints, and triggers during masking operations can significantly improve performance.

After the masking job completes, these database objects can be recreated.

Masking primary keys and foreign keys

Masking operations can be performed on primary key or unique constraint columns, but certain conditions must be met.

When masking primary keys or unique constraints:

  • Indexes and constraints must be temporarily dropped.

  • The masking algorithm must generate unique values.

When masking foreign keys:

  • Both the parent and child tables must be masked using the same algorithm.

  • The algorithm must generate unique values.

Failure to follow these requirements may result in constraint violation errors during masking jobs.

Limitations

Identity columns cannot be masked directly. Additional steps may be required depending on the database configuration.

Consult your database administrator if identity columns must be modified during masking operations.

Masking user privileges

It is recommended to create a dedicated database user for masking operations. This user should have the necessary privileges to read and update data within the target database.

Required database roles include:

  • db_datareader

  • db_datawriter

  • db_ddladmin

Example SQL commands to create a masking user:

USE [mask_db];
CREATE LOGIN [mask_user] WITH PASSWORD = 'password';
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];

Ensure that the credentials configured in the connector correspond to a user with these privileges.

Last updated
Oct 8, 2026