Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.

Backup Performance

Following on from general questions of TDE performance, it’s sensible that you might also be concerned whether TDE has an impact on backup times. You may also have read of people complaining about long backup times with TDE.

It’s not necessarily true that TDE has much of an impact on backup performance. The reason is that when a backup is performed, SQL Server does not have to encrypt the data. The data already sits encrypted on disk in the data and log files, and those copies of the data are what are used for performing the backup. In practice there may be some data in memory that has yet to be encrypted and written to disk, but in general, that is not going to be large enough to cause significant overhead.

When people talk about issues with backup performance and TDE, they are likely to be talking about the case involving backup compression.

Backup Compression

Many people use backup compression with database backups in SQL Server. It is simple functionality to use as shown in this code example:

BACKUP DATABASE [TestTDE] TO DISK = 'C:\Test\TestTDE_Compressed.bak' WITH COMPRESSION;

The benefit of backup compression isn’t just about having smaller backup files but also in the time taken to perform a backup. The biggest bottleneck involved in taking a backup is usually the time it takes to write it to disk. By taking compressed backups you can significantly reduce backup takes. This comes at the cost of some extra CPU overhead to perform the compression, but unless your CPU is under pressure, it’s often worthwhile.

Up until the 2016 version, SQL Server did not support backup compression on TDE enabled databases. One reason for this may be that most compression algorithms work best where there is some repetition in the data to be compressed, but encrypted data looks pretty much random. What this meant in practice was that you might specify the WITH COMPRESSION option when backing up your TDE-protected databases but you wouldn’t see much difference in the file size or backup times. This changed from SQL 2016 and was a welcome improvement.

To use backup compression with TDE, however, you needed to specify an additional parameter MAXTRANSFERSIZE. This parameter specifies the largest unit of transfer in bytes used between SQL Server and the backup media. If you’re interested in fine-tuning your backup performance, this is one value you can play with. Backup compression with TDE doesn’t kick in unless your MAXTRANSFERSIZE is greater than 64kb (65536). As long as the value you specify is at least one greater than 64k, then an optimized algorithm for compression of TDE encrypted databases is enabled. Commonly people use the value of 128kb. The command looks like this:

BACKUP DATABASE TestTDE TO DISK = 'C:\Test\TestTDE_Compressed.bak'
WITH COMPRESSION, MAXTRANSFERSIZE = 131072;

This extra parameter becomes unnecessary if you are on SQL Server 2019 Cumulative Update 5 or higher. With that release, if you specify WITH COMPRESSION for a backup taken for a TDE-protected database and you don’t specify MAXTRANSFERSIZE, then MAXTRANSFERSIZE will automatically be increased to 128kb, and your backup will be compressed.

Backup Compression Issues

The introduction of backup compression for TDE-protected databases has however not been without problems, and this is something you really need to be aware of. There have been a number of bugs discovered where a compressed backup of a TDE database was found to be unrecoverable. Some people have also reported that restore times were massively increased in some cases.

If you’re on a version of SQL Server higher than 2016 CU 7 or 2016 SP1 CU4, then you should be fine, but I would stress the importance of regularly testing your backups by restoring them. A few days before writing this, I came across the term Schrodinger’s Backup – the condition of any backup is unknown until a restore is attempted. When it comes to TDE and backup compression, you should consider that as a very true statement.

TDE and High Availability

In general, TDE plays nicely with any of the built-in features that SQL Server has for high availability (HA). That includes:

• Availability Groups
• Log Shipping
• Database Mirroring

In theory, in all cases, the actions you need to take to support TDE are the same. You just need to ensure that the secondary server has a Database Master Key (DMK). Then you need to ensure that copies of your certificate and private key have been restored to the secondary before you turn encryption on. This is the same step you would take if you were attempting to restore a TDE-protected database to a different server. We covered that a previous post Migrating or Recovering a TDE Protected Database.

As long as that is done, then whichever HA tool you use should take care of the rest.

In practice, we DBAs are cautious folk, and you don’t want to risk anything going wrong when you are dealing with a live system. As such you may want to take the following steps:

1. Remove the database from HA.
2. Set up TDE for the database and turn on.
3. Set up the keys and certificate on the secondary.
4. Add the database back into HA.

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.