SQL に関する Q&A
分散トランザクション、パフォーマンス カウンター、SQL Server のバックアップなど
Paul S. Randal
Q. 多数の分散トランザクションを使用していますが、現在、重要なデータベースの 1 つで高可用性を実現する手段としてデータベース ミラーリングの調査を行っています。テスト中に、ミラー データベースへのフェールオーバーを試行した後に、分散トランザクションがエラーになる場合があることが判明しました。何がどうなっているのか、教えていただけませんか。
A. この現象については、分散トランザクションを使用する場合の制限事項としてドキュメントが公開されています。この制限事項は、データベース ミラーリングまたはログ配布を使用すると発生します (基本的に、この制限事項は、Windows サーバーの名前がフェールオーバー後に変わる場合に適用されます)。
Microsoft 分散トランザクション コーディネーター (MSDTC) トランザクションを使用すると、ローカル トランザクション コーディネーターでは、実行中のサーバーを識別するリソース ID を保持します。ミラーリングへのフェールオーバーが行われると、プリンシパル データベースは、別のサーバー (ミラーリング パートナー) でホストされることになり、トランザクション コーディネーターのリソース ID が変わります。
分散トランザクションがアクティブな場合に、ミラーリング パートナーのトランザクション コーディネーターでは、トランザクションのステータスを確認しようとしますが、適切なリソース ID を保持していないので確認できません。MSDTC では、当初、分散トランザクションに関与していなかったミラーリング パートナーのトランザクション コーディネーターが認識されません。このような場合には、分散トランザクションを終了する必要があります (これが、ご質問いただいた現象です)。
データベースをまたぐトランザクション (複数のデータベースを更新する単純なトランザクション) でも、同様の問題が発生することがあります。このようなトランザクションに関与しているデータベースにプリンシパルとミラーが含まれている場合、複数のデータベースをまたぐトランザクションが両方のデータベースに対してトランザクションをコミットすることがあります。ミラーリングへのフェールオーバーを強制的に行うと (プリンシパル データベースとミラー データベースが同期されていない状態で、データの損失が許容される手動によるフェールオーバーが実行された場合)、ミラー データベースでコミットされたトランザクションの結果は失われ、データベース間のトランザクションの整合性が失われるおそれがあります。
これは、ミラー データベースが同期されておらず、コミットされたデータベースをまたぐトランザクションのログ レコードがミラー データベースに転送されていない場合に発生します (詳細については、私が執筆した 2009 年 6 月のコラムを参照してください)。フェールオーバーを強制的に行うと、トランザクションが新しいプリンシパル データベースに存在しなくなるため、データベースをまたぐトランザクションの整合性が失われます。
Q. 最近、データベースのストレージに関する問題を特定するために、いくつかのパフォーマンス カウンターを監視していました。その際、非常に奇妙な現象が発生しました。データベースでは何の処理も行われていないのに、データベース ファイルに対して書き込みが行われていました。この現象はデータ ファイルとログ ファイルの両方で見られました。SQL Server への接続が確立されていないことも確認しましたが、このような書き込みは依然として行われていました。SQL Server への接続が確立されていないときに、I/O 処理が発生するのはどうしてでしょうか。
A. SQL Server には、バックグラウンド タスクと呼ばれる状態を監視するために必要な操作が多数あります。このような操作がシステムで行われているために、I/O 処理が発生しています。次に、この現象の原因として考えられる操作の概要を示します。
非実体クリーンアップ: 削除操作では、操作がキャンセルされた場合のパフォーマンスを最適化するために、削除されたレコードに対して削除済みというマーキングを付けるだけで、そのレコードで使用している領域のデータを物理的には消去しません。削除操作がコミットされたら、削除されたレコードをデータベースから実際に削除する処理を実行する必要があります。この処理を行うのが、非実体クリーンアップ タスクです。詳細については、私のブログ記事 (英語) を参照してください。この記事では、非実体クリーンアップ タスクが実行されているかどうかを確認する方法を説明しています。
自動圧縮: このタスクを有効にすると、データベースの空き領域を自動的に削除できます。このタスクでは、データ ファイルの末尾にあるページをファイルの先頭に移動し、空き領域をファイルの末尾に集めて、ファイルを切り詰めます。このタスクは有効にできますが、有効にすると、インデックスの断片化の問題が発生し (結果として、パフォーマンスの問題を招き)、多くのリソースを消費するため、有効にしないことをお勧めします。通常、自動圧縮はデータベースで有効になっているので、ファイルは圧縮と拡張のサイクルに取り込まれます。この処理では、多くのリソースが無駄に消費されます (この処理を行って得られるメリットはありません)。次のクエリを使用すると、すべてのデータベースのステータスを確認できます。
SELECT name, is_auto_shrink_on FROM sys.databases;
遅延削除: このタスクでは、テーブルとインデックスを削除または切り詰めるのに必要な処理を行います (新しいインデックスを構築すると、古いインデックスが削除されるので、インデックスの削除は、インデックスの再構築によって行えます)。小規模なテーブルとインデックスについては、直ちに割り当てが解除されます。大規模なテーブルとインデックスの割り当てについては、バックグラウンド タスクによって、何度かに分けて解除されます。これは、メモリ不足を発生させることなく、必要なロックを取得できるようにするための措置です。このタスクの監視には、さまざまな遅延削除のパフォーマンス カウンターを使用できます。詳細については、オンライン ブックを参照してください。
Lazy writes: このタスクでは、メモリ内キャッシュ (バッファ プール) から古いページを削除します。サーバーのメモリ使用率が高い場合、変更内容が含まれているページも、メモリ内キャッシュから削除しなければならないことがあります。この場合、変更されたページは、メモリから削除する前に、ディスクに書き込む必要があります。このタスクは、Lazy writes/sec パフォーマンス カウンターを使用して監視できます。詳細については、オンライン ブックを参照してください。
上記すべてのタスクでは、データベースに変更を加える可能性があります。これらのタスクでは、トランザクションを使用して変更を加え、トランザクションがコミットされたときには、トランザクションで生成されたトランザクション ログ レコードをディスクに格納されているデータベースのログ部分に書き込む必要があります。データベースに変更が加えられるたびに、チェックポイントを発生させて、変更されたデータ ファイル ページをディスクにフラッシュする必要があります。詳細については、私が執筆した TechNet Magazine 2009 年 2 月号の記事「SQL Server のログ記録と復旧について」を参照してください。
ご覧のとおり、SQL Server へのアクティブな接続がないときでも、プロセスが停止されているとは限らず、1 つまたは複数のバックグラウンド タスクが実行されていることがあります。データベースのすべての処理が完了した後にも、しばらく I/O 処理が継続して発生する場合は、スケジュールされたジョブが実行されていないかどうかを確認することをお勧めします。
Q. 私は不本意な DBA ですが、必要な情報を得るために、いくつかのことを試しています。前任の DBA は、1 つのファイルにバックアップを格納するジョブを設定していましたが、このデータを復元する方法がわかりません。ファイルに格納されているバックアップの内容を確認する方法はありませんか。それから、これらのファイルを適切に復元する方法を教えてください。
A. 1 つのファイルに複数のバックアップを追加することは可能ですが、通常は、意味のある名前を付けた個別のファイルに各バックアップを格納します (一般的に、ファイル名には日付とタイムスタンプの組み合わせを使用します)。このようにすると、ファイルの内容がわからないという問題を回避して、次のようなタスクを簡単に実行できるので便利です。
- 各バックアップが個別のファイルに格納されていれば、非常時に備えてバックアップを簡単にコピーできます。すべてのバックアップが 1 つのファイルに格納されていると、最新のバックアップをコピーするのに、バックアップ ファイル全体をコピーする必要があります。
- すべてのバックアップが 1 つのファイルに格納されていると、古いバックアップを削除できません。
- 各バックアップを個別のファイルに格納していれば、間違って既存のバックアップを上書きする可能性が低くなります。
ですが、ご質問のケースでは、複数のバックアップが 1 つのファイルに格納されているので、上記の情報は役に立たないと思います。ですが、バックアップは、手動または SQL Server Management Studio (SSMS) を使用して復元できます。
SSMS を使用して、ファイルを参照する新しいバックアップ デバイスを作成し、ファイルに格納されているバックアップを確認できます。参照を作成したら、このバックアップ デバイスに格納されているバックアップの詳細を確認できます。または、RESTORE HEADERONLY コマンドを使用できます。どちらの場合も、バックアップ デバイスを検証して、ファイルに格納されているバックアップを説明する 1 行のデータが出力されます。SSMS では、わかりやすい名前でバックアップの種類を識別しますが、適切な構文を使用して、各バックアップの種類を特定する必要があります。この情報は、適切な RESTORE コマンドを使用して、バックアップを復元するのに必要です。詳細については、SQL Server 2008 オンライン ブックを参照してください。
復元するバックアップも特定する必要があります。RESTORE HEADERONLY コマンドの出力に含まれる列名は、バックアップの復元に使用するオプションと一致しないので、少し厄介です。ファイルに格納されているバックアップには 1 から順に番号が付けられています (一番古いバックアップの番号が 1 になります)。この番号は、Position という名前の列に格納されています。必要なバックアップを復元するには、WITH FILE = <番号> という形式を私用して、RESTORE コマンドで番号を指定する必要があります。次に例を示します。
RESTORE DATABASE test FROM DISK = 'C:\SQLskills\test.bak' WITH FILE = 1, NORECOVERY;RESTORE LOG test FROM DISK = 'C:\SQLskills\test.bak'WITH FILE = 2, NORECOVERY;
これはほんの一例に過ぎません。データベースのバックアップを使用して復元シーケンスを開始し、差分データベースまたはトランザクション ログのバックアップ (あるいは、その両方) を復元する必要があります。今月のコラムでは、これ以上の詳細を説明することはできませんが、復元シーケンスと必要な RESTORE オプションの詳細については、私が執筆した 2009 年 11 月号の記事「バックアップを使用して障害から復旧する」を参照してください。
SSMS を使用する場合は、データベースの復元ウィザードでバックアップ ファイルを指定すると、ファイルに格納されているバックアップの内容が自動的に表示され、必要なバックアップを選択できます。この例を図 1 に示します。
.jpg)
図 1 SSMS のデータベースの復元ウィザードを使用して、ファイルに格納されている複数のバックアップを確認する
どちらのオプションを使用する場合も、実際に障害から回復するために運用環境で復元を実行する前には、別の場所で復元の練習を行うことが重要です。「正常に復元できなければバックアップではない」というのがバックアップに関する私の基本方針です。
Q. 数週間おきに、開発環境にコピーしなければならない、比較的サイズの大きなデータベースがあります。最近、データの増加に備えて、このデータベースのサイズが拡張されました。その結果、このデータベースはサイズが大きすぎて、開発環境に復元できなくなり困っています。データベースを復元するときに、サイズを縮小する方法はありますか。
A. これは非常に一般的な質問ですが、残念ながら、良い回答を提供できない質問でもあります。
データベースのバックアップでは、データベースをどのようにも変更することはありません。バックアップでは、データベースの使用されている部分を読み取り、そのデータとトランザクション ログの一部をバックアップに含めているだけです (この理由とデータの量については、私のブログ記事 (英語) を参照してください)。また、バックアップを復元する際には、ファイルを作成し、バックアップに含まれているものを書き出し、データベースで復元処理を実行しているだけです。基本的に、復元時には、データベースに格納されていたものが提供されます。復元時にデータベースのサイズを縮小したり、インデックスの断片化に対応したり、統計を更新したりするオプションはありません。
必要な処理を行うにはどうすれば良いかというと、使用する方法は状況によって異なりますが、3 つの選択肢があります。
1 つ目の選択肢は、運用データベースで圧縮操作を実行して、空き領域を再利用することです。この操作を実行すると、運用環境のデータベースと復元したデータベースのコピーは同じ状態になり、領域を無駄に使うこともなくなります。ただし、潜在的にコストが高くなることがあります。というのも、運用環境のデータベースを再度拡張する必要があり、圧縮操作では、CPU、I/O、およびトランザクション ログの観点で非常にコストが高くなることがあり、インデックスの断片化が生じることがあるからです。 インデックスの断片化は解消する必要があるため、より多くのリソースが必要になります。これは、ご質問のケースにはお勧めの選択肢ではありません (データ ファイルを圧縮することについての危険性に関する詳細については、私のブログ記事 (英語) を参照してください)。(DBCC SHRINKFILE WITH TRUNCATEONLY コマンドを使用して) ファイルの末尾にある空き領域だけを削除することもできますが、データベースのサイズは、それほど小さくならない可能性があります。
2 つ目は、開発環境に 1 回だけ運用環境のデータベースを復元する必要がある場合に適切な選択肢ですが、データベース全体を復元してから、圧縮して領域を再利用するのに十分な空き領域を確保することです。その後、圧縮操作によって発生した断片化に対応するかどうかを判断する必要があります。
パフォーマンス テストやレポート作成にクエリを使用する場合、断片化は、クエリのパフォーマンスに大きく影響します。このようなクエリを実行しない場合は、断片化を解消する必要はありません。断片化に対応する際には、(ALTER INDEX … REBUILD コマンドを使用して) インデックスを再構築することはできません。インデックスの再構築には、追加の空き領域が必要になり、データベースのサイズが再度大きくなるので、(ALTER INDEX … REORGANIZE コマンドを使用して) インデックスを再編成する必要があります。
断片化を解消する場合は、データベースを単純復旧モデルに切り替えて、再編成により生成されるトランザクション ログ レコードによって、トランザクション ログのサイズが大きくならないように注意します。完全復旧モデルのまま、断片化の解消を行うと、ログのバックアップを作成しない限り、ログが増大し続けます。これは、開発環境にデータベースをコピーする際には、望ましくない状態です。
3 つ目は、開発環境に運用環境のデータベースを複数回復元する必要がある場合に適切な選択肢ですが、2 つ目の選択肢の手順を繰り返し行いたくない場合に使用します。この場合は、2 つ目の選択肢の手順を実行した後に、圧縮 (および断片化を解消) したデータベースのバックアップを作成することをお勧めします。
2 つ目のバックアップは、可能な限りサイズを小さくした運用環境のデータベースを複数回復元するのに使用できます。
要約すると、多くの空き領域がある運用環境のデータベースを、初回復元時に必要な空き領域がない開発環境にコピーする簡単な方法はないということになります。
今月のコラムの技術校閲者を務めてくれた SQLskills.com の Kimberly L. Tripp に感謝します。
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は Twitter.com/PaulRandal (英語) でフォローできます。