> Documentation > Backup and Restore Agents > Databases > Microsoft SQL Server > Solutions

SQL Server Data Masking

Updated

Applies to: 64-bit SQL Server installed on a 64-bit Windows operating system

Commvault software has industry-standard data-masking strategies for SQL Server database table columns. Use data masking when you want to hide sensitive data in a non-production environment, such as a test or development system.

Keep the following considerations in mind when planning data masking operations:

  • This feature is not a replacement for SQL Server native dynamic data masking.

  • Data masking is generally considered to be a destructive, irreversible operation, and is not usually applied to the only copy of important data.

  • Use data masking when you want to hide sensitive data in a non-production environment, such as a test or development system. You can apply data masking to sensitive data in the copied databases.

  • There are no safeguards to deal with a table whose data is being changed by other update operations during the data masking operation. The results are unpredictable and may result in some data not being masked or some data being masked multiple times. Consequently, it is important that the database not be changed by other update operations during the data masking operation.

  • SQL Server data masking is a standalone operation and does not occur as part of another operation, for example a clone or copy operation.

To use data masking, create data-masking policies for SQL Server instances that contain the following information:

  • The database schema

  • The table columns to mask

  • The data masking type to use for the table column. The data masking types that are available depend on the column data type. For example, if the data type is "Date", then you can use the shuffling masking type. See the following table for details.

  • The Commvault software applies the data-masking policies when you run a data masking operation on the database.

The Commvault software provides different data-masking strategies based on the type of data that you want to mask.

Data Type

Available Masking Types

Character

  • Shuffling

  • Format-preserving

  • Fixed String

Number

  • Shuffling

  • Numeric Range

  • Numeric Variance

Date, Raw, Timestamp

Shuffling

Primary key

Foreign key

Decimal type column

  • Format-preserving

  • Shuffling

Data Masking Types

Shuffling

Shuffling applies to all data types. Shuffling takes values from multiple rows in a block (up to 2000 rows) and exchanges the values for the same field by using the Fisher-Yates algorithm. You can use this method for all data types, but we recommend that you do not use it for primary key or foreign key fields because it affects the table relationships.

Format-Preserving Encryption

If you use format-preserving encryption, then the masked output is the same length and format as the input. For example, the masked output of a 20-character user name is a 20-character field.

Fixed String

Fixed string applies to character strings and replaces the original string with a random string that you specify when you configure a policy.

Numeric Range

Numeric range generates a random number between a minimum and maximum value that you specify when you configure a policy. We recommend that you do not use this type for primary key or foreign key fields because it affects the table relationships.

Numeric Variance

Numeric variance takes the initial value and varies it by the percent that you specify when you configure a policy. We recommend that you do not use this type for primary key or foreign key fields because it affects the table relationships.