• [アーティクル]

SQL Server

効果的なデータベース メンテナンスのヒント

Paul S. Randal

 

概要 :

  • データ ファイルとトランザクション ログ ファイルを管理する
  • インデックスの断片化を解消する
  • 正確な最新の統計を得る
  • 破損したデータベース ページを検出する
  • 効果的なバックアップ戦略を確立する

目次

データ ファイルとログ ファイルの管理
インデックスの断片化
統計
破損の検出
バックアップ
まとめ

私は 1 週間のうちに何回か、運用データベースの効果的なメンテナンス方法に関するアドバイスを求められます。また、新しいソリューションの実装を進めていて、新しいデータベースの特性に

合ったメンテナンス方法を探している DBA の皆さんから質問を受けることもあります。ただし、プロの DBA ではなく、なんらかの理由でデータベースの所有者となり、その責任を負っている方から質問を受けることの方がよくあります。私は、このような役割をよく "不本意な DBA" と呼んでいます。この記事では、世の中のすべての不本意な DBA の皆さんに、データベースのメンテナンスに関するベスト プラクティスの手引きを提供します。

IT 分野における大半の作業や手続きと同様、データベースの効果的なメンテナンスを行うための簡単かつ汎用的なソリューションは存在しません。ただし、ほぼ常に対処する必要がある重要な領域がいくつか存在します。私が考える重要な 5 つの領域は、次のとおりです (以下の順序は重要度とは無関係です)。

  • データ ファイルとログ ファイルの管理
  • インデックスの断片化
  • 統計
  • 破損の検出
  • バックアップ

データベースがメンテナンスされていない (または不適切にメンテナンスされた) 場合、上記の 1 つ以上の領域で問題が発生する可能性があります。これらの問題が発生した場合、最終的にアプリケーションのパフォーマンスが低下するだけでなく、ダウンタイムやデータの損失が発生することもあります。

この記事では、これらの問題が重要である理由について説明し、問題を軽減するための簡単な方法をいくつか紹介します。ここでの説明は、SQL Server ® 2005 に基づいていますが、SQL Server 2000 や新しい SQL Server 2008 を使用する場合は主にどのような違いが生じるかについても説明します。

データ ファイルとログ ファイルの管理

データベースの管理作業を引き継ぐときに、まず確認すべき領域として必ずお勧めしているのは、データ ファイルと (トランザクション) ログ ファイルの管理に関する設定です。具体的には、次のことを確認します。

  • データ ファイルとログ ファイルが分かれていて、他のものからも分離されている
  • 自動拡張が正しく構成されている
  • ファイルの瞬時初期化が構成されている
  • 自動圧縮が有効ではなく、メンテナンス プランに圧縮が含まれていない

データ ファイルとログ ファイル (これらは別々のボリューム上に格納されていることが理想的です) が、ファイルを作成または拡張する他のアプリケーションと同じボリューム上に格納されている場合、ファイルの断片化が発生する可能性があります。データ ファイル内で過剰に断片化が進むと、クエリ (特に大量のデータをスキャンするクエリ) のパフォーマンスが若干低下する可能性があります。ログ ファイルの断片化が進んだ場合、パフォーマンスは大幅に低下する可能性があります。特に、各ファイルが必要になるたびに少量ずつサイズを増加するように自動拡張が設定されている場合、この違いは顕著に現れます。

ログ ファイルの内部は仮想ログ ファイル (VLF) というセクションに分かれていて、ある 1 つのログ ファイル (複数のログ ファイルを使用するメリットはなく、データベース 1 つに対して 1 つのファイルを用意した方がよいため、ここではあえて "1 つのログ ファイル" としています) の断片化が進むと、VLF の数が増加します。たとえば、1 つのログ ファイルの VLF が 200 である場合、ログの読み取り (トランザクションのレプリケーションやロールバックなど)、ログのバックアップ、SQL Server 2000 のトリガ (SQL Server 2005 ではトリガの実装が変更されていて、トランザクション ログではなく行バージョン管理フレームワークが使用されます) など、ログに関連する操作のパフォーマンスが低下する可能性があります。

データ ファイルとログ ファイルのサイズ調整に関するベスト プラクティスは、ファイルの作成時に適切な初期サイズを設定することです。データ ファイルの場合は、短い期間内でデータベースにデータが追加される可能性を考慮して初期サイズを設定します。たとえば、データの初期サイズが 50 GB でも、今後 6 か月間でさらに 50 GB のデータが追加されることがわかっている場合は、データ ファイルを作成する時点でサイズを 100 GB に設定した方が、最終的にこのサイズになるまでファイルの拡張を繰り返すよりも適切です。

