Transparent Data Encryption (TDE), Traceflag 5004, and interrupting encryption scanning

Hello Blogreaders,

Here is another post from Grant Carter to help with your TDE efforts on large databases.   Have a read and consider.

 

Recently I was engaged with a customer who runs a system that is very visible and busy during the business day.  The customer wanted to enable TDE (Transparent Data Encryption), but the database was very large, and they realized they would not be able to complete the initial scan and encryption of the data before the business day started.   The concern was that the encryption scan would bleed into the business day, and would cause disruption for their customers.  Due to the nature of this customer’s business that was unacceptable.

The customer reached out and asked if there is a way they can either pause or lower the priority of the initial encryption scan to reduce the impact to the system during the business day.

SQL Server Transparent Data Encryption is either on or off.  There is no command like alter database <database name> set encryption pause.   Another idea was to use resource governor to lower the priority of the process while it does the encryption scan.  However, when the encryption scan process starts, it starts as a background process.  The problem with this is it uses the internal workload group and resource pool to get its resources from.   You can’t classify a background process, which means you can’t put it into a user defined workload group.  Using resource governor to lower the priority is out as a possible solution.

On the face of it, it appears that there is no way to control the encryption and you will just have to live with the impact until the encryption scan finishes.   However….

Traceflag 5004 stops the encryption scan and causes it to exit and in circumstances where there is a material impact to the database, this is a good option to manage the encryption scanner.

With traceflag 5004, here are the considerations for using it.

• The traceflag can be turned on or off without a restart.
• The database will continue to keep an encryption_state of 2 in the sys.dm_database_encryption_keys with a percent complete of 0 while the traceflag is enabled. Keep in mind the database is not fully encrypted until that scan completes and the encryption_state has changed to 3.
• Data that hasn’t been encrypted yet, will not be encrypted until one of two things occur.

o   The page gets updated by way of normal transaction processing.   During that point, it will get encrypted

o   The traceflag is disabled and the scan is resumed.

• To resume the encryption scan, the traceflag must be disabled and then alter database <database name> set encryption on ran. Just disabling the traceflag will not resume the encryption scan.

The encryption scanner process must complete, so disabling it and not finishing the scan is not an option.   Once you enable TDE, you need to try to complete the encryption scan process and get the encryption_state value to 3 as soon as you can, but if you have a limited window or need to truncate the transaction log, then this traceflag can assist.

When encrypting the database perform the following:

• Monitor CPU utilization and ensure you workload can run. The encryption scan is running as a background process, but it will take some resources while it runs.
• Monitor transaction log usage with dbcc loginfo. When the virtual log files fill up and are active, the status flag will be set to 2.   While the encryption scanner is running, the transaction log can’t be truncated and have the VLFs marked back to 0 for reuse.   Once a significant number of virtual logs are marked with a status of 2, you may want to stop the encryption scanner using traceflag 5004 and then truncate the transaction log.
• Monitor for blocking as described by https://blogs.msdn.microsoft.com/psssql/2016/04/19/why-do-we-see-encryption_scan-locks-in-a-sql-server/. If blocking becomes too problematic, you can resolve this by setting the traceflag and stopping the encryption scanner.   However, you will have to finish the scan at a period where the impact is reduced.

Should you decide to no longer encrypt the database, there is no “rollback”.   You must allow the encryption scan process to complete, before you can disable the encryption.   Once you disable the encryption, the entire scan process will run again to decrypt the data pages that were encrypted.

Completely Off Topic:

Obscure Item of History

The longest war in history is the “Three Hundred and Thirty Five Years’ War” between the Netherlands and the Isles of Scilly.  The war started on March 30, 1651, and peace was not declared until April 17, 1986.   Ironically, not a single shot was fired and there were no casualties.

Grant Carter is a Senior Premier Field Engineer for Microsoft based in Boise, Idaho.

Email: grant.carter@microsoft.com