SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases

  • [アーティクル]

Sanjay Mishra, with contributions from Aasav Prakash

Reviewed by: Denzil Ribeiro, Murshed Zaman, Arvind Shyamsundar, Mike Ruthruff

Celebrating the release of SQL Server 2016, we are starting a new blog series on exciting new scenarios and features. Aptly named “Sweet 16” series, we will discuss sixteen new capabilities in SQL Server 2016 (one on each post) that help improve your business, and enhance your life as database professionals. Among the ocean of new capabilities, we have chosen 16, so will we will miss out on some very good ones. If we miss out your favorite ones, and you would like to hear from us, let us know and we will try our best to cover.

Starting with a hidden-gem: Backup compression for TDE-enabled databases.

Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features in SQL Server. Both were released as part of SQL Server 2008. However, had very little co-existence till now. While backup compression worked great for databases that were not enabled for TDE, its effectiveness for TDE-enabled databases was very limited. We described this behavior in our SQL Server 2008 blog post (please refer to pages 22-23 in our old blog collection document), and recommended not to use backup compression for TDE-enabled databases.

These two wonderful features could not stay strangers to each other for long. Starting with SQL Server 2016, you can now get the benefits of backup compression for TDE-enabled databases. Sweet!

This is one of the least publicized features in SQL Server 2016, and has managed to remain under the radar.

Here are some numbers to get you interested. I performed some tests with a database of about 115 GB size (real world data, not synthetic test data) first on SQL Server 2014, and then on SQL Server 2016 on the same server. The results are very impressive on SQL Server 2016.

[caption id="attachment_2765" align="alignnone" width="1417"]Figure 1: Backup Compression with TDE (SQL Server 2014) Figure 1: Backup Compression with TDE (SQL Server 2014)[/caption]

As illustrated in Figure 1, in SQL Server 2014 (and prior versions) backup compression doesn’t help reduce the backup size of a TDE-enabled database. However, due to the time spent in attempting the compression, the backup takes much longer to complete.

Things change completely in SQL Server 2016. You not only get great compression of the backup file size, but the backup time is reduced significantly as well, illustrated in Figure 2.

[caption id="attachment_2755" align="alignnone" width="1299"]Figure 2: Backup Compression with TDE (SQL Server 2016) Figure 2: Backup Compression with TDE (SQL Server 2016)[/caption]

The performance numbers presented here are examples. Your mileage will vary based on your data, workload and hardware.

Important to Know

It is important to know that while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good. It is recommended that you determine your optimum MAXTRANSFERSIZE through testing, based on your workload and storage subsystem. The default value of MAXTRANSFERSIZE for most devices is 1 MB, however, if you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped.

Update Sep 20th, 2017
The fixes for issues described below have been released in SQL Server 2016 RTM CU7 and SQL 2016 SP1 CU4. If you plan to leverage native backup compression for TDE databases or are already using it, we strongly recommend applying the latest CUs on SQL 2016 to ensure you are not hitting any of the known issues we have discovered earlier. It is important to know VDI support for backup compression on TDE enabled databases is not added yet and we plan to add it soon in upcoming servicing releases of SQL Server.?

Update April 6th, 2017

We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:

  • Currently it is not advisable to use striped backups with TDE and backup compression
  • If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
  • Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.

SQL engineering is working on fixes for these issues in SQL Server 2016. We will update this blog post once again once we have further information to share.

My Wish List

My wish list is that backup compression would work for TDE-enabled databases, irrespective of MAXTRANSFERSIZE setting. Specifying MAXTRANSFERSIZE in the BACKUP command may be a simple thing for many, but may be a bit cumbersome for some. Many DBAs rely on scripts generated from SSMS, which doesn’t specify MAXTRANSFERSIZE, and some DBAs who craft their own BACKUP scripts rely on the default value of MAXTRANSFERSIZE. Moreover, the log shipping UI doesn’t provide an option to specify MAXTRANSFERSIZE. It would be nice, if backup compression worked for all, without having to rely on explicitly specifying MAXTRANSFERSIZE.

What is on your wish list?

Call to Action

Many of you have asked for this capability for years. And, now that it is in the product, I urge you to go and test with your databases (especially the larger ones), and share some of your findings – compression ratio, backup time, backup size, etc.

Comments

  • Anonymous
    June 21, 2016
    Wow, that's awesome! Can you elaborate on how it's being done?Since it's hooked to MAXTRANSFERSIZE, I'm guessing there's some kind of dictionary or deduplication happening of data inside a MAXTRANSFERSIZE, which might indicate that higher MAXTRANSFERSIZEs could get better compression?
    • Anonymous
      June 23, 2016
      Higher MAXTRANSFERSIZE do not yield better compression, in numerous tests I have seen. Though higher MAXTRANSFERSIZE may impact IO performance, depending upon the concurrent workload during the backup and the IO characteristics of the storage subsystem.It is similar to backup compression for non-TDE databases, except that the additional condition of (MAXTRANSFERSIZE > 65536) is applied.
    • Anonymous
      June 27, 2016
      I think they're just reading pages (or even whole extents since it must be >65536) from database files into memory what decrypts them then compress them in memory using some variant of simple ZIP and then encrypt them again for backup. That would do the trick wouldn't it? :-)
  • Anonymous
    June 23, 2016
    Thanks for sharing the info Sanjay. Had a question about "...the log shipping UI doesn’t provide an option to specify MAXTRANSFERSIZE". Can we supply the MAXTRANSFERSIZE parameter via scripts? I do not see any additional parameter for SQLLOGSHIP (https://msdn.microsoft.com/en-us/library/bb283327.aspx).
    • Anonymous
      June 24, 2016
      Kapil, I didn't find an option to specify MAXTRANSFERSIZE with log shipping, especially in the sp_add_log_shipping_primary_database stored procedure.
  • Anonymous
    June 27, 2016
    This is really good and the savings are much visible. Thanks !BOL needs to be changed - https://msdn.microsoft.com/en-us/library/bb934049.aspx especially - "Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended."
    • Anonymous
      January 05, 2017
      Kin, were the changes made appropriately? How does it look now?Thanks!
  • Anonymous
    June 27, 2016
    Excellent Work! Regarding Your wish list, would be also importing to consider third party backup solutions, that don’t allow to explicity specify the MAXTRANSFERSIZE or the cases where that option isn’t specified by default (without custom or advanced configuration). Best Regards, Luís.
  • Anonymous
    September 21, 2016
    It would be nice if we could get TDE to work with Dedup backup systems...
    • Anonymous
      January 05, 2017
      Thanks, Emerson! Are these ideas logged into Connect as feature requests?
  • Anonymous
    December 16, 2016
    Sadly it doesn't work with Azure backup to URL of managed backup as specifying the MAXTRANSFERSIZE isn't supported: https://msdn.microsoft.com/en-us/library/dn435916.aspx Snippet: "Specifying MAXTRANSFERSIZE is not supported."If this could be added it'd be awesome.... :)