SQL Server has many great features, but sometimes they don’t always work nicely together. One such case is with Transparent Data Encryption (TDE) and Backup Compression. However, in SQL Server 2016, Microsoft announced that they can work together, and in this article, we will take a closer look at how to use TDE and Backup Compression in SQL Server 2016.

Transparent Data Encryption (TDE)

TDE encrypts the data at rest, meaning it performs real-time I/O encryption and decryption of the SQL Server database data, log, and backup files. It uses a symmetric key secured by a certificate stored in the master system database, known as the Database Encryption Key (DEK).

Backup Compression

Database backup compression is important because it enables you to save disk space by generating a backup file smaller than the database size. It also saves time required to backup and restore the database.

Enabling Backup Compression for TDE-Enabled Databases

Before SQL Server 2016, backup compression was not available for TDE-enabled databases. However, starting with SQL Server 2016, you can now get the benefits of backup compression for a TDE-enabled database.

To check if TDE is enabled in a database, you can query the sys.certificates system view to check the certificate used to encrypt the database:

SELECT name, pvt_key_encryption_type_desc, issuer_name, expiry_date, start_date
FROM sys.certificates

You can also query the sys.dm_database_encryption_keys view to list all databases with TDE enabled:

SELECT db_name(database_id) as DATABASE_Name
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3

To enable backup compression for a TDE-enabled database, you need to set the MAXTRANSFERSIZE backup parameter to a value larger than the default 65536 value. MAXTRANSFERSIZE specifies the largest unit of data transfer in bytes between SQL Server and the backup media, with possible values equal to multiples of 65536 bytes (64 KB) and a maximum value equal to 4MB.

Here is an example of taking a full backup for a TDE-enabled database twice, once without compression and once with compression enabled:

BACKUP DATABASE [AdventureWorksDW2012_TDE]
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_NotCompressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW2012_TDE-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10
GO

BACKUP DATABASE [AdventureWorksDW2012_TDE]
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW2012_TDE-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Browsing the backup location will allow you to compare the size of the two files, which will show that the compressed backup file is significantly smaller.

It is important to note that although there may be extra CPU cycles consumed to compress the backup, Microsoft allows you to take a compressed backup for a TDE-enabled database. This provides the benefits of a smaller file and faster backup and restore times.

In conclusion, SQL Server 2016 introduced the ability to use backup compression for TDE-enabled databases. This allows you to save disk space and reduce backup and restore times. By setting the MAXTRANSFERSIZE backup parameter appropriately, you can optimize the backup process for TDE-enabled databases.