SQL に関する Q&A:バックアップとセットアップ
SQL Server は強力なプラットフォームですが、トランザクション ログの設定などの構成に関する問題を検討する際には洗練された技術が必要になります。
Paul S. Randal
XXXL トランザクション ログ
Q. 弊社の製品では、データの格納に SQL Server を使用しています。ときどき製品の新しいバージョンをリリースしていますが、新しいバージョンにはデータベースに対して実行が必要なアップグレード スクリプトが含まれていることもあります。サンプルのテスト データベースで最新のアップグレード スクリプトのテストを実施したところ、トランザクション ログ ファイルのサイズが増加して、40 GB を超えました。ログ ファイルが、このように大きくなることを回避したいと思っています。この問題に対処する選択肢には、どのようなものがありますか。障害回復のために完全復旧モデルを必ず使用する必要があることを考慮してお答えいただけると助かります。
A. まず、顧客のサンプル データでテストを実施しているのは、すばらしいことだと思います。このようなスクリプトのテストを小規模なデータで実施しただけで、顧客に製品をリリースし、その結果、運用時に顧客がトラブルに見舞われるというケースを何度となく目にしています。あなたが製品のユーザーだと仮定して、ご質問にお答えしましょう。そうすれば、顧客の状況に合わせて、情報を調整できると思います。
完全復旧モデルを使用する必要があるとのことですが、完全復旧モデルを採用しているということは、トランザクション ログのバックアップは作成されているので、トランザクション ログのサイズが制御できないほど大きくなるという一般的な問題はないと思います。トランザクションがコミットされた後に、トランザクション ログをクリアするには、トランザクション ログのバックアップ作成が必須条件となるので、これは良いことです (詳細については、トランザクション ログのしくみとさまざまな回復モデルがトランザクション ログの動作に与える影響について説明している technet.microsoft.com/magazine/2009.02.logging を参照してください)。
それでもやはり、トランザクション ログのバックアップを作成する頻度は、トランザクション ログをどれだけ早い時点でクリアできて、サイズの拡張を抑えられるかに影響します。たとえば、定期的に実行しているバックアップ ジョブで、30 分ごとにトランザクション ログのバックアップを作成している場合、トランザクション ログ ファイルには、30 分間に生成されるトランザクション ログ データの最大量を保持するのに十分なサイズが割り当てられている必要があります。十分なサイズが割り当てられていない場合、ログ ファイルのサイズは大きくなります。
アップグレード スクリプトの実行に 60 分かかり、30 分ごとに 20 GB のトランザクション ログが生成される場合は、トランザクション ログのサイズは 20 GB にする必要があります。ですが、これでも、まだ大きすぎます。ですから、アップグレード スクリプトの実行中には、もっと頻繁にトランザクション ログのバックアップを作成する必要があります。トランザクション ログのバックアップの作成頻度を上げると、トランザクション ログを頻繁にクリアして、ファイルのサイズが大きくなりすぎることを回避できます。クライアントの環境で、同じような状況に遭遇したことがあります。大きなデータベースに対して同じようなスクリプトを数時間かけて実行しましたが、そのときは、1 分ごとにトランザクション ログのバックアップを作成する必要がありました。
このように追加で作成するトランザクション ログのバックアップは、障害回復時に必要となるログ バックアップ チェーンの構成要素であることに注意してください。また、ログ ファイルには意味がある名前を付けて、削除しないようにしてください。
他にも考慮しなければならないことがあります。設計したアップグレード プロセスで発生する最も大きなトランザクションは何ですか。トランザクション ログは、ログに記録されている情報がコミットされたトランザクションのものである場合にのみクリアできます (この説明は少し乱暴なので、詳細については、先ほど言及した TechNet マガジンの記事を参照してください)。つまり長時間実行されるトランザクションがあると、トランザクション ログで生成されたトランザクション ログをバックアップしているにもかかわらず、ログをクリアすることができません。
アップグレード スクリプトに、ログ ファイルの領域として 15 GB 必要なトランザクションがある場合、トランザクション ログ ファイルのサイズは、そのトランザクションがコミットされるまでのトランザクション ログ データを保持するために最低でも 15 GB にする必要があります。この場合、どれだけ頻繁にトランザクション ログのバックアップを作成しても、トランザクション ログをクリアすることはできません。この場合の唯一の対応策は、大きなトランザクションを小さな複数のトランザクションに分割することです (ただし、このような処理が可能な場合に限ります)。
アップグレード スクリプトの実行に必要なトランザクション ログのサイズは、トランザクション ログのバックアップを作成する頻度と設計したトランザクションのうち一番大きなトランザクションのサイズによって決まるということを覚えておいてください。
構成に関する難しい問題
Q. あるデータベース サーバーで使用する直接取り付け記憶域の準備をしているところですが、使用可能なすべての選択肢を理解して、適切に構成したいと考えています。SQL Server に関して、把握しておくべき構成の設定について説明していただけますか。
A. ストレージの準備をするときには、さまざまな設定と構成のオプションがあります。ですから、私は、ストレージ管理者の知恵を借りるようにしています。ですが、SQL Server の管理者が把握して適切に設定すべきオプションがあるのも事実です。
まずは、基盤となる RAID レベルです。RAID のレベルによって、パフォーマンスと冗長性に関するトレードオフが異なります。たとえば、ある程度の冗長性を提供する最も安価な RAID 構成は RAID-5 ですが、この構成では 1 つのドライブの障害にしか対応できません (ただし、RAID-6 を使用したり、ホットスペア ドライブを構成している場合は除きます)。また、配列のドライブ数によっては、大量の書き込みを伴うワークロードが発生するとパフォーマンスに影響することがあります。
最も高い冗長性が提供されるのは、RAID-10 ですが、コストが高くなります。配列の容量は、最大でも構成ドライブの全容量の半分になります。さまざまな RAID レベルの詳細については、TechNet で公開しているホワイト ペーパー「物理データベースのストレージ デザイン (英語)」の Appendix A を参照してください。
RAID のストライプ サイズ、NTFS アロケーション ユニット サイズ (クラスター サイズ)、およびディスク パーティションのアラインメントについても考慮する必要があります。これらは適切に設定しないと、パフォーマンスに大きな悪影響を及ぼすことがあります。最も重要なのは、Windows Server 2003 で作成されたディスク ボリュームのディスク パーティションのアラインメントです。既定では 31.5 KB の割り当てを使用しますが、一般的な RAID ストライプ サイズである 64 KB (または 64 の倍数) と適合しません。そのため、各 I/O 処理では、実質 2 つの RAID ストライプに対して読み取りまたは書き込み処理を行う必要があります。このような処理によって、パフォーマンスが大幅に低下するのは明らかです。
Windows Server 2008 では、既定で 1 MB のアラインメントを使用します。Windows Server 2003 で作成され、Windows Server 2008 でホストするためにアップグレードされたボリュームのアラインメントは変更されないので、このようなボリュームのパフォーマンスにも影響が生じることがあります。この問題を修正するには、ボリュームの再フォーマットが必要になりますが、多くの場合、この処理に見合うパフォーマンスの向上が見られます。
このコラムでは、これらについて詳しく説明することはできませんが、私のブログ記事「Are your disk partition offsets, RAID stripe sizes and NTFS allocation units set correctly? (ディスク パーティション オフセット、RAID ストライプ サイズ、および NTFS アロケーション ユニットは正しく設定されていますか、英語)」では、この詳細を説明し、さらに情報が必要な方のためにリンクを掲載しています。詳細については、そちらを参照してください。
新しいストレージを準備する際には、運用環境で使用する前に、ストレス テストとパフォーマンス テストを実施することをお勧めします。ストレス テストを実施すると、ダウンタイムやデータの損失につながる構成上の問題を検出できます。パフォーマンス テストを実施すると、新しいストレージでワークロードに必要な I/O の容量が提供されることを確認できます。マイクロソフトでは、パフォーマンス テストに役立つ無料のツールを提供しています。このツールの詳細については、「Pre-Deployment I/O Best Practices (展開前の I/O ベスト プラクティス、英語)」を参照してください。
ミラーリング
Q. データベース ミラーリングを設定するときのミラーリング監視サーバーの働きについて混乱しています。ミラーリング監視サーバーは、どの程度、強力なものにする必要がありますか。それは、フェールオーバーするデータベースの数によって決まるものですか。それともミラーリング監視サーバーを配置するデータセンターによって異なりますか。ミラー化されたデータベースの可用性を最大限に高められるようにしたいと思っているのですが、ご教授いただけますでしょうか。
A. ミラーリング監視サーバーの役割は、データベース ミラーリング システムにおいて最も誤解されやすい側面の 1 つです。同期データベース ミラーリング構成のミラーリング監視サーバーは、プリンシパル サーバーが使用できなくなったときに自動フェールオーバーを円滑に進めることのみを目的としたサーバーです。
プリンシパル サーバーでは、ミラー サーバーにトランザクション ログのレコードを断続的に送信しますが、ミラーリング監視サーバーに送信することはありません。プリンシパル サーバー、ミラー サーバー、およびミラーリング サーバーでは、自動フェールオーバー検出メカニズムの一環として、1 秒ごとに相互に ping コマンドを実行しています。ミラー サーバーがなんらかの理由でプリンシパル サーバーと通信できないことが判明しても、ミラーリング監視サーバーがプリンシパル サーバーと通信できないことに同意しない限り、ミラー サーバーでは自動フェールオーバーを開始できません。2 台のサーバーが同意すると、クォーラムが形成され、ミラー サーバーでは自動フェールオーバーが開始されます。ミラーリング監視サーバーが存在しない場合、クォーラムを形成できないので、自動フェールオーバーを開始することもできません。
そのため、ミラーリング監視サーバーは、クォーラムを形成するという目的のためにのみ存在しています。フェールオーバーを開始したり、ミラー化されたデータベースのホスティングに関与したりすることはありません。通常、クォーラムはプリンシパル サーバーとミラー サーバーの間に存在します。
ミラーリング監視サーバーでは、このように負荷の高い処理を行うわけではないので、強力なサーバーを用意する必要はありません。無償の SQL Server Express Edition を含む任意のエディションの SQL Server をホストできます。また、1 つの SQL Server インスタンスがミラーリング監視サーバーとして機能できるデータベース ミラーリング セッションの数にも制限はありません。
ミラーリング監視サーバーは、プリンシパル サーバーまたはミラー サーバーと異なるデータセンターに配置するのが最適です。ただし、ほとんどの企業は 3 つものデータセンターを保有していないので、ミラー サーバーとプリンシパル サーバーのどちらと同じデータセンターに配置するかということが問題になります。
使用可能なデータセンターが 2 つしかない場合、ミラーリング監視サーバーは、必ずプリンシパル サーバーと同じデータセンターに配置します。その理由は、クォーラムの形成に関係しています。ミラーリング監視サーバーとミラー サーバーが同じデータセンターに配置されている場合、プリンシパル サーバーへのネットワーク リンクが切断されると、ミラーリング監視サーバーとミラー サーバーの間でクォーラムが形成され、ミラー サーバーによってフェールオーバーが開始されます。
プリンシパル サーバーでは、問題が発生していないかもしれないにもかかわらず、クォーラムを失うと、プリンシパル データベースがオフラインになります。この場合、プリンシパル サーバーでは、ミラー サーバーがフェールオーバーを実行することを想定します。このような状況を回避するには、プリンシパル サーバーとミラーリング監視サーバーを同じデータセンターに配置します。このように配置することで、ネットワーク障害が発生したときに、プリンシパル サーバーがミラーリング監視サーバーとのクォーラムを維持できるようになり、プリンシパル データベースは利用可能な状態を維持できます。
ミラーリング監視サーバーを配置するかどうかは任意ですが、ミラーリング監視サーバーが配置されていないと自動フェールオーバーを実現することができないので、ミラー化されたデータベースの可用性を最大限に高めることはできません。データベース ミラーリングは 1 日おきに同じように実行されます。ミラーリング監視サーバーが、なんらかの理由で使用できない状態になっても、自動フェールオーバーが実行できなくなる点を除いて、ミラー化の機能に影響はありません。
1 つのデータベース ミラーリング セッションに 2 台のミラーリング監視データベースを配置することもできます。ミラーリング監視サーバーの役割の冗長性を高める唯一の方法は、フェールオーバー クラスターにミラーリング監視サーバーの役割をインストールした SQL Server インスタンスを配置することです。データベース ミラーリングの構成に関する詳細については、TechNet ホワイト ペーパー「SQL Server 2005 データベース ミラーリング」を参照してください。
.jpg)
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 (英語) でフォローできます。