Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted January 8, 2020

Suspending and Resuming Transparent Data Encryption (TDE)

By Greg Larsen

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:


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


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM