Monitoring for Problems

The encryption of your existing data occurs as a background process referred to as the encryption scan, but it will consume resources while it runs, so if you are implementing TDE against a system with large databases where performance is critical then you will want to either run it in a period of quiet (or down time), or you will want to monitor to check that encryption isn’t impacting your system too much. Experience suggests that it shouldn’t be a problem unless your server is already under strain.

There are a few things to look out for if you are monitoring during the encryption scan:

• CPU and IO, both these could take a hit
• You may want to look out for blocking caused by encryption, you can do this by checking the sys.dm_tran_locks view where the resource_subtype is “ENCRYPTION_SCAN”. Here is an example of the SQL for that:

SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'ENCRYPTION_SCAN';

• Monitor transaction log usage with DBCC LOGINFO. While the encryption scanner is running the transaction log can’t be truncated and VLFs marked for re-use. This could mean the transaction log might grow larger than normal, so you need to watch out if you are constrained for disk space.
• If your database is synched to a secondary in an HA scenario, for example as part of an Availability Group or through log shipping, you may want to check your synch process remains healthy during the encryption scan. We talk a little bit about working with TDE and HA later on in this series.

What if you run into any performance problems during the scan?

First things first – don’t turn encryption off.

ALTER DATABASE TestTDE SET ENCRYPTION OFF;

This isn’t going to stop the encryption scan, rather it’s just going to change direction. So now it will begin decrypting everything it’s just encrypted, that’s likely to have just as much impact, then sooner or later you’re going to have to start again.

What we want to do is to pause the encryption scan. Prior to SQL Server 2019 there was no direct command to achieve this, however there was a trace flag that could be used to achieve much the same thing. Let’s look at both methods. First, if you are on SQL Server 2017 or lower you can pause the encryption scan with the following command:

DBCC TRACEON(5004);

When you do this, if you then query dm_database_encryption_keys you will see the database is set to a state of 2 (Encryption in Progress) and the percent_complete column will show zero – as nothing is currently in progress.

When you wish to begin the scan again, you need to disable the trace flag and then set encryption on again (even though it’s not technically off):

DBCC TRACEOFF(5004,-1);
ALTER DATABASE TestTDE SET ENCRYPTION ON;

When you do this the scanner will pick up where it left off, for instance if it had got to 50%, then it will then continue from there.

It’s important to note that the trace flag doesn’t actually turn TDE off. In fact, if you add new data, or update data and it gets written to disk while the trace flag is enabled, the data will still become encrypted. The trace flag just pauses the background process of converting all the data already on disk.

Note that this method will pause the encryption scan for all databases that are in the process of being encrypted. The SQL 2019 method is better as it gives us the control to pause the scan for a specific database using the following code:

ALTER DATABASE TestTDE SET ENCRYPTION SUSPEND;

To set the encryption running again from where it left off, we just use the following command:

ALTER DATABASE TestTDE SET ENCRYPTION RESUME;

Just like with the trace flag, this method doesn’t turn TDE off, it just pauses the background process of encrypting existing data. New data will still be written to disk encrypted. 

Along with this new functionality, SQL 2019 also added extra columns to the dm_database_encryption_keys view to help you manage the process of pausing and resuming the encryption scan. These columns include encryption_scan_state_desc which will tell us the state of the scan, for instance if it is running or suspended. We also have encryption_scan_modify_date which tells us when the scan was last modified, we can use that to tell when a scan was suspended or resumed.

If performance impact is an issue for you with the scanning process, you can use one of these methods to incrementally run the scan out of hours, pausing it when you want to ensure maximum performance of your systems.

What if the Encryption Scan Fails?

 It is possible for the encryption scan to fail. The most likely scenario is a failure due to corruption in your database. Because the scan is a background process you won’t get an error message telling you that it has stopped, so it is good to use the methods we’ve looked at in this chapter to check when the scan is complete.

Prior to SQL 2019 a failure would exhibit itself by the encryption_state column showing as 2 (in progress) in dm_database_encryption_keys and the percent_complete column showing as zero. Usually the 5004 trace flag would also have been enabled to prevent the scan from resuming.

From SQL 2019 we have a little more information and can see in dm_database_encryption_keys that  encryption_scan_state_desc is set to aborted and can tell when that happened.

In either case your first action should be to check the database for corruption and resolve that if there are any issues. After that you can attempt to restart the encryption scan using the methods we’ve just looked at. If you have no corruption, and the scan still fails then you’ll need to reach out to Microsoft for support, but that’s an unlikely scenario.

Taking Backups While Encryption is in Progress

You can continue to take backups while the scanner is running to encrypt your existing data. However, it is important to understand that until that process is complete your backups will not be fully encrypted.

What will happen in the meantime is that a mixture of encrypted and unencrypted data will get written to your backup files. Only backups taken after the TDE encryption scan is complete will be fully protected.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.