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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--Create DB TestDB USE master; GO CREATE DATABASE TESTDB ON ( NAME = TestDB_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TESTDB.mdf', SIZE = 10GB, FILEGROWTH = 5MB) LOG ON ( NAME = TESTDB_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TESTDB_log.ldf', SIZE = 500MB, FILEGROWTH = 50MB ) ; GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
--Create master key USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='$ql$h@ck@12'; GO --Create Certificate using above master key CREATE CERTIFICATE TESTDB_TDECert WITH SUBJECT='TESTDB_Encryption'; GO --Create database encryption key USE TESTDB GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TESTDB_TDECert; GO --Enable encryption ALTER DATABASE TESTDB SET ENCRYPTION ON; GO |
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.
|
1 2 3 4 5 6 7 8 |
--Check TDE status SELECT DB_Name(database_id) As [DB Name, encryption_state, encryption_state_desc, percent_complete, encryption_scan_state_desc FROM sys.dm_database_encryption_keys Go --Check TDE for Tempdb and user datababase SELECT name, is_encrypted FROM sys.databases Go |
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.
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.
|
1 2 3 |
-- Suspend TDE Scan to pause TDE enablement ALTER DATABASE TESTDB SET ENCRYPTION SUSPEND Go |
Below is the output of the above execution.
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.
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.
|
1 2 3 |
-- Resume TDE Scan ALTER DATABASE TESTDB SET ENCRYPTION RESUME Go |
The above T-SQL statement has been executed to resume the TDE scan process. Have a look at this output.
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%.
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.
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.
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.
|
1 2 3 4 |
--Disable TDE ALTER DATABASE TESTDB SET ENCRYPTION OFF; GO |
Here is the output of its execution:
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.
Now, we will suspend the TDE scan process during decryption by running the below T-SQL statement to see its impact.
|
1 2 3 |
-- Suspend TDE Scan to pause TDE ALTER DATABASE TESTDB SET ENCRYPTION SUSPEND Go |
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.
Now, let’s resume the TDE scan to complete the decryption process by running the below T-SQL statement.
|
1 2 3 |
-- Resume TDE Scan ALTER DATABASE TESTDB SET ENCRYPTION RESUME Go |
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.
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.
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.
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.
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
- Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023