SQL Server の障害復旧

「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

• 戻る
  • SQL Server
    • SQL Server の基本的な設定
    • SQL Server のバックアップ

目次 †

概要 †

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

SQL Server の復旧モデル †

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

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

概要 †

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

次に、SQL Server の3 種類の「復旧モデル」の機能概要を示す。

単純 復旧モデル †

• 「単純 復旧モデル」は、処理性能に優れる一括コピーであるが、「チェック ポイント」が発生するたびに「トランザクション ログ」が切り捨てられる。
• このため、必要なスペースを抑制できるが、最新の「完全バックアップ」または「差分バックアップ」の時点にしか復旧できない。
  • バックアップ、リストアの手順は、「完全バックアップ」または「差分バックアップ」のみサポートしている。
  • 「トランザクション ログ」が切り捨てられるため、「トランザクション ログ バックアップ」はサポートされない

• 最小限の管理で済むが、「データ ファイル」が損傷を受けた場合のデータ消失の可能性が高い。
  • 最新の変更内容の消失が許されないOLTPシステムの場合、
    顧客要件や、ストレージの信頼性によるが、「単純 復旧モデル」は適切ではない場合がある。
  • ケース バイ ケースで、「データ消失」と「性能」を考慮したバックアップ間隔に調整する。
    • バックアップのオーバーヘッドが業務に影響しない程度に長いバックアップ間隔に調整する。
    • 大量のデータを消失しないで済む程度に短いバックアップ間隔に調整する。

完全 復旧モデル †

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

• 「完全 復旧モデル」では、全てのトランザクションが、
  「トランザクション ログ」に記録されるため、完全な復旧が可能である。

• 「トランザクション ログ」には、全ての操作が記録される。
  このため、
  • 大規模な操作の場合は、性能が問題となる。
  • 「トランザクション ログ」を保持する、ある程度のログ領域が必要になる。

• 復元の手順は、
  • 「完全バックアップ」のリストア、
  • 「差分バックアップ」のリストア、
  • 「トランザクション ログ バックアップ」のリストアを実施する。

一括ログ 復旧モデル †

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

• 「一括ログ 復旧モデル」では、特定の大規模な操作を除いた、
  全てのトランザクションが、「トランザクション ログ」に記録されるため、
  ほぼ完全な復旧が可能である。

• 特定の大規模操作の際に、「トランザクション ログ」には、エクステントのビットだけが記録される。
  このため、高い性能を実現し、「トランザクション ログ」のスペースを抑制できる。
  「一括ログ 復旧モデル」でログが記録されない大規模操作は以下のとおり。
  • SELECT INTO操作（検索結果をテーブルに挿入する処理）
  • bcpユーティリティを使用した大量データのインポート、エクスポート
  • BULK INSERTを使用した大量データのインポート
  • CREATE INDEX（その他、INDEXのデフラグなど）
  • text操作と、image操作

• 大規模操作を実行した後に「トランザクション ログ」をバックアップすれば、その際に、
  「データ ファイル」のエクステントから、最後のバックアップ以降の大規模操作が
  「トランザクション ログ バックアップ」に反映される。

• このため、大規模操作を実行した後は、
  「トランザクション ログ バックアップ」を利用したデータの復旧ができなくなるが、
  大規模操作後、直ちに「トランザクション ログ」をバックアップすれば、
  その時点までの復旧が可能になる。

• 「完全 復旧モデル」では、「一括読み込み」「インデックス作成」などの
  大規模操作に長い時間がかかるので、場合によっては、
  「完全 復旧モデル」と「一括ログ 復旧モデル」を切り替える。

利点、欠点 †

以下の表に、それぞれの「復旧モデル」の利点と欠点を示す。

設定方法 †

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

Management Studio †

「sp_configure」による設定 †

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

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

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

  「FULL」	「完全 復旧モデル」
  「BULK_LOGGED」	「一括ログ 復旧モデル」
  「SIMPLE」	「単純 復旧モデル」

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

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

切り替え操作 †

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

• 切り替えのパターン
  • 完全復旧 ---> 一括ログ復旧
  • 一括ログ復旧 ---> 完全復旧

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

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

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

• 切り替えのパターン
  • 完全復旧 ---> 単純復旧
  • 一括ログ復旧 ---> 単純復旧

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

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

• 切り替えのパターン
  • 単純復旧 ---> 完全復旧
  • 単純復旧 ---> 一括ログ復旧

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

「recovery interval」オプション †

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

復旧処理では、

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

が行われる。

• SQL Serverインスタンスは、この設定と内部アルゴリズムにより、「自動チェック ポイントの実行頻度」を判断し、
  復旧時間が「recovery interval」オプションで指定された時間以上にならないようにする。
  SQL Serverインスタンスは内部の処理量に応じて、「チェック ポイント」の間隔を決める。

• 内部の作業量（データ変更処理など）が多いほど、「チェック ポイント」処理は頻繁に実行される。
  これは、「データ ファイル」にフラッシュされていないデータ変更が少ないほど、復旧時の処理時間が短くなるためである。

SQL Serverの復旧処理の概要 †

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

「チェック ポイント」処理とは、「バッファ キャッシュ」中のデータを「データ ファイル」にフラッシュする処理である。
※ データ変更は、コミット、未コミットに関係なく、すべて「トランザクション ログ ファイル」に書き込まれる。

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

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

チューニングの考え方 †

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

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

I/Oを減らしたい場合 †

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

設定方法 †

Management Studio †

「sp_configure」による設定 †

• 既定値は０。
  この場合、復旧時間は１分未満

• 値を５に設定した場合
  この場合、復旧時間は５分未満で、

EXEC sp_configure 'recovery interval', n
RECONFIGURE
EXEC sp_configure
GO

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

参考 †

• MSDN
  • 復旧モデル (SQL Server)
    http://msdn.microsoft.com/ja-jp/library/ms189275.aspx
    • データベースの復旧モデルの表示または変更 (SQL Server)
      http://msdn.microsoft.com/ja-jp/library/ms189272.aspx

• 復旧モデルとトランザクション ログの管理
  http://msdn.microsoft.com/ja-jp/library/ms366344.aspx
  • 復旧モデルの概要
    http://msdn.microsoft.com/ja-jp/library/ms189275.aspx
  • データベースの復旧モデルの選択
    http://msdn.microsoft.com/ja-jp/library/ms175987.aspx

• その他
  • SQL Server の復旧モデルとトランザクション ログ - 松本崇博 Blog （SQL Server Tips）
    http://d.hatena.ne.jp/matu_tak/20091224/1261710845

• SQLServer2008完全復旧モデルにおけるデータ圧縮 simBlog
  http://simosan.minibird.jp/wordpress/sql-server/sqlserver2008%E5%AE%8C%E5%85%A8%E5%BE%A9%E6%97%A7%E3%83%A2%E3%83%87%E3%83%AB%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8B%E3%83%87%E3%83%BC%E3%82%BF%E5%9C%A7%E7%B8%AE/

• SQL Server 2008 以降のログの切り捨て SE の雑記
  http://engineermemo.wordpress.com/2011/06/29/sql-server-2008-%E4%BB%A5%E9%99%8D%E3%81%AE%E3%83%AD%E3%82%B0%E3%81%AE%E5%88%87%E3%82%8A%E6%8D%A8%E3%81%A6/

SQL Server のバックアップ †

---

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