Skip to content

MS_SQLServerDisasterRecovery

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 2 revisions

SQL Server の障害復旧

概要

障害復旧に関するオプションの説明。

SQL Server の復旧モデル

SQL Server では、「データ消失に対する保護」、「性能」、および
「ディスクとテープの容量」などの要件に対応するための目的とした
**3 種類の「復旧モデル」**が用意されている。
このため、「復旧モデル」を選択する場合は、
次の業務上の条件とトレードオフを考慮する必要がある。

  • コミットされたトランザクションの消失など、「データ消失の可能性」。
  • インデックスの作成や一括ロードなど、大量操作の「性能」。
  • 「トランザクション ログ」が使用する領域の「容量」。
  • バックアップ リストアの「手順の単純さ」。

概要

実行する操作の種類によっては、適切な「復旧モデル」が複数あることもある。
「復旧モデル」を選択した後は、バックアップ、リストアの手順を計画する必要がある。

単純 復旧モデル

  • 「単純 復旧モデル」は、処理性能に優れる一括コピーであるが、
    「チェック ポイント」が発生するたびに
    「トランザクション ログ」が切り捨てられる。
  • このため、必要なスペースを抑制できるが、
    最新の「完全バックアップ」または「差分バックアップ」の時点にしか復旧できない。
    • バックアップ、リストアの手順は、
      「完全バックアップ」または「差分バックアップ」のみサポートしている。
    • 「トランザクション ログ」が切り捨てられるため、
      「トランザクション ログ バックアップ」はサポートされない。
  • 最小限の管理で済むが、「データ ファイル」が損傷を受けた場合の
    データ消失の可能性が高い。
    • 最新の変更内容の消失が許されない OLTP システムの場合、
      顧客要件や、ストレージの信頼性によるが、
      「単純 復旧モデル」は適切ではない場合がある。
    • ケース バイ ケースで、「データ消失」と「性能」を考慮した
      バックアップ間隔に調整する。
      • バックアップのオーバーヘッドが業務に影響しない程度に長い間隔に調整する。
      • 大量のデータを消失しないで済む程度に短い間隔に調整する。

単純 復旧モデル

完全 復旧モデル

データが最大限に保護される。
このモデルは「トランザクション ログ」からデータを復旧することができる。

  • 「完全 復旧モデル」では、全てのトランザクションが
    「トランザクション ログ」に記録されるため、完全な復旧が可能である。
  • 「トランザクション ログ」には、全ての操作が記録される。このため、
    • 大規模な操作の場合は、性能が問題となる。
    • 「トランザクション ログ」を保持する、ある程度のログ領域が必要になる。
  • 復元の手順は、
    • 「完全バックアップ」のリストア、
    • 「差分バックアップ」のリストア、
    • 「トランザクション ログ バックアップ」のリストアを実施する。

完全 復旧モデル

一括ログ 復旧モデル

データが最大限に保護される。
このモデルは「トランザクション ログ」からデータを復旧することができる。

  • 「一括ログ 復旧モデル」では、特定の大規模な操作を除いた
    全てのトランザクションが「トランザクション ログ」に記録されるため、
    ほぼ完全な復旧が可能である。
  • 特定の大規模操作の際に、「トランザクション ログ」には
    エクステントのビットだけが記録される。
    このため、高い性能を実現し、「トランザクション ログ」のスペースを抑制できる。
    「一括ログ 復旧モデル」でログが記録されない大規模操作は以下のとおり。
    • SELECT INTO 操作（検索結果をテーブルに挿入する処理）
    • bcp ユーティリティを使用した大量データのインポート、エクスポート
    • BULK INSERT を使用した大量データのインポート
    • CREATE INDEX（その他、INDEX のデフラグなど）
    • text 操作と、image 操作
  • 大規模操作を実行した後に「トランザクション ログ」をバックアップすれば、
    その際に、「データ ファイル」のエクステントから、
    最後のバックアップ以降の大規模操作が
    「トランザクション ログ バックアップ」に反映される。
  • このため、大規模操作を実行した後は、
    「トランザクション ログ バックアップ」を利用した
    データの（指定時点への）復旧ができなくなるが、
    大規模操作後、直ちに「トランザクション ログ」をバックアップすれば、
    その時点までの復旧が可能になる。
  • 「完全 復旧モデル」では、「一括読み込み」「インデックス作成」などの
    大規模操作に長い時間がかかるので、場合によっては、
    「完全 復旧モデル」と「一括ログ 復旧モデル」を切り替える。

一括ログ 復旧モデル

利点、欠点

復旧モデル データ消失の影響度 性能 運用手順の難易度 必要なログ領域の容量
単純 大 高 容易 小
完全 最小 低 普通 大
一括ログ 小 中 難しい 中

