Manvendra Singh
Check encryption state after TDE SCAN

Understanding the TDE SCAN process in Transparent Data Encryption

September 30, 2021 by

Transparent Data Encryption is a SQL Server feature that is used to protect data stored in SQL Server databases. The process to enable TDE on any user database is a straightforward method. Once we enable TDE on any user database, SQL Server performs a scan for each data page into the buffer pool and then writes the encrypted pages back to disk. The process of scanning each data page is known as TDE scan. Any database is fully encrypted once the TDE scan will be completed for all data pages of the database.

If you have enabled or disabled Transparent Data Encryption on any user database, a TDE scan must be performed irrespective of the workload running on your SQL Server instance. It is very important to note that execution of such operations should be scheduled during off business hours if we have big databases because the scan is a very costly process, and it will add additional CPU, memory, and IO overhead to the system. This was a big pain, and we don’t have any control over this process before SQL Server 2019.

Microsoft has addressed this issue in SQL Server 2019 and introduced SUSPEND and RESUME options for the Transparent Data Encryption scan process. Now DBAs have more control over this scan process, and they can suspend the scan process when it is causing severe performance impact on the database system and later can resume it during off business hours to complete the TDE process.

Today I will show you this demo by suspending and resuming the TDE scan process in this article.

Preparation

I will use DMV sys.dm_database_encryption_keys to track the progress of TDE configuration. This DMV captures very useful information like encryption state of the database, encryption scan state, encryption percent complete, etc.

Let’s create a database named “TestDB” and then will enable TDE on this database to track its TDE scan progress. Use the below T-SQL statement to create the database.

Analyze TDE SCAN during Enabling TDE

I will capture its TDE scan progress using DMV sys.dm_database_encryption_keys and then will suspend the scan process followed by resuming it to complete the TDE scan process.

Let’s enable TDE on the newly created database TESTDB by running the below statements.

Transparent Data Encryption has been enabled on the newly created database TESTDB. Check encryption state of TDE by fetching details from DMV sys.dm_database_encryption_keys.

Have a look at this output. Here, encryption_state is showing as “ENCRYPTION IN PROGRESS” and encryption_scan_state_desc is showing as RUNNING, percent_complete is also showing as only 6%. It means the TDE scan is in process, now I will SUSPEND this scan and show you this status again by running the above T-SQL statement.

Check TDE SCAN during enabling TDE

Let’ suspend the TDE scan by executing the below ALTER DATABASE statement. Do not suspend if your system is normal and are not facing any performance issue. Suspending this scan will pause the transparent data encryption process, which needs to be completed later by resuming its scan process.

Below is the output of the above execution.

Suspend TDE SCAN

Let’s check the TDE scan state again by running the same T-SQL statements showing in image 1. One thing you will also note in the below output is that status for TDE enablement has changed as enabled in sys.databases system object whereas it is not fully enabled internally for the database and has been paused at 6%. A database is fully encrypted once its encryption scan process will fully complete.

We can see, ENCRYPTION IN PROGRESS state is still there for database TESTDB, percent_complete is set to 0 as it has been paused and its scan state is showing as SUSPENDED because we have suspended the TDE scan process by running the above ALTER DATABASE statement.

Check TDE SCAN state after suspending it

Now, let’s enable the TDE scan process by running below ALTER STATEMENT. Make sure to do it during off-business hours or after getting proper approvals from a business if you have been suspended TDE scan process because of performance issues.

The above T-SQL statement has been executed to resume the TDE scan process. Have a look at this output.

Resume TDE scan process

Now, check the TDE scan status again. The scan process has started since it was paused and its scan state has again changed to RUNNING now as per the below image, percent_complete is also showing as 44%.

check TDE scan state after resuming it

Let this scan process run and complete the TDE enablement process of user database TESTDB. Once the scan will be completed the status of the scan will be changed to COMPLETE and the TDE state will change from ENCRYPTION in PROGRESS to ENCRYPTED.

Check encryption state after TDE SCAN

The whole process which we did from enabling TDE on user database TESTDB to suspending its scan process and finally resuming scan to complete TDE encryption will be logged in SQL Server error log. Have a look at the below screenshot where you can analyze the sequence of events logged there.

