SQL に関する Q&Aバックアップの圧縮、データベース ミラーリングを使用したクライアントのリダイレクトなど
Paul S. Randal
Q サーバーのほとんどを SQL Server 2008 にアップグレードする予定なのですが、運用環境に適用することを期待している機能の 1 つに、バックアップの圧縮があります。それぞれのサーバー上のすべてのデータベースで、この機能を既定で有効にできることは知っていますが、そうしない方が好ましいかもしれないということも聞きました。失うものは何もなさそうなのに、この機能を既定で有効にするのが好ましくない理由がわかりません。私が聞いたことの根拠を説明してもらえますか。
A 私がずっと気に入って使っている回答になりますが、これは "ケース バイ ケース" です。この機能の背景についていくつかご説明しましょう。
考慮すべき重要なポイントは、バックアップの圧縮が有効になっている場合にデータベース バックアップごとに適用される圧縮率です。どのようなアルゴリズムで何を圧縮するとしても、圧縮率は圧縮される実際のデータによって決まります。
ランダムなデータ (たとえば、小さな整数値など) はあまり効率的に圧縮されません。そのため、ほとんどの場合は、データベース内のテーブルやインデックスの内容によって、実現できる圧縮率が決まります。
バックアップの圧縮による圧縮率が高くならない可能性がある例を次にいくつか示します。
- データベースで透過的なデータ暗号化が有効になっている場合は、圧縮されるデータがランダムで小さな値になるため、圧縮率が非常に低くなります。
- データベース内のほとんどのデータが列レベルで暗号化されている場合は、基本的に列レベルの暗号化によってデータがランダム化されるため、圧縮率は低くなります。
- データベース内のほとんどのテーブルでデータの圧縮が有効になっている場合は、既にほとんどが圧縮されているデータをさらに圧縮してもあまり効果がない場合が多いため、圧縮率は低くなります。
圧縮率が低い場合の問題点は、比率が低いことではなく、得るものが何もないのに圧縮アルゴリズムを実行するために CPU リソースが使用されることです。まとまったデータの圧縮が効率的に行われるかどうかに関係なく、常に圧縮と圧縮解除のアルゴリズムを実行するために CPU リソースが使用されます。
つまり、あるデータベースにバックアップの圧縮を使用することを決定する前に、バックアップでそれぞれのデータベースがどれほど効率的に圧縮されるかを常に確認しておく必要があります。そうしなければ、CPU リソースを無駄にしてしまう可能性があります。これが、あなたが聞いたことの根拠です。
まとめると、データベースの大部分でバックアップを圧縮するメリットがある場合は、バックアップの圧縮をサーバー レベルで有効にし、少数のバックアップ ジョブを WITH NO_COMPRESSION オプションを使用するように手動で変更するようにします。また、データベースの大部分でバックアップを圧縮してもメリットがない場合は、サーバー レベルでバックアップの圧縮を無効にし、少数のバックアップ ジョブを WITH COMPRESSION オプションを使用するように手動で変更するようにします。
Q 昨年、データベースをアップグレードしてデータベース ミラーリングを導入し、障害が発生した場合に、ミラーにフェールオーバーしてアプリケーションを続行できるようにしました。システム設計時にデータベースのフェールオーバーを試験的に実行したときは、すべてうまくいきました。先週、実際に障害が発生してデータベースのフェールオーバーが発生しましたが、アプリケーションのトランザクションがすべて停止し、アプリケーションからフェールオーバー サーバーに接続されませんでした。今後、フェールオーバー中にアプリケーションの接続が切断されないように SQL Server をセットアップして、トランザクションを続行できるようにするにはどうすればよいでしょうか。
A ここでは、回答を 2 つの部分に分けます。アプリケーションがフェールオーバーにどのくらい対処できるかと、データベース ミラーリングを使用してクライアントのリダイレクトを管理する方法についてです。
SQL Server で利用できるどの高可用性テクノロジを使用していても、フェールオーバーが発生すると、障害が発生したサーバーへのクライアント接続が切断され、実行中のすべてのトランザクションが失われます。(フェールオーバーが行われる場合であろうとなかろうと) 実行中のトランザクションをサーバー間で移行することはできません。高可用性テクノロジによっては、実行中のトランザクションがフェールオーバー サーバーにまったく存在しなくなるものもあれば、実行中のトランザクションとして存在しても、フェールオーバー サーバーでデータベースをオンラインにする処理の一環としてロール バックされるものもあります。
トランザクション ログ レコードがプリンシパル サーバーからミラー サーバーに継続的に配布されるデータベース ミラーリングでは、後者の場合がほとんどです。つまり、ミラー データベースを新しいプリンシパルとしてオンラインにする処理の一環として、実行中のすべてのトランザクションがロール バックされます。
そのため、別のサーバーにフェールオーバーする可能性があるサーバー上で実行されているアプリケーションは、次の 2 つのことを適切に行える必要があります。
- 切断されるサーバー接続を適切に処理し、短時間で再接続を試行できる必要があります。
- 中止されるトランザクションを適切に処理し、フェールオーバー サーバーとの接続が確立された後に、(おそらく中間層のトランザクション マネージャを使用して) トランザクションを再試行できる必要があります。
クライアントを具体的に変更しなくてもフェールオーバー後にクライアント接続をリダイレクトできる唯一の高可用性テクノロジは、フェールオーバー クラスタリングです。クライアントは仮想サーバー名を指定して接続するため、特に意識することなく物理的なクラスタ ノードがアクティブになっているサーバーにリダイレクトされます。
ログ配布やレプリケーションといった高可用性テクノロジでは、フェールオーバー サーバーのサーバー名が異なります。つまり、フェールオーバー後にクライアント接続を手動でリダイレクトする必要があります。この手動によるリダイレクトは、さまざまな方法で実行できます。
- フェールオーバー サーバーの名前をクライアントにハードコーディングして、再接続の試行がフェールオーバー サーバーに対して行われるようにできます。
- ネットワーク負荷分散を 100/0-0/100 の構成で使用して、接続がフェールオーバー サーバーに切り替わるようにできます。
- サーバー名のエイリアスや DNS テーブル内のエントリの切り替えなどを使用できます。
データベース ミラーリングでは、これらのオプションがすべて機能します。しかし、データベース ミラーリングには、組み込みのクライアントのリダイレクト機能もあります。クライアントの接続文字列にはミラー サーバーの名前を明示的に指定でき、プリンシパル サーバーに接続できない場合に、ミラーへの接続が自動的に試行されます。このプロセスは "明示的なリダイレクト" と呼ばれます。
クライアントの接続文字列を変更できない場合は、障害が発生したサーバーがミラー サーバーとして実行されている場合、暗黙的なリダイレクトが行われる可能性があります。ミラー サーバーへのすべての接続は自動的に新しいプリンシパルにリダイレクトされますが、これはミラー サーバーが実行されている場合にのみ機能します。
これらのオプションの詳細については、SQL Server 2005 ホワイト ペーパー「データベース ミラーリングを使用したアプリケーションのフェールオーバーの実装」を参照してください。
Q SQL Server 2005 にアップグレードしたときに、大きなテーブルがパーティション分割されるように再設計して、パーティション分割を使用したメンテナンスやスライディング ウィンドウのメカニズムを活用できるようにしました。これについては 2008 年 8 月の記事「パーティション分割、整合性チェックなど」で説明されていました。しかし、問題が発生しました。クエリが同じパーティションにアクセスしていない場合でも、同時実行されるアプリケーションのクエリがテーブル全体にわたってブロックされることがあります。SQL Server 2008 では、この問題が解決されていると聞きました。このブロックを防ぐにはどうすればよいか説明していただけますか。
図 1 パーティション テーブルのロックの確認
A この問題は、"ロックのエスカレーション" と呼ばれるメカニズムによって発生しています。SQL Server では、クエリによってデータが読み書きされている最中はデータを保護するために、データがロックされます。こうしたロックは、テーブル全体、データ ファイル ページ、または個々のテーブル行やインデックス行にかけることができ、すべてのロックで少量のメモリが使用されます。
クエリによりかけられるロックが多すぎる場合、SQL Server はテーブル内の行またはページのロックをすべて、テーブル全体の 1 つのロックに置き換えることができます (この現象は、約 5,000 個のロックがしきい値となって発生しますが、正確なアルゴリズムは複雑です。しきい値は構成できます)。このプロセスを "ロックのエスカレーション" と呼びます。
SQL Server 2005 では、クエリ A がテーブルの 1 つのパーティションで操作している際に、ロックのエスカレーションが行われるだけのロックがかけられた場合に、テーブル全体がロックされます。これにより、クエリ B は同じテーブルの異なるパーティションでも操作することができません。したがって、クエリ A の操作が完了してロックが解除されるまで、クエリ B はブロックされます。
SQL Server 2008 では、ロックのエスカレーションのメカニズムが強化され、テーブルはパーティション レベルでロックのエスカレーションが行えるようになりました。上記の例を使用して説明すると、クエリ A によって行われるロックのエスカレーションでは、テーブル全体ではなく、クエリ A が使用している 1 つのパーティションだけがロックされることになります。
クエリ B はロックされることなく別のパーティションで操作を行うことができます。クエリ B によってロックのエスカレーションが発生することもありますが、その場合はテーブル全体ではなく、クエリ B が操作しているパーティションだけがロックされます。
ロックのエスカレーションのこのモデルは、次の構文を使用して設定できます。
ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO);
GO
この構文では、テーブルがパーティション分割されている場合にパーティション レベルのロックのエスカレーションを使用し、テーブルがパーティション分割されていない場合には通常のテーブル レベルのロックのエスカレーションを使用することを、SQL Server のロック マネージャに指示します。既定の動作では、テーブル レベルのロックのエスカレーションが使用されます。クエリの動作によってはデッドロックが発生する可能性があるため、このオプションを設定する際には、注意が必要です。
たとえば、あるテーブルの異なるパーティションで、クエリ A とクエリ B の両方によってロックのエスカレーションが発生している場合に、各クエリがもう一方のクエリによってロックされているパーティションにアクセスしようとすると、デッドロック モニタのプロセスによってクエリのうちの 1 つが中止されます。
図には、sys.partitions システム カタログ ビュー (1 つ目の結果) および sys.dm_os_locks DMV (2 つ目の結果) へのクエリの例が示されており、パーティション レベルのロックのエスカレーションが発生しているパーティション テーブルに対するクエリで保持されているロックを確認できます。この場合は、パーティション レベルの排他ロックが 2 つあります (出力の HOBT ロック)。しかし、テーブル ロック (出力の OBJECT ロック) は排他的ではないため、ロックのエスカレーションが発生していたとしても、複数のクエリからパーティションにアクセスできます。これらの 2 つのパーティション ロックのリソース ID は、sys.partitions の出力にあるテーブルの、最初の 2 つのパーティションのパーティション ID と一致していることがわかります。
今年の初めに、パーティション レベルのロックのエスカレーションのしくみを示すサンプル スクリプトとデッドロックの可能性について、ブログに書きました。SQL Server 2008 オンライン ブックのトピック「データベース エンジンのロック」では、SQL Server 2008 におけるロックのすべての側面に関して詳しく説明されています。
Q サーバーのうちの 1 台で、データベースのトランザクション ログを保持しているディスクに問題がいくつか発生し、データベースが不安定になっていると考えました。前回の完全バックアップは 5 週間前で、すべてのログ バックアップの復元に非常に長い時間がかかりそうでした。問題が発生したのが業務時間外だったので、ダウンタイムを回避するため破損したトランザクション ログを再構築しました。状況によっては、この操作で問題が発生する可能性がありますが、データにアクセスされていなかったため、安全だと考えました。正しく対処できているでしょうか。
A 簡単にお答えすれば、トランザクション ログの再構築を検討するのは、バックアップから回復できないときだけです。皆さんはトランザクション ログの再構築による脅威についてはご存じですが (ご存じでない読者の方は、私のブログ記事「Last resorts that people try first... (最初に試される最後の手段)」を参照してください)、データベースに問題があったということは、回復中 (クラッシュ回復の実行時またはトランザクションのロール バック中) に問題が発生したということです。つまり、データベースでデータの破損が現実に発生する可能性があるということです。
問題は業務時間外に発生したということですが、定期ジョブやバックグラウンド タスクについては考慮しましたか。ログが破損した際にクラスタ化インデックスを再構築または再編成していたメンテナンス ジョブが実行されていた可能性もあります。バックグラウンド タスクが、ヒープ内のページまたはクラスタ化インデックスで非実体クリーンアップを実行していたかもしれません。たとえば、これらのうちのいずれかがクラスタ化インデックスの構造に変更を加えていた可能性があり、適切にロール バックされない場合は、データベースが破損したりデータが損失する可能性があるでしょう。
つまり、さらなる破損やデータの損失を発生させる可能性が非常に高いため、トランザクション ログの再構築は、常にあらゆる障害回復シナリオにおける絶対的な最終手段にしなければなりません。少なくとも、そのデータベースで完全な DBCC CHECKDB を実行して、破損があるかどうかを確認する必要があります。
さらに、バックアップ戦略を変更し、タイミング良く復元を実行できるようにして、トランザクション ログの再構築などの大掛かりな方法を使用しなくて済むようにします。バックアップ戦略を設計するための手順はこのコラムの内容から少しそれてしまいます。しかし、このトピックについては、来年のある時点で完全な特集記事として取り上げるつもりです。今後の記事にご期待ください。
Paul S. Randal は SQLskills.com の代表取締役であり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、『DBCC CHECKDB/repair for SQL Server 2005』を執筆し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に発表を行っています。彼のブログは SQLskills.com/blogs/paul で公開されています。