残念ながらログ ファイルの場合はもう少し複雑で、トランザクションのサイズ (実行時間が長いトランザクションは、完了するまでログから削除できません) やログのバックアップ (このときに、ログ内で使用されていない部分が削除されます) 頻度などの要因を考慮する必要があります。詳細については、私の妻である Kimberly Tripp が SQLskills.com に掲載した人気のブログ記事「トランザクション ログのスループットを高める 8 つの手順」を参照してください。

構成が完了したら、ファイルのサイズを頻繁に監視して、1 日のうちで適切な時間に、先を見通して手動でファイルを拡張します。自動拡張は、万一に備えて有効にしておき、なんらかの異常が発生して拡張が必要になった場合に対処できるようにします。私がファイルの管理を完全に自動拡張に頼ることに反対する 1 つ目の理由は、少量ずつ自動拡張することによって、ファイルの断片化が発生するためです。そして 2 つ目の理由は、自動拡張は処理に時間がかかる可能性があり、その結果、予測できないタイミングでアプリケーションのワークロード処理が妨げられる場合があるためです。

自動拡張のサイズを割合ではなく特定の値に設定して、自動拡張が発生した場合に使用される時間と領域を制限する必要があります。たとえば、100 GB のデータ ファイルを自動拡張するときに使用するサイズを、10% ではなく固定値の 5 GB に設定します。これによって、ファイルのサイズが増加するたびに、拡張されるサイズも (たとえば 10 GB、11 GB、12 GB と) 増加するのではなく、ファイルのサイズが最終的にどれほど大きくなったとしても、常に 5 GB 単位で拡張されます。

トランザクション ログが (手動または自動拡張によって) 拡張されるときは、常にゼロで初期化されます。データ ファイルの既定の動作は SQL Server 2000 と同じですが、SQL Server 2005 からは、ファイルの瞬時初期化を利用できるようになります。瞬時初期化では、ファイルをゼロで初期化する処理が省略されるため、手動での拡張や自動拡張が事実上瞬時に完了します。世間のイメージとは異なるかもしれませんが、この機能は SQL Server のすべてのエディションで提供されています。詳細については、SQL Server 2005 Books Online または SQL Server 2008 オンライン ブックで「ファイルの瞬時初期化」というキーワードで検索を実行し、検出されたページを参照してください。

また、決して圧縮を有効にしないようにしてください。圧縮を使用すると、データ ファイルやログ ファイルのサイズを削減できますが、これはリソースを大量に消費する非常に厄介な処理です。また、データ ファイル内で大量の論理スキャンの断片化が発生し (詳細については後述します)、パフォーマンスの低下にもつながります。私は、圧縮に関する SQL Server 2005 Books Online のトピックに変更を加え、この影響に関する警告を追加しました。ただし、特別な状況では、個々のデータ ファイルとログ ファイルを手動で圧縮する作業を行ってもよいでしょう。

自動圧縮は、パフォーマンスへの影響が最も大きな処理で、バックグラウンドで 30 分おきに起動し、自動圧縮データベース オプションが有効になっているデータベースの圧縮を試行します。これは、空き領域が 25% を超えるデータベースのみを圧縮するという点で、やや予測不可能な処理です。自動圧縮は多くのリソースを使用し、断片化によるパフォーマンスの低下を招くため、どのような状況であっても決して良い考えとは言えません。このため、次のコマンドを使用して必ず自動圧縮を無効にしてください。

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

手動のデータベース圧縮コマンドが組み込まれた定期的なメンテナンス プランを使用することも、ほぼ同じくらいお勧めできません。メンテナンス プランでデータベースが圧縮された後に、データベースが繰り返し拡張される場合、そのデータベースの実行には、拡張された分の領域が必要であることを示しています。

ベスト プラクティスは、安定したサイズになるまでデータベースを拡張し、決して圧縮を実行しないようにすることです。圧縮の欠点について、および SQL Server 2005 で使用される新しいアルゴリズムについては、私が以前に執筆した MSDN® ブログの記事 (blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx) を参照してください。

インデックスの断片化

