How to monitor and manage Transparent Data Encryption (TDE) in SQL Server

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

Few things to be aware of when implementing TDE:

With TDE, The data transmitted over the network is not encrypted and the data at the object level remains unencrypted. In other words, if a user has select access to a table(s) within the TDE enabled database, he/she will be able to read data with simple select statements, as the name suggests, it’s transparent. TDE does not protect FILESTREAM data and any files related to Buffer Pool Extension (BPE) are not encrypted as well, you should use file system encryption tools like windows BitLocker or any other third party tools for this purpose. Another caveat is TDE doesn’t support Instant File Initialization for database files. Also, when TDE is enabled on a user database your tempdb database gets encrypted behind the scenes.

Okay, let’s move on to our topic. In this article, we will see how to monitor and manage TDE progress, not essentially how to setup TDE on a user database. Before moving on to our main topic, here is a quick refresher on how Transparent Data Encryption works.

Image Source: Transparent Data Encryption (TDE)

Enabling TDE on a given database is a very straightforward process. 20,000-foot view of the process is basically creating a DMK (Master DB) which is protected by Service Master Key, Cert (Master DB), DEK (User DB) and enable TDE(User DB) and you are done. But things get little tricky when you are dealing with VLDBs. What if you have a ginormous database (Let’s say a 30 TeraBytes monster) on which you have to enable TDE? Enabling TDE is not instantaneous, the SQL Server Encryption Scanner has to read all the underlying database pages and encrypt them, For a 30 TB database it might take multiple days for SQL Server to encrypt the entire database and we as DBAs should monitor the encryption progress making sure there are no side effects. In this context I am not talking about server resources, I am talking about the impact on the transaction log file when encryption scanner is in progress. So, why should we worry about LDF file when TDE scanner is in progress? Well, SQL Server doesn’t truncate the transaction log file of your database when TDE Encryption Scanner is doing its job. Things get more complicated if you are not allowed to run TDE during business hours and let’s say you have nightly ETL loads or some other scheduled job(s) which generates a considerable amount of log records. In this article, let’s see how to monitor TDE progress and how to manage transaction log when TDE scanner is in progress.

Things to monitor when TDE is in progress:

Disk IO and CPU usage.
Keep an eye on blocking – Encryption_Scan resource can cause blocking.
Last but not the least, keep an eye on your T-Log. As mentioned earlier, when TDE scanner is running the T-Log can’t be truncated. In other words, the log file might grow larger than normal, something to watch out if your server is storage constrained.

How to monitor TDE Progress:

SQL Server keeps track of the encryption progress and we can pull that information by querying sys.dm_database_encryption_keys. Particularly ‘Percent_Complete’ and ‘encryption_state’ are the two columns which are required to understand the progress of TDE. ‘Encryption_state’ column returns an integer value (0-6) which indicates the encryption status of the database and ‘percent_complete’ column tells us percent complete of the DB encryption state change.

Encryption_state(int)	Description
0	No database encryption key present, no encryption
1	Unencrypted
2	Encryption in progress
3	Encrypted
4	Key change in progress
5	Decryption in progress
6	Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed)

Below T-SQL statement can be used to monitor TDE progress/status.

Script 1:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,

encryption_state_desc =

CASE encryption_state

WHEN '0' THEN 'No database encryption key present, no encryption'

WHEN '1' THEN 'Unencrypted'

WHEN '2' THEN 'Encryption in progress'

WHEN '3' THEN 'Encrypted'

WHEN '4' THEN 'Key change in progress'

WHEN '5' THEN 'Decryption in progress'

WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'

ELSE 'No Status'

END,

percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys

The output of above query comes really handy to manage TDE, Now let’s move on to managing TDE.

How to manage TDE scanner:

As discussed earlier, if you are dealing with a VLDB and you are not allowed to let TDE encryption scanner run in business hours or if you see any performance issues and would like to halt the process temporarily, it sounds like your only option is to PAUSE TDE scanner and resume later. But there is no such thing as ‘ALTER database db_name SET encryption PAUSE’ in SQL Server and also we can’t leverage resource governor to lower the priority of TDE scanner because it starts as a background process. So, what are our options here to control TDE? Using a Trace Flag.

Yes, you heard it right. We can pause and resume TDE scanner process using trace flag 5004. When this trace flag gets enabled, SQL Server will continue to keep encryption_state of 2 (Encryption in progress) when we query sys.dm_database_encryption_keys with a percent complete of 0. To resume the TDE scanner process, all we need to do is disable the trace flag and run ‘ALTER DATABASE db_name SET ENCRYPTION ON’. The database is not considered fully encrypted (TDE enabled) until the scanner process is 100% complete and the encryption_state column has changed to value 3 (Encrypted).

Note:

Don’t turn off encryption in panic by running ‘ALTER DATABASE DB_Name SET ENCRYPTION OFF’ if you want to halt the process temporarily. It doesn’t stop/halts TDE, instead, it starts decrypting whatever it encrypted so far which has just as much impact and eventually you have to start the encryption process from scratch. Bummer!

Demonstration:

I have a database named ‘TDE_Monit’ (around 10 GB in size) on which I will be enabling TDE, pause and resume TDE Scanner for our demo purposes. In the below screenshots steps 1 through 3 are to prepare my database for TDE and as soon as I run step 4, I got a message saying ‘command(s) completed successfully’, That doesn’t mean my database got encrypted successfully. SQL Server is just notifying me that the encryption scanner began to run successfully.

Now in another tab, I am running the query (Script 1) provided above and you can see the status as shown below (Encryption State = 2 and percent complete not Zero).

Assuming I did notice performance issue and would like to pause TDE, I enabled trace flag 5004 by running DBCC TRACEON(5004,-1) in a separate window and now when I run my monitoring query, I see below.

As mentioned earlier, when this trace flag gets enabled the encryption state remains status 2 but the percent_complete goes to zero percent (since nothing is in progress). At this point, you should be able to truncate the transaction log if needed.

To resume TDE scanner, all we have to do is to disable the trace flag and re-enable TDE on the database. Note: Just disabling the trace flag will not resume the encryption scan.

DBCC TRACEOFF(5004,-1)

ALTER DATABASE TDE_Monit

SET ENCRYPTION ON;

After running the above SQL statements, TDE process resumed from where it left off as shown in the below screenshot.

After letting it run for a while, TDE process completed successfully and below is the final status.

FYI, below are the series of events from SQL Server error log (Read from bottom to top).

Conclusion:

Enabling TDE is not instantaneous and the progress should be monitored to avoid potential issues. Keep in mind the database is not fully encrypted until the encryption scanner process completes and the encryption_state has changed to 3 and remember there is no rollback for TDE. Basically, once begun, to disable encryption on a database you must allow the encryption scanner process to finish first.

Sreekanth Bandarla

Sreekanth Bandarla is a Database Administrator having about 9 years of experience supporting SQL Servers in Banking, Trading, Industrial, Automotive and Health care domains. He is aMicrosoft certified professional holding active MCITP and MCSA certifications, currently exploring cloud technologies in database stack. He keeps his own blog and in his leisure time he loves playing open world games in Play station.

View all posts by Sreekanth Bandarla

Latest posts by Sreekanth Bandarla (see all)

SQL Server with a Docker container on Windows Server 2016 - April 15, 2019
Simulating a Multi Subnet cluster for setting up SQL Server Always On Availability Groups – lab setup - March 14, 2019
Deploy SQL Server with Cluster Shared Volumes – part 2 - September 19, 2018