压缩备份和TDE

SQLServer的压缩备份是一个特性，根据实际使用中的观察，压缩比至少在1:5左右，也就是备份时增加了压缩选项后可以至少压缩到数据文件大小的20%甚至更低，可以很大程度上加快备份执行时间，减轻IO压力和节省磁盘存储空间
SQLServer的TDE（Transparent Data Encryption）作为数据库的数据文件加密特性，也会在对数据文件和备份文件加密等安全性要求的场景中用到

当压缩备份遇到TDE

当压缩备份遇到TDE加密过的数据库，尤其是对于运行过一段时间之后，中途需要对未加密的数据启用TDE，那么此时的压缩备份在执行时就会发生意想不到的后果。
主要体现在:
1，压缩备份时，压缩没有任何效果,与未压缩备份大小几乎一致
2，压缩备份时，备份的执行时间，与未压缩是相比，呈现指数级上升，如果说1勉强可以接受，那么这一点完全无法接受，甚至可能会严重影响到业务的正常运行。
对以上结果，笔者多年前第一次遇到，在弄清楚真像之前，一开始也是难以置信，难以理解的！！！

压缩备份和TDE，两者都是作为SQL Server 2008的一部分发布的。然而，截止到SQLServer 2016 cu5之前，几乎没有共存。虽然备份压缩对于未启用TDE的数据库非常有效，但对于启用TDE的数据库，其有效性非常有限。
以SQLServer 2016 CU5之前，以SQLServer 2016 CU5，和SQLServer 2019为分水岭，压缩备份开启了TDE的库，都会有所不同。尽管如此，压缩备份在这是SQL Server 2016中最不公开的特性之一。

以下数据参考微软的官方博客中的测试结果。
首先在SQL Server 2014上使用大约115 GB大小的数据库(真实世界数据，而不是合成测试数据)进行了一些测试，然后在同一台服务器上的SQL Server 2016上进行了测试。
在SQL Server 2014上的结果。

从上图中可以看出：
1，备份是否压缩，最后生成的备份文件大小几乎一致，也就是压缩没有任何效果
2，未压缩备份时间大概在25分钟左右，备份压缩备份时间在130分钟左右
其实不难理解，压缩的原理是基于同样的数据块（明文）才能压缩的更小，在启用了TDE的数据库中，数据页面（page）是被加密了的，可以认为所有数据页都是没有任何规律的，因此压缩备份面对所有的无须页，既耗时有无效。

在SQL Server 2016上的结果。

从上图中可以看出：
1，压缩备份只有未压缩备份的1/5左右
2，未压缩备份时间大概在80分钟左右，备份压缩备份时间在30分钟左右

但前面为什么说“压缩备份在这是SQL Server 2016中最不公开的特性之一”
重要的是要知道，在备份启用tde的数据库时，只有在BACKUP命令中指定MAXTRANSFERSIZE时，压缩才会启动。MAXTRANSFERSIZE必须大于65536 (64kb)。
MAXTRANSFERSIZE参数的最小值是65536，如果在BACKUP命令中指定MAXTRANSFERSIZE = 65536，则不会进行压缩。必须大于65536。事实上，65537正好可以。
建议您根据工作负载和存储子系统通过测试确定最佳MAXTRANSFERSIZE。大多数设备的MAXTRANSFERSIZE的默认值是1mb，但是，如果您依赖默认值，并且在BACKUP命令中跳过显式指定MAXTRANSFERSIZE，则将跳过压缩。
SQLServer的多个补丁包（从SQL 2016 RTM CU7、SQL 2016 SP1 CU4及以上版本开始）一直在改进压缩备份TDE这一特性，因此SQLServer并没有公布这一特性。

压缩备份在SQLServer 2019中的改进

上面说了，在SQLServer 2016 SP1之前的版本中，笔者对于压缩备份TDE库的结果是难以置信的，时间来到SQL Server 2019 (15.x) CU5之后，在某次偶然或者说必然的情况下，笔者再次被颠覆（打脸），原来的经验又不好使了。
SQL Server 2019 (15.x) CU5之后的版本上，偶然间中发现压缩备份TDE的库，压缩比很高，压缩效果很明显啊！

从SQL Server 2019 CU5开始，对于启用TDE的数据库，不再需要将MAXTRANSFERSIZE设置为大于64KB来启用优化的压缩算法。也就是说，如果将备份命令指定为WITH COMPRESSION或备份压缩默认服务器配置设置为1，则MAXTRANSFERSIZE将自动增加到128 KB，以便对启用TDE的数据库备份进行最佳压缩。

它的原理大概是：在压缩备份的过程中，将已经加密的数据页，解密后再压缩备份，备份完后再基于TDE加密，这样绕过了直接对加密过的数据页面直接备份而造成的无效压缩和过高的耗时，同时又达到了高压缩比的目的。

总结

时代在变，你的经验不会永远有效，坚持学习，保持进步。

参考
https://learn.microsoft.com/zh-cn/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/archive/blogs/sqlcat/sqlsweet16-episode-1-backup-compression-for-tde-enabled-databases