補足（実務ではほぼ「単純」か「完全」の二択）: 一括ログ 復旧モデルは
常用するものではなく、大規模操作の間だけ一時的に切り替えるものである。

ケース 復旧モデル
開発・検証、日次バッチで作り直せる DWH 単純
業務 OLTP（1 件の消失も許されない） 完全
完全運用中の夜間の大量ロード・索引再構築の間だけ 一括ログ（終わったら完全へ戻す）

「完全 復旧モデルにしたのにログ バックアップを取っていない」というのが
最も多い事故で、この場合トランザクション ログが際限なく増え続けて
ディスクを食い潰す（SQL Server のバックアップを参照）。
完全復旧モデルの採用は、ログ バックアップの運用とセットである。

設定方法

  • 既定の「復旧モデル」を変更するには、model DB の「復旧モデル」を変更する。
  • 作成済みの DB の「復旧モデル」を変更するには、各 DB の「復旧モデル」を変更する。

Management Studio（MS_SSMS.md）

  • オブジェクト エクスプローラーから、変更したいデータベースを選択
  • データベースを右クリックし、「プロパティ」を選択
  • プロパティ・ダイアログの「オプション」ページを開く
  • 「復旧モデル」ドロップダウン メニューから、変更したい復旧モデルを選択
  • 「OK」をクリックして変更を保存

T-SQL による設定

ALTER DATABASE ステートメントの RECOVERY 句で設定する。

ALTER DATABASE [DB名] SET RECOVERY [復旧モデル]

[復旧モデル] に指定する文字列は以下の通り。

指定値 復旧モデル
FULL 完全 復旧モデル
BULK_LOGGED 一括ログ 復旧モデル
SIMPLE 単純 復旧モデル

DB に設定された「復旧モデル」は、DATABASEPROPERTYEX 関数に
Recovery プロパティを設定し、調べることができる。

SELECT DATABASEPROPERTYEX('[DB名]','Recovery')

移行メモ（見出しの表記）: 元ページは本項の見出しを
「sp_configure による設定」としているが、
復旧モデルは sp_configure では設定できない
（sp_configure はサーバー インスタンス単位の構成オプション用で、
復旧モデルはデータベース単位のプロパティである）。
実際に本文が示しているとおり ALTER DATABASE ... SET RECOVERY が正しい。
後述の「recovery interval」オプションのほうが sp_configure の対象である。

切り替え操作

変更後に、必要に応じて「トランザクション ログ」をバックアップする

切り替えのパターン

  • 完全復旧 → 一括ログ復旧
  • 一括ログ復旧 → 完全復旧

必要な操作（共通）

  • バックアップの計画に変更はない。
  • 「トランザクション ログ バックアップ」を実施すれば、
    「データ ファイル」のエクステントから、最後のバックアップ以降の大規模操作が
    「トランザクション ログ バックアップ」に反映される。

必要な操作（個別）

  • 完全復旧 → 一括ログ復旧
    大規模操作はトランザクション ログに記録されないようになるので、
    大規模操作のバックアップが重要な場合は、
    適宜「トランザクション ログ バックアップ」を実施する。
  • 一括ログ復旧 → 完全復旧
    切り替え後、指定日時への復旧が重要な場合は、
    切り替え直後に「トランザクション ログ バックアップ」を実行する。

変更前に「トランザクション ログ」をバックアップする

切り替えのパターン

  • 完全復旧 → 単純復旧
  • 一括ログ復旧 → 単純復旧

必要な操作（共通）

  • 切り替え直前に「トランザクション ログ」をバックアップすると、
    その時点の状態にまで復旧できる。
  • 切り替え後は、「トランザクション ログ」が無効になるので、
    「単純 復旧モデル」用のバックアップの計画に変更する。

変更後に、DB の「完全バックアップ」を実行する

切り替えのパターン

  • 単純復旧 → 完全復旧
  • 単純復旧 → 一括ログ復旧

必要な操作（共通）

  • 切り替え後に「トランザクション ログ」が有効になるため、
    切り替え直後に「トランザクション ログ バックアップ」のベースとなる
    「完全バックアップ」、「差分バックアップ」を実行する。
  • その後、「完全 復旧モデル」、「一括ログ 復旧モデル」用の
    バックアップの計画に変更する。

補足（この 3 パターンの覚え方）: 切り替えの前後どちらで
バックアップを取るかは、**「ログの鎖（log chain）が切れるかどうか」**で
決まる。

切り替え ログの鎖 対処
完全 ⇄ 一括ログ 切れない 変更後に必要に応じて
完全/一括ログ → 単純 切れる（ログが捨てられる） 変更前に取っておく
単純 → 完全/一括ログ 鎖が無い（起点が要る） 変更後に完全バックアップ

「単純に落とす前に取る、単純から上げた後に取る」と覚えればよい。

「recovery interval」オプション

