SQL Serverのデータベースを使用したシステムの保守をした方はわかると思いますが、復旧モデルは、ほとんど「単純」または「完全」復旧モデルになっていると思います。実はこの復旧モデルは,「単純」「完全」「一括ログ」の3種類があります。この一括ログの使い方がよくわからない?
なので今回復旧モデルについていろいろ調べてみました。
SQLServerに設定されている復旧モデルの種類
SQLServerの復旧モデルは、データベースの変更履歴をトランザクションログファイルに記録する際の管理方法を表したものです。設定できるモデルは以下の3通りになります。
完全復旧モデル
SQL Serverをインストールして、データベースを作成するとこのモデルが既定の設定になります。
トランザクションログにすべての記録を残す方式です。基本的にデータベースの障害が発生し、データベースのリストアが発生した場合でも、データベースが破損した直前まで戻したり、指定した時間に戻すことも可能です。なのでこのモデルで運用するのが本来の姿だと思います。
ただし、トランザクションログの運用をきちんと行わないと、色々なトラブルが発生してしまいます。
- トランザクションログバックアップを定期的に行わないと、ログが切り捨てられない。
- トランザクションログを書き込むディスクIOが発生する。
- トランザクションログが想定外に書き込まれディスクがパンクする。
単純復旧モデル
データファイルに既に反映されたトランザクションログは、自動でログを切り捨ててくれる。
このため、トランザクションログは最小限にとどめることができるがデータベースが破損した場合などは、最後に完全バックアップしたデータベースバックアップの時点までしか復旧できない。
一括ログ復旧モデル
一括ログ復旧モデルは、完全復旧モデルを補完したもので、完全復旧モデルの時に一括操作を行うと大量に書き込まれるトランザクションログを最小限にとどめることができるモデル。
一括操作対象の操作は以下の通りです。
- 一括インポート・・・bcpコマンド、BULK INSERT、INSERT SELECT * FROMなど
- テーブルコピー操作・・・SELECT INTOステートメント
- インデックスDDL操作・・・CREATE INDEX、ALTER INDEX・・・REBUILD
DBCC DBREINDEX、DROP INDEXなど
上記以外の操作は、完全復旧モデルと同様にトランザクションログに書き込まれ、トランザクションログのバックアップを行うまで切り捨てされることがありません。また、一括ログ復旧モデルで記録されたトランザクションの履歴は、時間指定で復旧させることができません。
一括ログ復旧モデルはどんなときに使用する？
一括ログ復旧モデルを使用するときは、完全復旧モデルで運用を行っていて、一時的にインデックスの再構築などを行う際に、一時的に変更するケースだと思います。一括ログ操作が終わった後は、復旧モデルをまた完全復旧モデルに戻すことが大前提です。
まとめると
- 一括ログ復旧モデルのみで運用を行うことは、ほぼない。
- インデックス再構築などの定期的な処理の間だけ完全→一括ログに変更する。
- 一括操作が終わったら必ず完全復旧モデルに戻す。
- 一括ログ→完全に戻したらすぐにトランザクションログバックアップを行う。
- システム要件問題が発生した場合は1日前の状態に戻ればよいという要件などがあれば、その時間に完全バックアップを取得して復旧モデルは単純にしたほうほうが良い。
- 完全から一括ログに変更→一括操作→一括ログから完全に戻す→トランザクションログバックアップ取得がベストの運用
トランザクションログは、一度容量を確保してしまうとバックアップで取得して切り捨てても圧縮しない限りディスク容量はそのまま確保されます。これが原因でディスクのパンクなどが発生してしまうことが多いので気をつけてください。また、復旧要件が緩いのであれば最初から単純復旧モデルで運用するのも現実的だと思います。
コメント