This time I would like to write about Transparent Data Encryption (TDE) and more specifically a new feature that is available in Sql Server 2019 and as a Azure SQL Database.
The already announced and available feature is called Resumable Transparent Data Encryption (TDE) Scan. Just like Resumable Online Index Create & Rebuild processes that Microsoft has created in Sql Server 2019 & Sql Server 2017 respectfully. I am even wondering if the same team has worked on this feature.
In the modern times, where the expected availability is expected to be around 99.99999999999(9)% if not the whole 100%, the possibility of using resumable operations and thus controlling the impact on the system are critical.
I love that in the past couple years the features starts to be taking advantage of RELATIVELY small amount of IO & CPU spendings can give us a more precise control over the exact moment and the amount of impact on our productive systems.
The funniest story I can tell you about TDE is the time I got a call from a Client running a Sql Server 2012 Availability Group on a pretty much critical Database and begging for help since he started implementing TDE encryption by using GUI in the Sql Server Management Studio (and the hint goes that until 2014 the GUI did not supported the complete process for configuring TDE in Availability Groups). So on my question of did he tested it in any other development, the answer was – “Oh Yeah. Yes ! I tested it on a single instance and it looked like it has worked out well!”.
Besides configuring the feature well in Availability Groups, the real issue was the impact of those “many-many-GBs databases” (TM) scanning and encryption during the working hours, negatively impacting the already busy Dynamics System
In any case, here we are around a week before expected Sql Server 2019 public RTM announcement and the Resumable Transparent Data Encryption (TDE) Scan feature is already available in the SQL Server 2019 RC1.
What I really love about it is that in contrary to the Resumable Online Index Creation/Rebuild process, there is no need to specify anything additional in the command when encrypting/decrypting a database.
The syntax for suspending the TDE is following:
ALTER DATABASE [Database_Name] SET ENCRYPTION SUSPEND;
The resuming part is rather trivial as well:
ALTER DATABASE [Database_Name] SET ENCRYPTION RESUME;
For viewing progress we have always used the sys.dm_database_encryption_keys DMV, which is still there but it has also been enhanced with the new columns:
encryption_state_desc – displays current encryption status with possible values (NONE, UNENCRYPTED, ENCRYPTED, DECRYPTION_IN_PROGRESS, ENCRYPTION_IN_PROGRESS, KEY_CHANGE_IN_PROGRESS, PROTECTION_CHANGE_IN_PROGRESS
encryption_scan_state – displays the current encryption status in the integer value
encryption_scan_state_desc – displays the textual description of the current encryption status and includes the following values ( NONE, RUNNING, SUSPENDED, COMPLETE, ABORTED )
encryption_scan_modify_date – displays the last timestamp (UTC) of the encryption status modification.
Without long overdue, let’s take the currently available copy of Sql Server 2019 RC1, restore a backup of any database (ContosoRetailDW in my case) and start playing with this awesome feature!
First we need to ensure the creation of the Master Key and the certificate which shall be used to execute encryption/decryption processes:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'O3_2f_TY5Dme^fa;gw'; GO CREATE CERTIFICATE SQL19TestCert WITH SUBJECT = 'SQL Server 19 Test Certficate'; GO
Switching to our database we need to create Database Encryption Key with AES 256 algorithm:
USE ContosoRetailDW; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE SQL19TestCert; GO
The warning message above points to the lack of the backups for the respective Master Key & Certificate.
Let’s correct that by taking a backup for both of them:
-- Before going anywhere forward ensure that we have backup copies of the Certificate and Master Key
use master;
BACKUP CERTIFICATE SQL19TestCert TO FILE = 'C:\Install\SQL19TestCert'
WITH PRIVATE KEY ( FILE = 'c:\Install\SQL19TestCertKey' ,
ENCRYPTION BY PASSWORD = 'qt0DpTgf^YPU24jNTyt1J])rruGXq0C7' );
GO
Now we are ready to start encrypting our database:
ALTER DATABASE ContosoRetailDW SET ENCRYPTION ON;
and monitor the progress with the following query, showing the new columns mostly:
SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
which shows us 27% of the progress and the fact that the encryption is in progress and is running currently.
Now it is the moment to test the TDE encryption suspension with the command:
ALTER DATABASE ContosoRetailDW SET ENCRYPTION SUSPEND;
On the image on the left, you can see the suspension of the reading and the eventual suspension of the writing activities after the currently prefetched information has been read. The image itself is extremely clear and do not need any further explanation, besides that depending on the amount of your memory and the speed of your disk read activities, it might take some time to stop.
Consulting the state of the TDE gives us a rather surprising information with total lack of progress (0%), which I understand but find very much disturbing and consider it to be a bad bug:
SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
We have the information that the encryption is in progress and that is suspended, but we rather have no idea of how far we have progressed with it. Resuming and checking is not a good option on the production environment and I expect Microsoft to fix this in the following Cumulative Updates.
With that said, let’s resume the encryption
ALTER DATABASE ContosoRetailDW SET ENCRYPTION RESUME;
and follow it until the completion through the sys.dm_database_encryption_keys DMV:
SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
Now, let’s reverse the path with a total decryption of our Database by using SET ENCRYPTION OFF command against our test database:
ALTER DATABASE ContosoRetailDW SET ENCRYPTION OFF;
SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
And like in an encryption case, the percentage of the decryption gets totally messed up and shows 0% after suspension:
ALTER DATABASE ContosoRetailDW SET ENCRYPTION SUSPEND; SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
and eventually reaching the state of full decryption, after resuming the process:
ALTER DATABASE ContosoRetailDW SET ENCRYPTION RESUME; SELECT DB_NAME(database_id) as dbName, percent_complete, encryption_state, encryption_state_desc, encryption_scan_state, encryption_scan_state_desc, encryption_scan_modify_date FROM sys.dm_database_encryption_keys;
Final Thoughts
I love the feature and it makes a huge sense.
This is an another killer feature for me, especially since I am looking for more and more of my current clients to start using TDE and the control of the initial impact will be absolutely essential.
We just need that little bug with the percentage progress to get fixed …