SQL Server 2019 Suspend/Resume TDE

Yes, a really nice new shiny feature where we have the ability to suspend and resume the encryption scan for TDE – Transparent Data Encryption which is available in SQL Server 2019. (Tested against the latest version CTP 2.4)

Assume you are ready to enable encryption, if not please check out my older posts (https://blobeater.blog/2016/11/22/playing-with-tde/).


ALTER DATABASE [CodeDB]  SET ENCRYPTION ON;
GO

Whilst this is running in a different query window I issue the below:

 ALTER DATABASE [CodeDB] SET ENCRYPTION SUSPEND;

Checking the error log shows the state of encryption.

suspend

To resume this, quite simply issue:

 ALTER DATABASE [CodeDB] SET ENCRYPTION RESUME;

resume

Obvious to see that the wording could be better. I rather see paused / suspend then aborted and I would rather see resuming scan than beginning the scan. However, the point here is that this new feature is well cool. May times with very big databases I have wanted to pause the encryption scan because of I/O contention, now I can. You can also track the timestamp of the last scan update via a new field encryption_scan_modify_date from sys.dm_database_encryption_keys.

newscan

Also the above applies to when switching encryption off.

Advertisements

1 thought on “SQL Server 2019 Suspend/Resume TDE

  1. Pingback: Suspending and Resuming TDE – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.