ファイル システム レベルやログ ファイル内の断片化だけでなく、テーブルとインデックス データを保存する構造内でも、データ ファイルの断片化が発生する可能性があります。基本的に、データ ファイル内で発生する断片化には次の 2 種類があります。

  • 個々のデータおよびインデックス ページ内での断片化 (内部断片化と呼ばれることもあります)
  • 複数のページで構成されるインデックスまたはテーブル構造内の断片化 (論理スキャンの断片化またはエクステント スキャンの断片化と呼ばれます)

内部断片化は、ページの空き領域が多い状態を指します。図 1 からわかるように、データベースの各ページのサイズは 8 KB で、ページごとに 96 バイトのヘッダーが含まれます。したがって、1 ページにつき約 8,096 バイトのテーブルまたはインデックス データを格納できます (特定のテーブルやインデックスのデータおよび行構造については、私が sqlskills.com/blogs/paul で公開しているブログの「ストレージ エンジンの内部」カテゴリを参照してください)。各テーブルまたはインデックス レコードがページ サイズの半分より大きく、それによって 1 ページにつき 1 つのレコードしか保存できない場合、空き領域が発生します。この状況では、テーブルやインデックス スキーマに変更を加える必要があるため、修正作業が非常に難しいか、修正作業を行うことができない場合もあります。たとえば、GUID などとは異なり、インデックス キーを挿入ポイントがランダムにならないものに変更したりする必要があります。

fig01.gif

図 1 データベース ページの構造 (画像をクリックすると拡大表示されます)

一般的には、挿入、更新、削除など、ページ上に空き領域を残す可能性があるデータ変更操作の結果として、内部断片化が発生します。適切に管理されていない FILL FACTOR も、断片化の原因になる可能性があります (詳細については、SQL Server オンライン ブックを参照してください)。テーブルやインデックスのスキーマとアプリケーションの性質によっては、この空き領域は一度作成されると決して再利用されないため、使用できない領域がデータベース内で増加し続ける可能性があります。

たとえば、レコードの平均サイズが 400 バイトである 1 億行のテーブルがあるとします。アプリケーションのデータ変更パターンによって、各ページ上に平均 2,800 バイトの空き領域が残されました。テーブルに必要な領域の合計は約 59 GB です。この数字を計算するには、8 KB のページ 1 ページあたりのレコード数を求めた (8096 - 2800 / 400 = 13) 後、1 億を 13 で除算してページ数を求めます。無駄な領域が存在しなければ、1 ページあたり 20 個のレコードを格納できるため、必要な領域の合計サイズは 38 GB に抑えられます。これは大きな節約です。

したがって、データまたはインデックス ページ上に無駄な領域が存在すると、同じ量のデータを保持する場合でも、余分にページが必要になります。また、より多くのディスク領域が奪われるだけでなく、同じ量のデータを読み取るためにクエリによって発行される I/O の回数も増加することになります。さらに、これらの余分なページによって、データ キャッシュ内のより多くの領域が占有されるため、サーバーのメモリも余分に使用されます。

論理スキャンの断片化は、ページ分割と呼ばれる操作が原因です。これは、特定のインデックス ページに (インデックス キー定義に従って) レコードを挿入する必要があるときに、そのレコード データを格納するための領域をページ上に確保できない場合に発生します。このページは半分に分割され、約 50% のレコードが、新しく割り当てられたページに移動されます。通常、この新しいページは古いページに物理的に隣接していないため、"断片化している" と表現されます。エクステント スキャンの断片化も、概念的には似ています。テーブルまたはインデックス構造内の断片化は、SQL Server が効率的なスキャンを実行できるかどうかに影響します。これは、スキャンの対象がテーブルまたはインデックス構造全体の場合でも、クエリの WHERE 句によって対象が制限される (SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000 など) 場合でも同様です。

図 2 は、FILL FACTOR が 100% で断片化が発生していない、新しく作成されたページを示しています。各ページの領域全体が使用され、ページの物理的な順序と論理的な順序が一致しています。図 3 は、ランダムな挿入、更新、または削除の後に発生する可能性がある断片化を示しています。

fig02.gif

図 2 ページの領域使用率が 100% で断片化が発生していない、新しく作成されたインデックス ページ (画像をクリックすると拡大表示されます)

fig03.gif

図 3 ランダムな挿入、更新、削除の後に論理スキャンの内部断片化が発生したインデックス ページ (画像をクリックすると拡大表示されます)

