Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Backup Compression for SQL Server TDE Enabled Databases


By:   |   Last Updated: 2016-11-16   |   Comments (2)   |   Related Tips: More > Backup

Problem

SQL Server has many great features, but sometimes they don't always work nicely together.  This is the case with Transparent Data Encryption and Backup Compression.  However in SQL Server 2016, Microsoft announced that they can work together and in this tip we take a closer look.

Solution

Transparent Data Encryption (TDE) encrypts the data at rest, which means that TDE performs a real-time I/O encryption and decryption of the SQL Server database data, log and backup files, using a symmetric key that is secured by a certificate stored in the master system database. This key is called the Database Encryption Key or simply DEK.

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

Before SQL Server 2016 you were not able to use backup compression for TDE enabled databases, but starting with SQL Server 2016 you can get the benefits of backup compression for a TDE enabled database.

Assume that we have the AdventureWorksDW2012_TDE database with TDE enabled on that database, which is hosted in an Enterprise edition of SQL Server 2016. We 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

The result in our case is as follows:

query the sys.certificates system view to check the certificate used to encrypt the database

Also we will query the sys.dm_database_encryption_keys 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

The databases in our situation are:

query the sys.dm_database_encryption_keys to list all databases with TDE enabled

Now to make sure TDE is enabled in our database, we will take a full backup for the AdventureWorksDW2012_TDE database twice. The first time without enabling compression and the second time 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

Browse the backup location to compare the size of the two files, which shows us that the two generated files have similar sizes.

Browse the backup location to compare the size of the two files

In order to make backup compression work with TDE-enabled databases, the MAXTRANSFERSIZE backup parameter should be set to a value larger than the default 65536 value. MAXTRANSFERSIZE specifies the largest unit of data transfer in bytes that is used to transfer data 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.

So, we will repeat the compressed backup again and set the MAXTRANSFERSIZE to 131072 (128 KB):

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, MAXTRANSFERSIZE = 131072
GO

The compressed backup file size equals 20% of the normal uncompressed backup file, which is fantastic.

compressed backup again and set the MAXTRANSFERSIZE to 131072 (128 KB)

Also the elapsed time between the compressed and uncompressed backup operations is clear in the following image:

elapsed time between the compressed and uncompressed backup operations

Although you will observe extra CPU cycles consumed to compress the backup, Microsoft allows you to take a compressed backup for a TDE-enabled database. With this you get the benefits of a smaller file and faster backup and restore times.

Next Steps


Last Updated: 2016-11-16


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 12, 2018 - 8:25:40 AM - Ahmad Yaseen Back To Top

 Hi,

 

Please make sure that the TDE is enabled on that database and what is the version and edition of the SQL Server you are using returned from SELECT @@VERSION?

 

Best Regards,

Ahmad

 


Thursday, April 12, 2018 - 6:30:07 AM - Sweta Back To Top

 Hi post using the abvove two scripts , the backup size has not reduced then normal backup.

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, MAXTRANSFERSIZE = 131072
GO

Please suggest if I m missing any step.

 


Learn more about SQL Server tools