The error log has also captured an IO pressure on the system during the TDE scan process. That’s the reason it is always recommended to do careful planning before enabling it to production because the scan process of transparent data encryption is a resource-intensive operation that perform a lot of IO operation during reading each page in buffer and then saving it back to disk. These operations increase the significant load on CPU, memory, and IO.

SQL Server error log for TDE SCAN

I have shown you how to suspend and resume the TDE scan process if you are facing severe performance issues during TDE enablement on a user database. Now, let’s analyze the same behavior during disabling TDE from the same user database TESTDB in the next section.

Analyze TDE SCAN during Disabling TDE

SQL Server performs TDE scan during encryption and decryption both operations. I will show you and prove this during decrypting the TESTDB database in this section. TDE has already been enabled on the user database TESTDB in the above section. Let’s turn off Transparent Data Encryption from this database and analyze the scan process. I will not remove any key or certificate as part of this disablement as we don’t want to remove Transparent Data Encryption from our database, but we are just temporarily disabling TDE to analyze its scan process. DO NOT perform this in your production for testing purposes. If you want to remove TDE completely from the SQL Server instance, then I would suggest reading another article attached here, Remove Transparent Data Encryption (TDE) from SQL Server user databases.

Run below ALTER statement to turn off TDE from database TESTDB.

Here is the output of its execution:

Turn off TDE from user database

Once the above T-SQL statement will be executed, the TDE scan will start to decrypt the database TESTDB. Let’s get it confirmed by looking into DMV sys.dm_database_encryption_keys. Here is the output which shows the outcome as per our expectations. TDE scan has been initiated for database TESTDB. Other details like encryption state showing as “DECRYPTION IN PROGRESS”, percent complete is also showing its numbers in below image.

Check decryption state during TDE scan after disabling TDE

Now, we will suspend the TDE scan process during decryption by running the below T-SQL statement to see its impact.

Suspend TDE SCAN during decryption

Let’s check DMV sys.dm_database_encryption_keys again to see the state of the scan and its encryption state.

Output is showing like what we have captured during enabling Transparent Data Encryption for database TESTDB. The below image is showing that the TDE scan is suspended, and its state is showing as DECRYPTION IN PROGRESS. Again, sys.databases object is showing that TESTDB is not encrypted whereas its process to decrypt has been paused using ALTER statement. Always look into the above DMV to get the latest state of Transparent Data Encryption and not the system object sys.databases.

Check TDE SCAN state after suspending it

Now, let’s resume the TDE scan to complete the decryption process by running the below T-SQL statement.

Once above statement will execute, check its state in DMV sys.dm_database_encryption_keys. Here is the output, we can see the encryption scan state has changed from SUSPEND to RUNNING now. Percent complete is also showing its number.

Check scan state after resuming it

Once this TDE scan will be completed then the user database TESTDB will be decrypted and TDE will be fully turned off from this database.

Below is the screenshot post TDE scan completion where the encryption state of the database is showing as UNENCRYPTED and the encryption scan is showing as COMPLETE.

Check encryption state after scan completion

SQL Server logs have captured another poor IO performance log during the decryption process in the SQL Server error log as shown below image. Also, it has logged all Transparent Data Encryption operations in its error log file.

SQL Server error log during decryption process

Conclusion

I have discussed an internal process TDE SCAN of Transparent Data Encryption in this article. Whether you enable TDE or disable TDE on any user database, TDE scan is a must-go process internally to complete encryption or decryption of the user database. Although this internal process was already there in earlier versions of SQL Server, SQL Server 2019 has given us more control over this process to suspend it and resume it as per our desired need.

I have explained this process in detail while enabling or disabling TDE in the above sections. You should always plan carefully while doing these activities. Never perform such activities during business hours. Take proper approvals before doing anything on your production systems.

Please share this article and comment your feedback in the comment section so that we can improve in a better way.

Manvendra Singh
Transparent Data Encryption (TDE)

About Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC. He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects. You can also find him on LinkedIn View all posts by Manvendra Singh

168 Views