SQL Server TDE Initial Scan Suspend and Resume Options

By: Filip Holub | Updated: 2022-09-07 | Comments | Related: > Encryption

Problem

Transparent Data Encryption (TDE) was introduced in SQL Server 2008 (Enterprise version only). It works by encrypting database pages at the storage subsystem level. The Data Files (.mdf), Transaction Log Files (.ldf), and Backup Files (.bak) are encrypted by a Database Encryption Key (DEK) where TDE is enabled, which protects all data "at rest."

In SQL Server 2016, Microsoft added a couple of enhancements, including using Backup Compression on a database where TDE is enabled, encrypting memory-optimized filegroups, and the use of Intel’s Westmere architecture supporting Advanced Encryption Standard New Instruction (AES-NI) that can impact performance by only 2-3% for CPUs if you are using TDE.

When enabling TDE on a very large database the encryption scan can take a significant time and block activities on the database. In this article, we look at how to suspend and resume the encryption scan for a database.

Solution

SQL Server 2019 introduced another TDE enhancement, Suspend and Resume initial scan for Transparent Data Encryption. With this new feature, you can pause the scan during business hours or heavy workloads. In Dynamic Management View, sys.dm_database_encryption_keys a new column was added, encryption_scan_state, that shows the current state of the encryption scan. The second new column in this DMV is encryption_scan_modify_data, which informs you about the date and time of the last encryption-scan state change.

Let’s take a look at how to use this enhancement.

Create Demo Database

In this demo, I will use this sample database Stack Overflow Database (Medium: 50GB database as of 2013). After the download, extract the file and attach the database using the following steps:

Step 1: Right-click on Database and choose Attach…

Step 2: Select the extracted Data Files and Transaction Log File and click OK.

Step 3: As you can see below, the StackOverflow database was successfully added.

Enable Transparent Data Encryption (TDE)

Step 1: Create a master key.

Step 2: Create or obtain a certificate protected by the master key.

Step 3: Create a database encryption key and protect it using the certificate on the StackOverflow2013 database.

USE master;
GO

-- step 1
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'VeryStrongPassword123/*-';
GO

-- step 2
CREATE CERTIFICATE MyServerCertificate WITH SUBJECT = 'My DEK Certificate';
GO

-- step 3
USE [StackOverflow2013];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCertificate;
GO

Note: It is strongly recommended to backup a newly created certificate in a production environment!

Enable, Suspend and Resume Scan of TDE Encryption Pages

Step 1: Enable the database to use encryption using the following command:

USE master
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION ON;

Step 2: As mentioned earlier, SQL Server starts with scan pages on disk and encrypts them. We can see the scan progress with the following DMV command:

SELECT DB_NAME([database_id]) AS 'Database Name',
   [encryption_state],
   [percent_complete],
   [encryption_state_desc],
   [encryption_scan_state],
   [encryption_scan_state_desc],
   [encryption_scan_modify_date],
   [create_date],
   [regenerate_date],
   [key_algorithm],
   [key_length],
   [modify_date],
   [set_date],
   [opened_date],
   [encryptor_thumbprint],
   [encryptor_type]
FROM [sys].[dm_database_encryption_keys]

The returned information from the DMV shows that TempDB was encrypted automatically and database StackOverflow2013 is in progress of encryption – ENCRYPTION_IN_PROGRESS.

Step 3: We can pause the scan of pages using the following command:

USE master
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION SUSPEND;
GO

If we run the previous SELECT statement, the column encryption_scan_state_desc confirms that the scan of pages is SUSPENDED and column percent_complete is back to 0.

Step 4: If we want to continue with encryption of pages, we can execute the following command - RESUME:

USE master
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION RESUME;
GO

Scan of pages continues successfully.

We can see that Processor Time (green line) and Disk Write Time (black line) have heavy utilization during scanning pages.

Lastly, we can restart the SQL Server instance during the scanning of pages (RESUME state or SUSPEND state). SQL Server will continue after starting the service and write information to the ERROR log as you can see below:

Next Steps

Here is some additional reading:

About the author

Filip Holub is a big SQL Server enthusiast from the Czech Republic with 5 years of DBA experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-09-07

Suspend and resume initial scan for Transparent Data Encryption (TDE) in SQL Server