障害発生後、SQL Server インスタンスの再起動時に発生する
復旧処理の最大時間（分単位） を設定する。

復旧処理では、

  • ロールバック
  • ロールフォワード

が行われる。

  • SQL Server インスタンスは、この設定と内部アルゴリズムにより、
    「自動チェック ポイントの実行頻度」を判断し、
    復旧時間が「recovery interval」オプションで指定された時間以上に
    ならないようにする。
    SQL Server インスタンスは内部の処理量に応じて、
    「チェック ポイント」の間隔を決める。
  • 内部の作業量（データ変更処理など）が多いほど、
    「チェック ポイント」処理は頻繁に実行される。
    これは、「データ ファイル」にフラッシュされていないデータ変更が少ないほど、
    復旧時の処理時間が短くなるためである。

SQL Serverの復旧処理の概要

「チェック ポイント」処理

「チェック ポイント」処理とは、
「バッファ キャッシュ」中のデータを「データ ファイル」に
フラッシュする処理である。

※ データ変更は、コミット、未コミットに関係なく、
すべて「トランザクション ログ ファイル」に書き込まれる。

「チェック ポイント」処理

復旧処理（ロールバック、ロールフォワード）

復旧処理では、「データ ファイル」にフラッシュされていなかった変更を
「トランザクション ログ」上の記録を元に、「データ ファイル」に反映する。
この際、

  • 「コミット」されたトランザクションは、「ロール フォワード」され、
  • 「未コミット」のトランザクションは、「ロール バック」される。

ロールバック、ロールフォワード

補足（WAL とチェック ポイント）: この仕組みは
WAL（Write Ahead Logging：ログ先行書き込み） と呼ばれ、
商用 RDBMS に共通する。

更新 ──> [ログ ファイル] へ即時書き込み（順次 I/O・速い）
      └> [バッファ キャッシュ] を更新（メモリ）
                    │ チェック ポイント（まとめて）
                    ▼
             [データ ファイル]（ランダム I/O・遅い）

「コミット時にデータ ファイルへ書かない」ことで性能を稼ぎ、
障害時はログから作り直す。したがって、

  • ログ ファイルは別ディスクに置く（順次 I/O を邪魔しない）
  • ログ ファイルを失うと復旧できない

という設計上の要請が出てくる
（SQL Server のファイルの配置）。

チューニングの考え方

復旧時間を短くしたい場合

「recovery interval」オプションで復旧時間を短く設定する。
この場合、「チェック ポイント」処理は頻繁に実行されるため I/O が増える。

I/Oを減らしたい場合

「recovery interval」オプションで復旧時間を長く設定すれば、
「チェック ポイント」処理の間隔を長くすることができる。
このため、I/O が減少するので、（I/O に関する）性能向上が期待できる。

設定方法

Management Studio（MS_SSMS.md）

  • オブジェクト エクスプローラーでサーバー インスタンスを右クリックし
    [プロパティ] をクリック
  • [データベースの設定] ノードを選び、[復旧] の [復旧間隔 (分単位)] ボックスで、
    0 ～ 32767 の値を入力するか選択

「sp_configure」による設定

  • 既定値は 0。この場合、復旧時間は 1 分未満。
  • 値を 5 に設定した場合、復旧時間は 5 分未満になる。
EXEC sp_configure 'recovery interval', n
RECONFIGURE
EXEC sp_configure
GO

n の単位は、分で指定する。

移行メモ（最新化：間接チェックポイント）: SQL Server 2016 以降、
新規に作成したデータベースの既定は「間接チェックポイント」
（TARGET_RECOVERY_TIME = 60 秒）になっている。

自動チェックポイント（従来） 間接チェックポイント（既定）
設定 sp_configure 'recovery interval'（インスタンス単位・分） ALTER DATABASE ... SET TARGET_RECOVERY_TIME（DB 単位・秒）
動作 ログ量から間隔を推定 ダーティ ページ数を継続的に制御
復旧時間 ばらつく 予測しやすい 
ALTER DATABASE [DB名] SET TARGET_RECOVERY_TIME = 60 SECONDS;

TARGET_RECOVERY_TIME が 0 より大きい DB では、
本ページの recovery interval は効かない。
考え方（復旧時間と I/O のトレードオフ）は本ページの記述どおりである。

補足（さらに新しい選択肢：ADR）: SQL Server 2019 以降には
ADR（Accelerated Database Recovery：高速データベース復旧） がある。
長時間トランザクションのロールバックをほぼ即座に終わらせ、
起動時の復旧時間を大幅に短縮する（その分ストレージを消費する）。
「巨大な更新を ROLLBACK したら何時間も終わらない」という
従来の問題への回答である。

参考

Microsoft Learn

その他

関連

Tags: 移行, データアクセス, SQL Server, 障害対応

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally