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.
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].
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.
It is also essential to verify the SQL Server version. In this article, I use SQL Server 2019 RTM (15.0.2000.5) 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.
|
1 2 3 4 5 |
BACKUP DATABASE [DBARepository] TO DISK = N'c:\sql\DBARepository_withoutCompression.bak' WITH NOFORMAT,NOINIT, NAME = N'DBARepository-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
In my AG instance, the [DBARepository] database size is 80MB, with only 0.28 MB available free space.
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.
|
1 2 3 4 5 |
BACKUP DATABASE [DBARepository] TO DISK = N'c:\sql\DBARepository_withCompression.bak' WITH NOFORMAT, NOINIT, NAME = N'DBARepository-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO |
Verify the backup sizes of both compressed and uncompressed backups.
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.
|
1 2 3 4 5 6 |
BACKUP DATABASE [DBARepository] TO DISK = N'c:\sql\DBARepository_withCompression_MaxTransferSize.bak' WITH NOFORMAT,NOINIT, NAME = N'DBARepository-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, MAXTRANSFERSIZE = 131072 GO |
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.
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.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023