Backup Compression Performance Enhancements for SQL Server 2016 TDE Enabled Databases

By: Simon Liew   |   Updated: 2016-10-31   |   Comments   |   Related: > Backup

Problem

Typically backup compression on a TDE enabled SQL Server database is not recommended as backup compression is not able to significantly compress the backup file. Starting with SQL Server 2016, backup compression now works well on TDE-enabled databases. This tip will describe the conditions when backup compression on TDE-enabled databases is beneficial.

Solution

On TDE-enabled databases prior to SQL Server 2016, the trick to get a smaller backup is to compress tables in the database with PAGE or ROW compression which then also reduces the backup size. But if you have a highly volatile table, then table compression might not be suitable. There is an official article Data Compression: Strategy, Capacity Planning and Best Practices to assist in determining if a table is suitable for compression.

Starting with SQL Server 2016, backup compression on TDE-enabled database can compress similarly to a database without TDE. One condition is backup compression only kicks in when the MAXTRANSFERSIZE parameter is specified with a value higher than 65536 (64KB) in the BACKUP command.

SQL Server 2016 maintenance plan now includes an option to allow Max transfer size to be specified in the Back Up Database Task.

Guidelines for Backup Compression for SQL Server TDE Enabled Databases

Prior to SQL Server 2016:

• It is not recommended to backup compressed TDE-enabled databases.
• With or without backup compression, the final database backup size will be close to the size of the TDE-enabled database used data space.
• A compressed backup of a TDE-enabled database might take longer due to SQL Server trying to apply compression.
• Typically, using PAGE or ROW compression on tables in a TDE-enabled database would reduce the database data file used space as well and the full database backup size.

Starting with SQL Server 2016:

• Backup compression on a TDE-enabled database is enabled when the MAXTRANSFERSIZE parameter is set to a value higher than 65536.
• The backup compression works similarly to a database without TDE.

This tip uses MAXTRANSFERSIZE = 65537 (64K), but this value does not indicate an optimal value for your storage subsystem. You will need to test specifically on your storage subsystem to find the optimum MAXTRANSFERSIZE.

MAXTRANSFERSIZE is always rounded to a proper 64K extent boundary. So, the MAXTRANSFERSIZE effective value is actually 131072 (128K) when 65537 was specified.

SQL Server Test Environment Setup

To start, download the AdventureWorks2012-Full Database Backup.zip from codeplex and restore the database on SQL Server 2016 Developer Edition.

Reasons to use AdventureWorks2012 database in this tip:

• Does not contain memory optimized tables as memory optimized tables do not support compression anyway.
• All user tables in AdventureWorks2012 are not compressed. This will allow us to focus on backup compression on a database without compressed tables.

All disk drives sector allocation unit hosting the SQL Server data file, transaction log and backup are formatted to 64K.

The restored AdventureWorks2012 database data file size is only 205MB. The script below will add records into the AdventureWorks2012 database and the new database data file size on disk will be approximately 9.1GB.

USE [AdventureWorks2012]
GO

CREATE TABLE [dbo].[SalesDetail](
 [SalesOrderID] [int] NOT NULL,
 [SalesOrderDetailID] [int] NOT NULL,
 [CarrierTrackingNumber] [nvarchar](25) NULL,
 [OrderQty] [smallint] NOT NULL,
 [ProductID] [int] NOT NULL,
 [SpecialOfferID] [int] NOT NULL,
 [UnitPrice] [money] NOT NULL,
 [UnitPriceDiscount] [money] NOT NULL,
 [LineTotal] [decimal](18, 4) NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
)
GO

SET NOCOUNT ON;
INSERT INTO [dbo].[SalesDetail]
SELECT *
FROM [Sales].[SalesOrderDetail]
GO 800

The database data file size on disk is captured so that we can compare the size with the backup file size on disk.

After adding records using the script, AdventureWorks2012 database data file only has 1 extent (64K) free which means the database data file size is very close to the data used space.

