How to Enable, Suspend, and Resume TDE Encryption Scans in SQL Server Always On

Transparent Data Encryption (TDE) is a crucial security feature in SQL Server that protects data at rest by encrypting database files. In an Always On availability group, managing TDE encryption scans efficiently ensures minimal performance impact and seamless data protection. This guide will walk you through enabling, suspending, and resuming TDE encryption scans in SQL Server Always On, helping you maintain security while optimizing system performance. Whether you need to temporarily pause encryption scans for maintenance or resume them after an interruption, these steps will ensure smooth database operations.

For encrypting an SQL Server database, please review Encrypt an Existing MS SQL Server 2019 Database in an Always On Environment Using TDE

1. Enable encryption

1.1- On the primary database, Start (enable) Encryption

Use the following command to enable encryption for the databases on the primary replica.

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

SQL Server begins by scanning pages on disk and encrypting them. The progress of this scan can be monitored using the following query on the primary and secondary databases.

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 query shows that TempDB was encrypted automatically and the StackOverflow2013 database is in progress of encryption – ENCRYPTION_IN_PROGRESS.

1.2- On the primary database, pause the encryption

1.2- On the primary database, pause the encryption

We can pause the scan of pages using the following command on the primary SQLNODE1\StackOverflow2013 instance:

USE [StackOverflow2013]; 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 the column percent_complete is back to 0 on all databases.

On the primary instance SQLNODE1\StackOverflow2013

This is the load on the system after suspending the encryption.

On the secondary instance SQLNODE2\StackOverflow2013, it doesn't show 'suspended'.

1.3- On the primary database, resume Encryption

To resume page encryption on the primary instance SQLNODE1\StackOverflow2013, execute the following command: RESUME.

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

The query results show that TempDB was encrypted automatically, and TempestTest database encryption is in progress (ENCRYPTION_IN_PROGRESS).

This is the load after resuming the encryption on the primary instance SQLNODE1\StackOverflow2013

On the secondary instance, SQLNODE2\StackOverflow2013, ENCRYPTION_IN_PROGRESS is also displayed.