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.

 

 

 

1 thought on “SQL Server 2019 Suspend/Resume TDE

  1. Pingback: Suspending and Resuming TDE – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s