断片化は、テーブルやインデックスのスキーマを変更することによって回避できる場合がありますが、前述のとおり、これは非常に困難であるか、不可能な場合があります。この回避策を選択できない場合は、インデックスの再構築や再編成などの方法を使用して、発生した断片化を解消できます。

インデックスを再構築すると、インデックスの新しいコピーが (可能な限り連続するように最適化された状態で) 作成され、断片化された古いコピーが削除されます。SQL Server では、インデックスの新しいコピーが作成されてから古いコピーが削除されるため、データ ファイル内にインデックスのサイズと同程度の空き領域が必要です。SQL Server 2000 では、インデックスの再構築は常にオフラインで実行されていましたが、SQL Server 2005 Enterprise Edition では、(いくつかの制限はありますが) オンラインでもインデックスを再構築できます。一方、再編成では、インプレース アルゴリズムを使用して最適化とインデックスの断片化解消が行われるため、必要な領域は 8 KB のみです。また、この処理は常にオンラインで実行されます。実際、私は SQL Server 2000 で、インデックスを再構築する代わりにオンラインで領域を効率よく使用できる方法として、インデックスの再編成コードを作成しました。

SQL Server 2005 では、ALTER INDEX … REBUILD コマンドを使用してインデックスを再構築し、ALTER INDEX … REORGANIZE コマンドを使用してインデックスを再編成します。SQL Server 2000 でこれらに相当するコマンドは、それぞれ DBCC DBREINDEX と DBCC INDEXDEFRAG です。

これら 2 つの手法の間には、生成されるトランザクション ログの量、データベース内の必要な空き領域、作業内容を失うことなく処理を中断できるかどうかなど、多くのトレードオフがあります。これらのトレードオフなどに関する説明が記載されたホワイト ペーパーが microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx で公開されています。このペーパーは SQL Server 2000 に基づいていますが、概念はそれ以降のバージョンにも適用できます。

管理者によっては、どのインデックスが断片化しているかや、断片化を解消することによってなんらかのメリットを得ることができるかどうかを検証せずに、(たとえばメンテナンス プランのオプションを使用して) 毎晩または毎週、インデックス全体の再構築や再編成を行う場合があります。最小限の労力でなんらかの対策を施す必要がある不本意な DBA の皆さんにとっては良い方法かもしれませんが、リソースが重視される、サイズの大きなデータベースやシステムにとっては、きわめて不適切な選択になる可能性があります。

より賢い方法は、DMV sys.dm_db_index_physical_stats (SQL Server 2000 の場合は DBCC SHOWCONTIG) を使用して、断片化されているインデックスを定期的に確認し、それらのインデックスに対処するかどうか、またどのように対処するかを決定することです。上記のホワイト ペーパーでは、より対象が絞られたこれらの選択肢を使用する方法も記載されています。また、この処理を行うサンプル コードについては、Books Online に記載されている SQL Server 2005 の DMV sys.dm_db_index_physical_stats に関するトピック (msdn.microsoft.com/library/ms188917) のサンプル D、または SQL Server 2000 以降の DBCC SHOWCONTIG に関するトピック (msdn.microsoft.com/library/aa258803) のサンプル E を参照してください。

どちらの方法を使用する場合でも、断片化が発生しているかどうかを定期的に確認し、それらを解消することを強くお勧めします。

クエリ プロセッサは SQL Server のコンポーネントで、どのようにクエリを実行するかを定義します。具体的には、使用するテーブルとインデックスや、目的の結果を得るためにそれらに対してどのような操作を実行するかなどを定義します。この定義はクエリ プランと呼ばれます。この定義を行ううえで最も重要な判断材料の 1 つは、テーブルやインデックス内で使用されている列のデータ値の分布を示す統計です。もちろん、有用な情報をクエリ プロセッサに提供するには、最新の正確な統計を使用する必要があります。これを怠った場合、パフォーマンスの低いクエリ プランが選択される可能性があります。

統計は、テーブルまたはインデックスのデータを読み取り、関連性のある列のデータ分布状況を特定することによって生成されます。統計は、特定の列に格納されているすべてのデータ値をスキャンして (完全スキャン) 構築することも、ユーザーが指定した割合のデータに基づいて (サンプル スキャン) 構築することもできます。列内の値が比較的均等に分布している場合は、サンプル スキャンを使用すれば十分です。サンプル スキャンの方が完全スキャンと比べて短時間で統計を作成および更新できます。