DBCC SHOWFILESTATS WITH TABLERESULTS

Fileid      FileGroup   TotalExtents         UsedExtents          Name                       FileName
----------- ----------- -------------------- -------------------- -------------------------- ----------------------------------------------------------------------
1           1           139728               139727               AdventureWorks2012_Data   D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The backup compression setting on the SQL Server instance is disabled with the command below prior to performing the backup. This will allow the BACKUP command to explicitly specify if backup compression should be used.

EXEC sys.sp_configure N'backup compression default', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Test Results

The BACKUP command and the script to enable TDE are provided at the bottom section of this tip. The backup file size and the backup duration is recorded in each step of the test as shown below.

SQL Server BACKUP command and script to enable TDE

Step 1

A full SQL Server database backup is taken on a database without TDE and no backup compression.

BACKUP DATABASE [AdventureWorks2012] TO DISK = 'F:\Backup\AdventureWorks2012_NonCompressed.BAK' 

Processed 1117816 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 1.
Processed 7 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 1.
BACKUP DATABASE successfully processed 1117823 pages in 86.911 seconds (100.482 MB/sec).

The database backup file on disk is 9.1GB

Step 2

A full SQL Server database backup is taken on a database without TDE with backup compression.

BACKUP DATABASE [AdventureWorks2012] TO DISK = 'F:\Backup\AdventureWorks2012_Compressed.BAK' WITH COMPRESSION

Processed 1117816 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 1.
BACKUP DATABASE successfully processed 1117818 pages in 60.956 seconds (143.266 MB/sec).

The database backup file on disk is 3.2GB

Script to enable TDE

The script below will turn on TDE for the AdventureWorks2012 database

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';  
go  
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
go  
USE AdventureWorks2012;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  
ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION ON;  
GO 

Step 3

A full SQL Server database backup is taken on a TDE-enabled database without backup compression.

BACKUP DATABASE [AdventureWorks2012] TO DISK = 'F:\Backup\AdventureWorks2012_TDE_NonCompressed.BAK'

Processed 1117816 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 1.
Processed 7 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 1.
BACKUP DATABASE successfully processed 1117823 pages in 74.547 seconds (117.147 MB/sec).

The database backup file on disk is 9.1GB

Step 4

A full SQL Server database backup is taken on TDE-enabled database with backup compression. MAXTRANSFERSIZE parameter is not specified so the default value 65536 is used.

BACKUP DATABASE [AdventureWorks2012] TO DISK = 'F:\Backup\AdventureWorks2012_TDE_Compressed.BAK' WITH COMPRESSION

Processed 1117816 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 1.
BACKUP DATABASE successfully processed 1117818 pages in 98.996 seconds (88.215 MB/sec).

The database backup file on disk is 9.1GB

Step 5

A full SQL Server database backup is taken on TDE-enabled database with backup compression. MAXTRANSFERSIZE parameter specified with a value of 65537.

BACKUP DATABASE [AdventureWorks2012] TO DISK = 'F:\Backup\AdventureWorks2012_TDE_CompressedTFS.BAK' WITH COMPRESSION,MAXTRANSFERSIZE = 65537

Processed 1117816 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Data' on file 1.
Processed 2 pages for database 'AdventureWorks2012', file 'AdventureWorks2012_Log' on file 1.
BACKUP DATABASE successfully processed 1117818 pages in 86.545 seconds (100.906 MB/sec).

The database backup file on disk is 3.2GB

Next Steps

• Keep the guidelines and performance metrics in mind as you select TDE and backup compression configurations on your SQL Servers.
• Check out these resources:
  • Transparent Data Encryption (TDE)
  • Data Compression: Strategy, Capacity Planning and Best Practices
  • SQL Server Backup Tips
  • SQL Server Backup Tutorial
  • Data Compression: Strategy, Capacity Planning and Best Practices

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips