MS_SQLServerDisasterRecovery - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

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

  • オブゞェクト ゚クスプロヌラヌから、倉曎したいデヌタベヌスを遞択
  • デヌタベヌスを右クリックし、「プロパティ」を遞択
  • プロパティ・ダむアログの「オプション」ペヌゞを開く
  • 「埩旧モデル」ドロップダりン メニュヌから、倉曎したい埩旧モデルを遞択
  • 「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

  • オブゞェクト ゚クスプロヌラヌでサヌバヌ むンスタンスを右クリックし
    [プロパティ] をクリック
  • [デヌタベヌスの蚭定] ノヌドを遞び、[埩旧] の [埩旧間隔 (分単䜍)] ボックスで、
    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コン゜ヌシアム 開発基盀郚䌚」によっお運営されおいたす。

⚠ **GitHub.com Fallback** ⚠