When you turn on Transparent Data Encryption (TDE), the SQL Server database engine asynchronously starts encrypting data pages. For a large database, it may take a while to encrypt the entire database. Encrypting a database is an IO intensive process. Therefore you may find when you turn on TDE your system becomes IO bound and query performance starts to suffer.
Well don't fret if you have this problem, because in SQL Server 2019 you now have the option to suspend and resume the asynchronous TDE operation.
Starting with SQL Server 2019 the team at Microsoft realized that the asynchronous encryption process of TDE can cause IO contention, so they added a new feature that allows you to suspend and resume the TDE encryption process. Therefore, if you find you have IO contention issues when you enable TDE for a large database, you can now turn off the encryption until a time when the IO associated with the encryption process will not impact the performance of other critical SQL Server queries.
To suspend TDE all you need to do is run the following command:
ALTER DATABASE <Your DB> SET ENCRYPTION SUSPEND;
Where "<Your DB>" is the name of the database that is being encrypted for TDE.
After issuing the command above SQL Server will suspend the asynchronous encryption process.
It is easy to resume this process by running the following command
ALTER DATABASE <Your DB> SET ENCRYPTION RESUME;
After issuing this command the database engine will start resuming the TDE encryption where it left off.
This is a great feature if you find transparent data encryption is causing IO contention with your normal user queries.
In addition to adding this new feature, Microsoft also added the column encryption_scan_date and encryption_scan_modification_date to the Dynamic Management View (DMV) sys.dm_database_encryption_keys so you can monitor the asynchronous encryption process. The encryption_scan_date column shows the current state of the scan, whereas the encryption_scan_modification_date shows the date and time when the encryption state has changed.
# # #
» See All Articles by Columnist Gregory A. Larsen