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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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:

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



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