Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Impact on TDE encryption in progress on SQL Server restart

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 SET ENCRYPTION SUSPEND;

ALTER DATABASE SET ENCRYPTION RESUME

Read more on SQL Server TDE

Tested TDE with Instant File Initialization on SQL Server databases

How to measure Transparent Data Encryption (TDE) Performance



This post first appeared on SQLSERVER-DBA.com, please read the originial post: here

Share the post

Impact on TDE encryption in progress on SQL Server restart

×

Subscribe to Sqlserver-dba.com

Get updates delivered right to your inbox!

Thank you for your subscription

×