SQL Server 2019資料庫設定TDE加密後，導致備份檔bak過大

如果不想額外特別加MAXTRANSFERSIZE 參數
必須安裝SQL Server 2019 CU(Cumulative Update) 5 以上
因為微軟官網已經不提供CU5下載
可以安裝最新版的CU8 (KB4577194)

Cumulative Update 8 for SQL Server 2019
https://support.microsoft.com/en-us/help/4577194/cumulative-update-8-for-sql-server-2019

下載Cumulative Update Package 8 for SQL Server 2019 - KB4577194
https://www.microsoft.com/en-us/download/details.aspx?id=100809

SELECT @@VERSION結果
<安裝CU8之前>
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)   Oct 28 2019 19:56:59   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

<安裝CU8之後>
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)   Sep 23 2020 16:03:08   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)


https://support.microsoft.com/en-us/help/4552255/kb4552255-cumulative-update-5-for-sql-server-2019
Bug Reference:13495908
KB article number:4561915
Improvement: MAXTRANSFERSIZE no longer required to enable backup compression on TDE encrypted databases

ref:
https://www.sqlshack.com/backup-compression-in-tde-enabled-databases-in-sql-server-always-on-availability-groups/

https://www.mssqltips.com/sqlservertip/4522/backup-compression-performance-enhancements-for-sql-server-2016-tde-enabled-databases/