AUTO_CREATE_STATISTICS および AUTO_UPDATE_STATISTICS データベース オプションを有効にすると、統計を自動的に作成およびメンテナンスできます (図 4 参照)。これらのオプションは既定で有効になっていますが、まだデータベースを引き継いで間もない場合は、これらが有効になっているかどうかを確認することをお勧めします。統計の内容は古くなる可能性がありますが、特定の統計に対して UPDATE STATISTICS 操作を使用することによって、これらを手動で更新できます。また、sp_updatestats ストアド プロシージャを使用して、作成日時の古い統計をすべて更新することもできます (SQL Server 2000 では、sp_updatestats を使用すると、作成日時に関係なくすべての統計が更新されます)。

fig04.gif

図 4 SQL Server Management Studio を使用したデータベース設定の変更 (画像をクリックすると拡大表示されます)

定期的なメンテナンス プランの一環として統計を更新する場合、注意すべきことが 1 つあります。それは、UPDATE STATISTICS と sp_updatestats では以前に指定したサンプリング レベルが使用されます (指定したことがある場合) が、このレベルが完全スキャンではない可能性があることです。インデックスを再構築すると、自動的に完全スキャンが統計の更新に使用されます。インデックスの再構築後に統計を手動で更新すると、統計の正確さが低下する可能性があります。この状況は、インデックスの再構築時に生成された完全スキャンが、手動での更新に使用されたサンプル スキャンによって上書きされた場合に発生します。一方、インデックスを再編成しても統計が更新されることはありません。

また、すべてのインデックスを再構築する前後のある時点ですべての統計を更新するメンテナンス プランを使用した結果、知らず知らずのうちに正確さを欠いた統計を保持することもよくあります。単純にすべてのインデックスを頻繁に再構築する場合、統計もそれによって自動的に再構築されます。より複雑な方法を使用して断片化を解消する場合は、その作業に統計のメンテナンスも含めるようにしてください。以下にいくつかの推奨事項を示します。

  • インデックスを分析し、操作対象のインデックスと断片化の解消方法を決定します。
  • 再構築されていないすべてのインデックスの統計を更新します。
  • インデックスが作成されていないすべての列の統計を更新します。

統計の詳細については、ホワイト ペーパー「Microsoft® SQL Server 2005 のクエリ オプティマイザが使用する統計情報」(microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx) を参照してください。

破損の検出

これまでは、パフォーマンス関連のメンテナンスについて説明しました。ここではトピックを変えて、破損の検出と緩和策について説明します。

管理しているデータベースに、だれも気にかけていないまったく不要な情報が格納されていることはまず考えられません。では、データが破損していない状態、および障害が発生した場合に復旧可能な状態を維持するにはどうすればよいでしょうか。この記事では、完全な障害復旧および高可用性戦略を作成する方法については説明しませんが、手始めに簡単にできることをいくつか紹介します。

破損の原因のほとんどは "ハードウェア" です。なぜハードウェアを二重引用符で囲んだかと言えば、この場合は本来の意味ではなく、"SQL Server の基盤となる I/O サブシステム内の要素" を表しているからです。I/O サブシステムは、オペレーティング システム、ファイル システム ドライバ、デバイス ドライバ、RAID コントローラ、ケーブル、ネットワーク、実際のディスク ドライブなどから構成されます。このようなさまざまな場所で問題が発生する可能性があります (そして実際に発生します)。

最もよく問題が発生する状況の 1 つは、ディスク ドライブがデータベース ページを書き込んでいるときに停電が発生した場合です。電力が供給されなくなる前にドライバが書き込みを完了できなかった (または書き込み操作がキャッシュされたが、ドライバのキャッシュをフラッシュできるだけの補助電力が供給されなかった) 場合、ディスク上に不完全なページ イメージが残る可能性があります。この問題が発生する理由は、8 KB のデータベース ページが、実際には 512 バイトのディスク セクタを 16 個連結して構成されているためです。不完全な書き込みでは、新しいページを構成するいくつかのセクタが書き込まれ、以前のページ イメージを構成していたいくつかのセクタが元のまま残ります。これは破損ページと呼ばれます。この問題が発生しているかどうかを検出するにはどうすればよいでしょうか。

