Impact on TDE encryption in progress on SQL Server restart

23 February,2022 by Tom Collins

Question: I am planning on TDE encrypting a bunch of  large SQL Server databases. Due to potential IO contention issues during the work day , the plan is to encrypt these databases during the out of hrs period. If the encryption is triggered , but there is a SQL Server service restart during the encryption process , will  the encryption process be impacted when the SQL Server service restarts?

Answer:   The TDE encryption is triggered by executing the following statement 

ALTER DATABASE myDB  
SET ENCRYPTION ON

You can track the progress of the encryption by executing this sql statement , use the percent_complete column to review the percentage. If the SQL Server restarts , the encryption process will just continue , and continue form where it left off . 

 

SELECT DB_NAME (e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint

 

DatabaseName database_id encryption_state encryption_state_desc name percent_complete
tempdb 2 3 Encrypted NULL 0
test 5 3 Encrypted NULL 0
MYDB 9 2 Encryption in progress NULL 15.25823

 

From SQL Server 2019 there is also an option for a controlled pause of the encryption

ALTER DATABASE <Your DB> SET ENCRYPTION SUSPEND;

ALTER DATABASE <Your DB> SET ENCRYPTION RESUME

 

Read more on SQL Server TDE set up  & TDE performance troubleshooting 

 How To Use Transparent Data Encryption To Increase SQL Server Security

Tested TDE with Instant File Initialization on SQL Server databases

How to measure Transparent Data Encryption (TDE) Performance

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Impact on TDE encryption in progress on SQL Server restart


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer