As we’ve seen in previous posts, the process of turning TDE on for an empty database with no data is instantaneous. Most of the time though you’ll be applying TDE to an existing system where you may have a large amount of data that needs to be encrypted.

The process of setting up TDE is the same whether you’ve just set up a new database, or whether you’re working with a live database. Once you turn encryption on, SQL Server will automatically begin the process of encrypting any data in your database. Be that one row or terabytes of data.

There is nothing extra you need to do to make that happen. However, there are some questions you should be asking. How long will it take to encrypt your database? What sort of overhead is that going to put on your server – particularly for a live system? What should you do if you run into any issues?

Win this post we’re going to look at the first of those questions, how you can get an estimate of how long it might take for the initial encryption of your database. If you want to follow the examples in this post then you should first set up TDE as shown in https://matthewmcgiffen.com/2023/01/06/setting-up-tde/

Benchmarking TDE Performance on Your Server

I’m going to load up my previously created TestTDE database with about 10GB of data so we can see how the encryption process performs. We’ll do this with TDE turned off and then turn it on once the data is loaded. I’m running this on a  reasonably quick laptop and it takes a couple of minutes to load the data. If you’ve been following through these examples on your own SQL Server, then first you’ll need to turn TDE off with the following SQL:

ALTER DATABASE TestTDE SET ENCRYPTION OFF;

Then we can load the dummy data with the following code:

USE TestTDE;
CREATE TABLE dbo.SomeData(Id INT IDENTITY(1,1), SomeText VARCHAR(255));
GO
INSERT INTO dbo.SomeData (SomeText)
SELECT TOP 1000000
('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c
CROSS JOIN sys.objects d;
GO 100

After I turn encryption on again, I’m going to run a query to monitor the progress of encryption – polling every 5 seconds. If you’re working through this example on your own machine, you’ll want to have the query ready to go before you turn encryption on.

The command to turn TDE on is as previously stated.

ALTER DATABASE TestTDE SET ENCRYPTION ON;

Then you can immediately execute this query to report the progress.

DECLARE @state tinyint;
DECLARE @encyrption_progress
TABLE(sample_time DATETIME, percent_complete DECIMAL(5,2))
SELECT @state = k.encryption_state
FROM sys.dm_database_encryption_keys k
INNER JOIN sys.databases d
ON k.database_id = d.database_id
WHERE d.name = 'TestTDE';
WHILE @state != 3
BEGIN
INSERT INTO @encyrption_progress(sample_time, percent_complete)
SELECT GETDATE(), percent_complete
FROM sys.dm_database_encryption_keys k
INNER JOIN sys.databases d
ON k.database_id = d.database_id
WHERE d.name = 'TestTDE';
WAITFOR delay '00:00:05';
SELECT @state = k.encryption_state
FROM sys.dm_database_encryption_keys k
INNER JOIN sys.databases d
ON k.database_id = d.database_id
WHERE d.name = 'TestTDE';
END
SELECT * FROM @encyrption_progress;

The following image shows the results from running this test on my laptop.

You can see it took about a minute to encrypt 10GB of data. Figures will be different on your hardware, but that gives us an idea of the order of magnitude involved. If that scaled up linearly then it might take between 1 and 2 hours to encrypt a terabyte of data. I’m working here with data on an SSD, if you’re using older style disks then it would take considerably longer. I’d recommend using this technique to benchmark how your hardware will perform before you turn encryption on for your production databases.

In the next post we’ll look at how you can monitor for problem during the encryption process and what to do if you encounter an issue.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.