バックアップの圧縮 (SQL Server)
適用対象: 
SQL Server
この記事では、SQL Serverバックアップの圧縮について、制限事項、バックアップを圧縮することによるパフォーマンスのトレードオフ、バックアップ圧縮の構成、圧縮率などについて説明します。 バックアップ圧縮はSQL Serverエディションでサポートされています： Enterprise、Standard、および Developer。 SQL Server 2008 (10.0.x)以降のすべてのエディションでは、圧縮バックアップをリストアできます。
メリット
圧縮されたバックアップは、同じデータの圧縮されていないバックアップよりも小さいため、バックアップを圧縮すると一般には必要なデバイス I/O が少なくなり、通常はバックアップの速度が大きく短縮されます。
詳細については、この記事の後半にある「バックアップの圧縮によるパフォーマンスへの影響」を参照してください。
制限
圧縮されたバックアップには次の制限が適用されます:
圧縮されたバックアップと圧縮されていないバックアップを 1 つのメディア セット内に共存させることはできません。
以前のバージョンのSQL Serverでは、圧縮されたバックアップを読み込むことができません。
NTbackupsは、圧縮されたSQL Serverバックアップとテープを共有することはできません。
バックアップ圧縮によるパフォーマンスへの影響
既定の設定では、圧縮によって CPU 使用率が著しく増加し、圧縮処理によって CPU がさらに消費されるために、同時に実行される操作が悪影響を受ける場合があります。 このため、 リソース ガバナーによって CPU 使用率が制限されるセッションでは、優先度の低い圧縮バックアップを作成することができます。 詳細については、「リソース ガバナーを使用してバックアップの圧縮による CPU 使用率を制限する方法 (Transact-SQL)」を参照してください。
SQL Server 2022 (16.x) 以降、Integrated offloading & acceleration を使用してバックアップを圧縮し、バックアップの CPU リソースをオフロードすることができます。
バックアップ I/O パフォーマンスの実態を把握するためには、次のようなパフォーマンス カウンターを評価することで、デバイスに対するバックアップ I/O を特定できます:
物理ディスク カウンターなどの Windows I/O パフォーマンス カウンター
SQLServer:Backup Device オブジェクトの Device Throughput Bytes/sec カウンター
SQLServer:Databases オブジェクトの Backup/Restore Throughput/sec カウンター
Windows カウンターの詳細については、Windows ヘルプを参照してください。 SQL Server カウンターの使用方法の詳細については、「 SQL Server オブジェクトの使用」をご覧ください。
圧縮バックアップの圧縮率を計算します
バックアップの圧縮比率を計算するには、 backupset 履歴テーブルの backup_size 列と compressed_backup_size 列にある値を次のように使用します:
backup_size:compressed_backup_size
たとえば、圧縮比率が 3:1 の場合、ディスク領域を約 66% 節約できることを意味します。 これらの列に対するクエリを実行するには、次の Transact-SQL ステートメントを使用できます:
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
圧縮されたバックアップの圧縮比率は、圧縮対象のデータによって異なります。 実際の圧縮比率は、さまざまな要因の影響を受けます。 主な要因は次のとおりです:
データの型。
文字データは、他のデータ型よりも圧縮されます。
ページ上の各行のデータの一貫性。
一般に、ページ内の複数の行でフィールドに同じ値が格納されている場合、その値が大幅に圧縮される可能性があります。 これに対して、ランダムなデータが格納されたデータベースや、各ページにサイズの大きい行が 1 つだけ含まれているデータベースの場合、圧縮されたバックアップは、圧縮されていないバックアップとそれほど変わらないサイズになります。
データが暗号化されているかどうか
暗号化されたデータは、暗号化されていない同等のデータより、圧縮比率が大幅に下がります。 たとえば、データが Always Encrypted またはその他のアプリケーション レベルの暗号化を使用して列レベルで暗号化されている場合は、バックアップを圧縮しても、サイズの大幅な削減には至らない可能性があります。
Transparent Data Encryption (TDE) を使用して暗号化されたデータベースの圧縮に関する詳細については、「TDE を使用したバックアップの圧縮」を参照してください。
データベースが圧縮されているかどうか。
データベースが圧縮されている場合、バックアップを圧縮しても、サイズが大幅に減少することはありません。
TDE を使用したバックアップの圧縮
SQL Server 2016 (13.x) 以降では、MAXTRANSFERSIZE を 65536 (64 KB) より大きい値に設定することにより、最初にページを復号化し、圧縮してから再度暗号化する、Transparent Data Encryption (TDE) で暗号化されたデータベースの最適化された圧縮アルゴリズムが有効になります。 MAXTRANSFERSIZE が指定されていない場合、または MAXTRANSFERSIZE = 65536 (64 KB) が使用される場合、TDE で暗号化されたデータベースでのバックアップの圧縮では暗号化されたページが直接圧縮され、適切な圧縮比率が得られない可能性があります。 詳細については、「Backup Compression for TDE-enabled Databases」 (TDE が有効になっているデータベースのバックアップの圧縮) を参照してください。
SQL Server 2019 (15.x) CU5 以降では、この最適化された圧縮アルゴリズムを TDE で有効にするために MAXTRANSFERSIZE を設定する必要がなくなりました。 バックアップ コマンドに WITH COMPRESSION が指定されている場合、または backup compression default サーバー構成が 1 に設定されている場合、最適化されたアルゴリズムを有効にするために、MAXTRANSFERSIZE は自動的に 128 K に増加されます。 バックアップ コマンドに MAXTRANSFERSIZE が 64 K より大きい値で指定されている場合は、指定された値が使用されます。 言い換えると、SQL Server によって値は増加されるのみであり、自動的に減少されることはありません。 MAXTRANSFERSIZE = 65536 で TDE で暗号化されたデータベースをバックアップする必要がある場合は、WITH NO_COMPRESSION を指定するか、backup compression default サーバー構成が 0 に設定されていることを確認する必要があります。
詳細については、「BACKUP (Transact-SQL)」を参照してください。
バックアップファイルの領域確保
圧縮されたバックアップの場合、最終的なバックアップ ファイルのサイズは、データをどれくらい圧縮できるかによりますが、それはバックアップ操作が終了するまで不明です。 そのため、既定では、圧縮を使用してデータベースをバックアップする場合、データベース エンジンはバックアップ ファイルのために事前割り当てアルゴリズムを使用します。 このアルゴリズムでは、バックアップ ファイルに、データベースのサイズに対する定義済みの割合のサイズを事前に割り当てます。 バックアップ操作中に、より多くの領域が必要になった場合は、データベース エンジンによってファイルが拡張されます。 バックアップ操作の終了時の最終的なサイズが、割り当てられた領域のサイズを下回る場合は、データベース エンジンによってファイルがバックアップの実際の最終的なサイズに縮小されます。
バックアップ ファイルが、最終的なサイズに達するために必要な分だけを拡張できるようにするには、トレース フラグ 3042 を使用します。 トレース フラグ 3042 は、バックアップ操作が既定のバックアップ圧縮の事前割り当てアルゴリズムを使用しないようにします。 このトレース フラグは、圧縮されたバックアップに実際に必要なサイズだけを割り当てることによって、容量を節約する必要がある場合に便利です。 ただし、このトレース フラグを使用すると、わずかなパフォーマンスの低下 (バックアップ操作の期間が長くなる可能性) が発生することがあります。