SQL Server では、この状況を検出するためのメカニズムが提供されます。このメカニズムでは、ページのセクタごとに数ビットが保存され、特定のパターンがそれぞれの領域に書き込まれます (この処理は、ページがディスクに書き込まれる直前に実行されます)。ページが再度読み取られたときにパターンが一致しない場合、SQL Server はページが "破損" していると判断し、エラーを生成します。

SQL Server 2005 以降では、ページ チェックサムというさらに包括的なメカニズムが提供されます。このメカニズムを使用すると、ページ上のあらゆる破損を検出できます。このメカニズムでは、破損ページを検出するメカニズムと同様、ページがディスクに書き込まれる直前に、ページ全体のチェックサムがページに書き込まれ、ページが再度読み取られるときに、このチェックサムがテストされます。ページ チェックサムを有効にした場合、ページ チェックサムによってページが保護される前に、ページをバッファ プールに書き込み、なんらかの変更を加えて、ディスクに再び書き込む必要があります。

したがって、破損の検出に関するベスト プラクティスは、SQL Server 2005 以降ではページ チェックサムを有効にし、SQL Server 2000 では破損ページの検出を有効にすることです。ページ チェックサムを有効にするには、次のコマンドを使用します。

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

SQL Server 2000 で破損ページの検出を有効にするには、次のコマンドを使用します。

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

これらのメカニズムを使用すると、ページが破損していることを検出できますが、ページを読み取るとき以外は破損を検出できません。簡単にすべての割り当て済みページの読み取りを強制するにはどうすればよいでしょうか。最も良い方法 (かつ他のどのような種類の破損も検出できる方法) は、DBCC CHECKDB コマンドを使用することです。指定されたオプションにかかわらず、このコマンドでは常にデータベースのすべてのページが読み取られるため、ページ チェックサムや破損ページが確認されます。また、通知を設定して、ユーザーがクエリを実行したときに破損に関する問題が発生した場合、そのことを確認できるようにするとよいでしょう。重要度 24 エラーの通知を使用することによって、これまでに説明したすべての問題の通知を受け取ることができます (図 5 参照)。

fig05.gif

図 5 重要度 24 エラーが発生したときに通知を受け取るよう設定する (画像をクリックすると拡大表示されます)

したがって、もう 1 つのベスト プラクティスは、データベースに対して DBCC CHECKDB を定期的に実行し、データベースの整合性を確認することです。このコマンドのバリエーションは豊富で、実行頻度についても多くの質問を受けます。残念ながら、このことに関する説明が記載されたホワイト ペーパーは提供されていません。ただし、私が SQL Server 2005 用に作成したコードで最も重要なコマンドは DBCC CHECKDB で、これについてはかなり詳しくブログに書きました。私のブログ (sqlskills.com/blogs/paul) の「CHECKDB の詳細」カテゴリには、整合性チェック、ベスト プラクティス、使用方法などに関する詳細な説明が記載された記事が多数含まれています。不本意な DBA の皆さんは、データベースの完全バックアップを作成するたびに DBCC CHECKDB を実行することを大まかな目安にしてください (詳細については後述します)。次のコマンドを実行することをお勧めします。

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

このコマンドによってデータが出力された場合、DBCC によってデータベース内の破損が検出されたことになります。ここで問題となるのは、DBCC CHECKDB によって破損が検出された場合、どのようにしてその破損に対処すべきかということです。そこで活躍するのがバックアップです。

破損などの障害が発生した場合、最も効果的な復旧方法は、バックアップからデータベースを復元することです。それには、まずバックアップが保管されていて、バックアップ自体が破損していないことが必要です。バックアップを保有していない場合に、大きく破損したデータベースを再び実行するにはどうすればよいかを質問されることが非常によくあります。答えは単純で、それは不可能です。このようなデータベースを実行すると、なんらかのデータ消失が発生し、ビジネス ロジックとリレーショナル データの整合性に大きな影響を与える可能性があります。

このため、必ず定期的にバックアップを作成するようにしてください。この記事では、バックアップと復元を使用する方法の詳細については取り上げませんが、ここでバックアップ戦略を確立する方法について簡単に説明します。

