第6回では「エディションのアップグレード」についてまとめてみました。今回は SQL Serverのデータベース 復旧モデル について、自分の整理も兼ねて、まとめてみようと思います。
- データベース復旧モデルについて
- 単純 (Simple) 復旧モデル
- 完全 (Full) 復旧モデル
- 一括ログ (Bulk logged) 復旧モデル
- データベース復旧モデルの変更方法 (SSMS)
- まとめ
データベース復旧モデルについて
データベースの復旧モデルには、以下のようなものがあります。
- 単純 (Simple)
- 完全 (Full)
- 一括ログ (Bulk logged)
各復旧モデルの特徴についてまとめてみようと思います。
単純 (Simple) 復旧モデル
バックアップ (完全バックアップ、差分バックアップ) の完了時点にのみ、データベースの状態を復旧させることが可能な復旧モデルとなります。
トランザクション ログに書き込まれたログレコードは、トランザクションがコミットされ、次回 チェックポイント (checkpoint) のタイミングで切り捨てが行われるため、トランザクション ログのバックアップを実施する必要はなく、一般的に トランザクション ログの肥大化現象が発生しにくい復旧モデルと言えるかと思います。
単純 (Simple) 復旧モデルの注意事項
- AlwaysOn 可用性グループ、データベース ミラーリング、ログ配布の機能など、データベースの復旧モデルが「完全 (Full)」であることが前提となっている機能を使用することが出来ない。
- バックアップ (完全バックアップ、差分バックアップ) の完了時点にのみしかデータベースの状態を復旧させることできないため、目標復旧時点 (RPO: Recovery Point Objective) はバックアップの実行間隔となり、例えば、完全バックアップを1日に1回実施している環境で何らかの障害が発生し、バックアップからデータベースを復元させる必要が発生した場合は、前回 バックアップを採取した以降に発生したトランザクションが損失 (データが損失) することになる。
- 特定の時点 (2020/09/05 14:00 時点など) の状態にデータベースを復元することができない。
単純 (Simple) 復旧モデルのトランザクションログ
[チェックポイント実行前のログ情報]
以下の例は、Insert で 2レコードを挿入後、 コミットを実行した後のトランザクションログ内のログ情報になります。
※ 1トランザクションで1レコード挿入したため、コミットのログレコード「LOP_COMMIT_XACT」が2レコード存在しています。
[チェックポイント実行後のログ情報]
以下の例は、チェックポイント (checkpoint) 実行後のトランザクションログ内のログ情報になります。チェックポイント (checkpoint) 実行後、Insert 処理を実行した際のログレコードが切り捨てられていることが確認できます。
※ チェックポイント (checkpoint) についても何処かのタイミングでまとめてみようと思います。
完全 (Full) 復旧モデル
完全バックアップ、差分バックアップの他に、トランザクション ログのバックアップを利用したデータベースの復元が可能となり、特定の時点の状態にデータベースを復元、および、障害発生直前(トランザクション ログ ファイルが物理的に破損していない場合) の時点の状態にデータベースを回復させることも可能になるため、目標復旧時点 (RPO: Recovery Point Objective) を限りなく「0」にする必要がある場合は、完全 (Full) 復旧モデルを選択し、データベースを運用する必要があるかと思います。
完全 (Full) 復旧モデルの注意事項
- トランザクション ログのログ レコードの切り捨ては、トランザクション ログのバックアップ時に行われるため、定期的に トランザクション ログのバックアップを実施する必要がある。(定期的に トランザクション ログのバックアップを実施しなければ、トランザクション ログのログ レコードの切り捨てが行われないため、トランザクション ログが肥大化し、ディスクの空き容量を圧迫する事態となり、その後、ディスクの空き容量が枯渇した場合、DML操作 (INSERT, UPDATE, DELETE) を実施することができなくなります。)
- 完全 (Full) 復旧モデルに変更後、該当のデータベースの完全バックアップが完了した時点より、トランザクション ログのログ レコードの蓄積が始まります。完全 (Full) 復旧モデルに変更したのみでは、復旧モデルの動作は変わらないため、特に 復旧モデルを 単純 (Simple) から 完全 (Full) に変更した場合は、完全バックアップを採取する必要があることを認識しておくと良いかと思います。
完全 (Full) 復旧モデルのトランザクションログ
[チェックポイント実行前のログ情報]
以下の例は、Insert で 1レコードを挿入後、 コミットを実行した後のトランザクションログ内のログ情報になります。
[チェックポイント実行後のログ情報]
チェックポイント (checkpoint) 実行後、 単純 (Simple) 復旧モデルとは異なり、ログ レコードの切り捨ては行われず、チェックポイント (checkpoint) のログ レコードが追記されていることが確認できます。
[トランザクション ログ バックアップ後のログ情報]
Insert で 1レコードを挿入後、 コミットを実行したトランザクションログ内のログ レコードが切り捨てられていることが確認できます。
完全 (Full) 復旧モデルの場合、トランザクション ログのバックアップを使用して、特定の時点の状態、および、障害発生直前の状態にデータベースを回復できる必要があるため、トランザクション ログのバックアップにて確実にトランザクションログ内のログ レコードのバックアップが採取されるまで、トランザクションログ内のログ レコードの切り捨てが行われなくなります。「完全 (Full) 復旧モデルの注意事項」にも記載しましたが、完全 (Full) 復旧モデルでデータベースを運用する場合、確実に トランザクション ログのバックアップを定期的に採取することを検討する必要があるかと思います。
一括ログ (Bulk logged) 復旧モデル
大量のデータをデータベースに挿入する(一括インポート処理 (Bulk処理))場合、トランザクション ログにデータ挿入のログ レコードが挿入されることで、ログ レコードの書き込みがボトルネックとなり、処理のパフォーマンスが低下したり、トランザクション ログが肥大化するなどの現象が発生する可能性があります。
一括ログ (Bulk logged) 復旧モデルでは、一括インポート処理 (Bulk処理) の際、「最小ログ記録」により、トランザクション ログに書き込むログ レコードの量を最小限にすることが可能になります。
また、データベースの復旧モデルを「完全 (Full)」から「単純 (Simple)」に変更すると、トランザクション ログのログ チェーンが途切れるため、今まで採取したトランザクション ログのバックアップを使用したデータベースの復旧ができなくなりますが、「完全 (Full)」＜－＞「一括ログ (Bulk logged)」の相互の変更では、トランザクション ログのログ チェーンが途切れないため、今までに採取したトランザクション ログのバックアップを使用したデータベースの復旧が可能です。
一括ログ (Bulk logged) 復旧モデルの注意事項
- 「最小ログ記録」が使用できる処理は、「bcp」、「BULK INSERT」、「INSERT SELECT」、「インデックスの作成」、「インデックスの再構築操作」など、一部の処理に限られている。
- 「最小ログ記録」を使用するためには、データ挿入先のテーブルに対して「テーブル ロック」を獲得する必要があったり、該当テーブル上のインデックスの有無、該当テーブルのデータの有無 (データが空であるか、データが存在するか) などの細かい前提条件が存在する。
「最小ログ記録」を使用するための前提条件の詳細は、以下の URL を参照。
- 「最小ログ記録」で挿入されたデータは、トランザクション ログ上にログ レコードとしては挿入されませんが、一括インポート処理 (Bulk処理) 後の最初のトランザクション ログのバックアップに、データが挿入されたデータ ページ (エクステント単位) が含まれるようになるため、通常の トランザクション ログのバックアップファイルよりもサイズが大きくなる可能性があります。
- レプリケーションの対象になっているテーブルに対しては「最小ログ記録」を使用することが出来ない。
[一括インポート処理 (Bulk処理)後のログ情報]
インサート操作のログ「LOP_INSERT_ROWS」の代わりに、新たに割り当てられエクステント(データが挿入されたページを含む) 情報が記録されます。
一括インポート処理 (Bulk処理) 後の最初のトランザクション ログのバックアップには、データベース 物理ファイル (以下の例では「DB10」) のページが含まれていることが確認できます。
| データベース 'db10' の 50096 ページ、ファイル 1 のファイル 'DB10' を処理しました。 データベース 'db10' の 259 ページ、ファイル 1 のファイル 'DB10_log' を処理しました。 BACKUP LOG により 50355 ページが 3.117 秒間で正常に処理されました (126.210 MB/秒)。 |
データベース復旧モデルの変更方法 (SSMS)
1) SQL Server Management Studio (SSMS) を起動し、該当の SQL Server インスタンスに管理者権限 (sysadmin) が付与されたログインで接続します。
2) 該当データベース - 右クリック -「プロパティ」を選択します。
3) ページの選択：オプション - 復旧モデル「一括ログ」を選択後、「OK」ボタンを選択します。
[補足]
データベースの復旧モデルは、オンラインで変更可能で、即座に設定が反映されます。
しかしながら、データベースの復旧モデルを「単純 (Simple)」から「完全 (Full)」へ変更した場合、「完全 (Full)」の復旧モデルとして動作させるためには、該当データベースの完全バックアップを採取する必要があります。
また、「完全 (Full)」を前提とした機能 (AlwaysOn 可用性データベース、ミラーリングなど) を有効にしているデータベースの場合、データベースの復旧モデルを変更するためには、該当の機能を解除する必要があります。
まとめ
今回は SQL Serverのデータベース 復旧モデル についてまとめてみました。データベースの復旧モデルは、 目標復旧時点 (RPO: Recovery Point Objective) などの要件に応じて選択されると良いかと思います。
また、一括ログ (Bulk logged) 復旧モデルは、「最小ログ記録」の前提条件を満たすことができるかを確認後、一括インポート処理 (Bulk処理) のパフォーマンスなどを向上したい場合に検討してみると良いかと思います。
【第8回】基本から始める SQL Server【バックアップ／リストア】へ
