Rajendra Gupta
Enhancement in TDE compressed backup in SQL Server 2019 CU5

Backup compression in TDE enabled databases in SQL Server Always On Availability Groups

September 16, 2020 by

In the previous article, Transparent Data Encryption for SQL Server Always On Availability Groups, we discussed enabling TDE for the existing database in AG. In this article, we will explore options for a compressed backup of a TDE database.

Introduction

TDE protects the physical database, log, backup files using the IO encryptions. It uses the Advanced Encryption Standard (AES) for encryption purposes. To enable the TDE on the existing AG database, we created a database encryption key, certificate, master key for data protection. It does not encrypt the data; instead, it focuses on data file security.

We can take compressed backup for a SQL database starting from SQL Server 2008. It helps us to save disk space and time. Does backup compression work for a TDE enabled database in SQL Server Always On Availability Group? What are the options and improvements in different SQL versions? Let’s explore these questions in this article.

  • Note: The solution in this article works well with the standalone and availability group TDE enabled database.

Environment details

In this article, we will use the following database environment for demonstration purposes.

  • SQL Replicas: SQLAG1\INST1, SQLAG2\INST2
  • TDE enabled database: [DBARepository]
  • Synchronization mode: Synchronized with automatic failover

Compressed backup for a TDE enabled database

Usually, for a SQL database, we take compressed backups, and it works well for you in saving the disk space. Before we proceed further, let’s verify that TDE is enabled for my availability group database. Here, we verify that the TDE certificate protects the [DBARepository] database.

Compressed backup in SQL Server Always On Availability Group

We can view the certificate information using the sys.certificates. It shows all certificates in the master database along with the start and expiry date.

It shows that the database master key protects the [TDECert].

database master key

We can verify the default backup compression configuration using the sp_configure. It shows that we have not enabled the backup compression at the instance level. If you wish to enable compressed backup configuration at the instance level, you can modify the value for the parameter backup compression default to 1.

sp_configure

It is also essential to verify the SQL Server version. In this article, I use SQL Server 2019 RTM (15.0.2000.5) version.

SQL Server version

Take an uncompressed full database backup

Now, connect to the primary replica and take a full backup using the below query. It takes uncompressed backup in the specified directory.

In my AG instance, the [DBARepository] database size is 80MB, with only 0.28 MB available free space.

Take an uncompressed full database backup

Take a compressed full database backup

This time we take a full compressed backup for the TDE enabled database. To take a compressed backup, we add WITH COMPRESSION clause in the backup database statement.

Verify the backup sizes of both compressed and uncompressed backups.

compressed full database backup in SQL Server Always On Availability Group

We can see that the backup size is almost equal, and the compression did not help us to save the disk space. Until SQL Server 2016, Microsoft does not support compressed backup for a TDE standalone or SQL Server Always On availability group database. It does not report any error if you try to take a compressed backup, but it puts compression overhead but does not helps you.

TDE backups enhancements in SQL 2016 RTM CU7

Microsoft added backup compressions from the SQL Server 2016 RTM CU7. You might wonder that we are using SQL Server 2019 RTM in this article, but still, we do not see compression in effect for the TDE database.

Actually, for a compressed backup on the TDE database, we need to use argument MAXTRANSFERSIZE along with its value. A default backup uses MAXTRANSFERSIZE = 64K for a single database file. For a TDE database, we need to use the MAXTRANSFERSIZE > 64K.

How does this parameter help a compressed backup for TDE enabled database? In SQL Server 2016 RTM CU7, it uses a new backup algorithm and does the following tasks.

  • It reads a block of the database from the TDE database file
  • It decrypts 64 KB extent data and compresses it
  • It encrypts the extent again and writes a new extent in the buffer
  • It repeats the process until the complete block is written to the disk
  • Once the buffer gets full, it flushes the buffer in the backup file

In the below SQL query, we specify the MAXTRANSFERSIZE parameter as 128KB (131072/1024=128KB) to specify a unit of data transfer between SQL Server and backup media.

It takes a compressed backup for TDE database in SQL Server Always On Availability Group. The size of the backup file is 26.3 MB. The original full backup size (uncompressed) was 75.2 MB, so it saved around 65% of disk space.

backups enhancements in SQL 2016 RTM CU7 SQL Server Always On Availability Group

Enhancement in TDE compressed backup in SQL Server 2019 CU5

As we saw earlier, we need to specify the MAXTRANSFERSIZE parameter to take a compressed backup of the TDE enabled database. This parameter might not be familiar with everyone. Usually, we create a single backup job to take a full compressed backup frequently as per our organization’s backup policy. As a regular practice, as compression works for all backups, we might not verify it explicitly. If your database size is huge, it can take a lot of disk space without your information.

To resolve this issue, SQL Server 2019 CU5 contains a fix for it. In this CU5 (released in June 2020) you do not need to specify MAXTRANSFERSIZE value higher than 64KB to take a compressed backup of transparent data encryption (TDE) database.

If we specify WITH COMPRESSION argument for a compressed backup of TDE database, SQL Server automatically increases the MAXTRANSFERSIZE value to 128K (higher than 64k). However, if you still specify an exact value of MAXTRANSFERSIZE, it overwrites the default value (128k), and explicit value comes in affect. To take an uncompressed backup for the TDE database, we can specify the NO_COMPRESSION clause or modify the default backup configuration.

AS you know, SQL Server stopped providing service packs from SQL Server 2017 onwards. It only provides cumulative updates (CU) to fix the issues. TDE backup fix comes in SQL 2019 CU5 however you can install the latest SQL 2019 CU. To get the latest cumulative updates, browse the URL SQL Server 2019 build versions and install it for your environment.

Enhancement in TDE compressed backup in SQL Server 2019 CU5

You can download the CU installer and run it. For a standalone SQL instance, you can directly apply CU after taking all required backups. SQL instance would be down while the patching is in progress.

For a SQL Server Always On Availability Group instances, high-level steps would be as follows.

  • Take database backups
  • Apply CU on the secondary replica and reboot it
  • Perform an availability group failover. It makes the current secondary replica as the new primary replica
  • Apply CU on the current secondary replica ( after failover) and reboot it
  • Perform an AG failback to original nodes

We will cover the detailed step on SQL Server patching for SQL Server Always On Availability Group in the upcoming articles.

Conclusion

In this article, we explored the way to take compressed backup for the TDE enabled database in SQL Server Always On Availability Groups. If you are on SQL Server 2016 or 2017 version, you still need to use the MAXTRANSFERSIZE parameter. You can use your regular backup commands starting from SQL 2019 CU5 as stated above. You should always take compressed backup as it might save a lot of disk space for massive databases. It is applicable for standalone as well as TDE database in SQL Server Always On Availability Group.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups

Rajendra Gupta
119 Views