まず、定期的にデータベースの完全バックアップを作成します。これによって、復元ポイントを確保し、後からデータベースを復元できるようになります。データベースの完全バックアップを作成するには、BACKUP DATABASE コマンドを使用します。例については、SQL Server オンライン ブックを参照してください。さらに保護を強化するには、WITH CHECKSUM オプションを使用します。このオプションは、読み取るページにページ チェックサムが含まれている場合、それらのチェックサムを検証し、バックアップ全体のチェックサムを計算します。実行頻度は、ビジネス上どれくらいのデータや作業の消失が許容されるかに応じて設定します。たとえば、1 日に 1 回データベースの完全バックアップを作成すると、障害が発生した場合、1 日分のデータが失われます。データベースの完全バックアップのみを使用する場合は、単純復旧モデル (通称、復旧モード) を使用すると、トランザクション ログの拡張を管理する作業の煩雑さを回避できます。

2 つ目に、いずれかのバックアップが破損した場合に備えて、常に数日分のバックアップを維持しておきます。数日分のバックアップでも、まったくバックアップを作成しないよりは役立ちます。また、RESTORE WITH VERIFYONLY コマンドを使用して、バックアップの整合性を検証します (これについても SQL Server オンライン ブックを参照してください)。バックアップの作成時に WITH CHECKSUM オプションを使用した場合、この検証コマンドを実行すると、バックアップのチェックサムが現在も有効であるかどうかが確認されるだけでなく、バックアップ内のページに含まれているチェックサムもすべて再確認されます。

3 つ目に、1 日 1 回データベースの完全バックアップを作成するだけでは、データや作業の消失量がビジネスに支障のない範囲を超えてしまう場合、データベースの差分バックアップを検討してください。データベースの差分バックアップはデータベースの完全バックアップに基づいたバックアップで、最後にデータベースの完全バックアップを作成してから発生したすべての変更に関する記録が含まれます (差分バックアップは増分バックアップとよく間違えられますが、実際は異なります)。戦略の例としては、1 日に 1 回データベースの完全バックアップを作成し、4 時間ごとにデータベースの差分バックアップを作成することが考えられます。差分バックアップによって、追加の復旧ポイントを確保できます。データベースの完全バックアップと差分バックアップのみを使用する場合も、単純復旧モデルを使用することをお勧めします。

最後に、最大限の復旧性を実現するには、ログ バックアップを使用します。このバックアップは、完全 (または一括ログ) 復旧モデルでのみ使用でき、前回のログ バックアップ以降に生成されたすべてのログ レコードをバックアップします。定期的にデータベースの完全バックアップ (および場合によっては差分バックアップ) と共にログ バックアップを作成することによって、最新の復旧ポイントも含め、非常に多くの復旧ポイントを確保できます。このトレードオフは、ログ バックアップを作成することによってトランザクション ログが "解放" されるまで、ログのサイズが増加し続けることです。この場合の戦略例としては、データベースの完全バックアップを 1 日に 1 回作成し、データベースの差分バックアップを 4 時間ごと、ログ バックアップを 30 分ごとにそれぞれ作成することが考えられます。

バックアップ戦略を決定および構成する作業は複雑になる可能性があります。このため、少なくとも定期的にデータベースの完全バックアップを作成し、1 つ以上の復旧ポイントを確保するようにしてください。

ご存知のとおり、データベースの状態を良好に保ち、可用性を確保するには、いくつかの作業が欠かせません。不本意な DBA の皆さんを対象にした、データベースの最終チェック リストを次に示します。

  • トランザクション ログ ファイルの過剰な断片化を解消する。
  • 自動拡張を正しく設定する。
  • スケジュールされた圧縮操作をすべて無効にする。
  • ファイルの瞬時初期化を有効にする。
  • 定期的にインデックスの断片化を検出してそれらを解消する。
  • AUTO_CREATE_STATISTICS と AUTO_UPDATE_STATISTICS を有効にし、定期的に統計を更新する。
  • ページ チェックサムを有効にする (SQL Server 2000 の場合は、少なくとも破損ページの検出を有効にする)。
  • 定期的に DBCC CHECKDB を実行する。
  • 定期的にデータベースの完全バックアップ、差分バックアップ、およびログ バックアップを作成して復旧ポイントを確保する。

この記事では T-SQL コマンドを使用しましたが、Management Studio からもさまざまな操作を行うことができます。この記事が効果的なデータベース メンテナンスの実施に役立つ指針となることを願っています。ご意見やご質問は paul@sqlskills.com (英語のみ) までお寄せください。

Paul S. Randal は SQLskills.com の代表取締役であり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。Paul は障害復旧、高可用性、およびデータベース メンテナンスの専門家です。彼のブログは SQLskills.com/blogs/paul で公開されています。

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved. 許可なしに一部または全体を複製することは禁止されています。