SQL に関する Q&Aクラスタリングの詳細、不可解なハング、SA アカウントなど
Nancy Michell
質問 - クラスタリングのしくみについて詳しく理解する必要があります。使用する環境は、SQL Server™ 2005 を実行する 64 ビットの Windows Server® 2003、SQL Server Reporting Services (SSRS) Web ファーム (レポート サーバー スケールアウト展開)、SSRS TempDB カタログ サーバー、およびリンク サーバー経由でサードパーティのデータベースからデータを取得し、そのデータを SSRS 用に保存する SQL Server という構成です。
3 ノード クラスタ (アクティブ/アクティブ/パッシブ) を考えています。ノード 1 はアクティブとし、ここにはサードパーティのデータベースから取得したデータを保存します。ノード 2 もアクティブとし、ここには SSRS カタログを保存します。ノード 3 はパッシブとし、ノード 1 またはノード 2 のいずれかをフェールオーバーします。どうすればよいでしょうか。
回答 - 残念ながら、SQL Server のクラスタリングに関して、アクティブ/アクティブ、およびアクティブ/パッシブという表現に惑わされている人が多すぎます。こうした人々は、SQL のクラスタリングでは、複数のサーバー間での、1 つのデータベースや 1 つの SQL インスタンスの "スケールアウト" がサポートされているだろうと考えています。これは間違いです。SQL Server では、アクティブ/アクティブのデータベースやインスタンスというものは存在しません。"インスタンス" とは、対応するデータベースを保持する SQL Server の 1 つのインストールです。SQL Server インスタンスごとのクラスタリングは、常にアクティブ (1) に対してパッシブ (n) です (n の値は 1 ～ 7 で、使用している SQL Server のバージョンによって異なります)。これが、フェールオーバー クラスタリングと呼ばれる理由です。
このことを理解すれば、フェールオーバー クラスタリングのインスタンスを一連のノードに複数インストールすることを検討できるようになります。たとえば、3 台の物理サーバーがすべて共有ディスクを使用している場合は、既定でアクティブになっているインスタンスをノード 1 に 1 つ保持し、既定でアクティブになっている 2 つ目のインスタンスをノード 2 に 1 つ保持することができ、どちらもノード 3 にフェールオーバーすることができます。各インスタンスは完全に独立しており、データを共有することもなく、アクティブ/アクティブではありません。これらはどちらもアクティブ/パッシブで、どちらも同じフェールオーバー インスタンスを共有します。両方のインスタンスがノード 3 にフェールオーバーされるとすると、長期的な課題は、ノード 3 が負荷に耐えられるかどうかを把握することです。仕様では、フェールオーバーは、等しい処理能力があることが前提です。通常運用での処理ピーク時の負荷に 2 つのノードが必要だとすると、通常は 2 つのノードに割り当てられているピーク時の負荷にノード 3 が耐えられる可能性はあまりありません。
とは言うものの、クラスタを実行できるハードウェアの相対的なコストを考えると、両方のプリンシパル ノードで同時に障害が発生し、全体の負荷が 1 つのノードにかかる可能性が一般的にどの程度重視されるかはわかります。この点を考えると、100% のフェールオーバー能力を手に入れるのではなく、ある程度のリスクを見込むというビジネス上の決断を下すことになるかもしれません。
さいわい、良いニュースがあります。SQL Server 2005 では、高可用性 (HA) を実現する選択肢を数多く提供しており、クラスタよりも迅速にフェールオーバーすることができる方法や、データの重複コピーも可能な方法などがあります (クラスタリングは単一の SAN に依存します)。こうした選択肢には、ミラーリング、ピア ツー ピア レプリケーションなどがあります。このような新しい方法の登場によって、あらゆる種類のニーズを満たす選択肢が大幅に増えました (いくつかの HA 機能を組み合わせることもできます)。
ダウンロード可能になった Microsoft® Cluster Configuration Validation Wizard (ClusPrep) は、以前のハードウェア互換リスト (HCL) テストに代わるものです。HCL テストでは、クラスタリングに "耐えられる" と判断するための構成全体の検証に、数か月かかることもありました。ClusPrep により、DBA がハードウェア検証ツールを使用できるので、認定済みのハードウェアを用意するのにかかる費用と時間が大幅に削減されます。ClusPrep を使用すると、1 つのクラスタ ノード セット内で異種ハードウェアを検証し展開することさえ可能になる場合があります。
質問 - 使用しているコンピュータのうち 1 台で、delete プロシージャを実行すると 12 時間後にハングするようです。ブロックはされていません。最も遅いクエリ プランを確認すると、87,327 秒間実行されているトリガが見つかるので、プロシージャはこのトリガ内でハングしていると思われます。どのステートメントがハングしているかを正確に確認するにはどうしたらよいでしょうか。
回答 - さまざまな理由により、トリガ内のループが終了していない可能性が大いにあります。長時間ハングしており、どのステートメントが実行されているかを確認する必要がある場合は、図 1 のコードを実行してください。このコードを実行すると、現在実行中のステートメントを把握できます。これがおそらくコンピュータでハングが発生する原因となっているステートメントです。
Figure 1 現在実行中のステートメントを検索する
-- Look at the current statement being run:
-- Put results to text (Ctrl + T)
DECLARE @Handle binary(20),
@start int,
@end int,
@SPID int
SET @SPID = spid
SELECT @Handle = sql_handle,
@start = stmt_start,
@end = stmt_end
FROM Master..sysProcesses(NOLOCK)
WHERE SPID = @SPID
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle))PRINT ‘Handle not found in cache’
ELSE
SELECT ‘Current Statement’= substring(text, (@start + 2)/2, CASE @end WHEN -1 THEN (datalength(text))
ELSE (@end -@start + 2)/2 END)
FROM ::fn_get_sql(@Handle)
質問 - ファイアウォール越しのトランザクション レプリケーションをサポートする必要があります。パブリッシャとディストリビュータはファイアウォールの外側にあり、サブスクライバは内側にあります。サブスクライバは 1433 でリッスンするように設定しています。コンピュータ名は、パブリッシャが PUBMACHINE、ディストリビュータが DISTMACHINE、サブスクライバが SUBMACHINE です。初期スナップショットとプッシュ パブリケーションを成功させるには、どのポートを開く必要がありますか。
回答 - プッシュ サブスクリプションを使用している場合は、ディストリビューション エージェントは、(ファイアウォールの外側にある) ディストリビュータ コンピュータで実行され、スナップショット エージェントによって生成されたスナップショット ファイルにローカルにアクセスできるため、SQL Server ポート (今回の場合は 1433) を開くだけで十分です。しかし、プル サブスクリプションを使用している場合は、サブスクライバ コンピュータで実行されているディストリビューション エージェントは、何らかの方法でファイアウォール越しにスナップショット ファイルにアクセスする必要があります。以下の選択肢を検討することができます。
ファイアウォールの外側にあるファイル共有のスナップショット ファイルに既にアクセスできるとすると、ファイアウォールで Windows® ファイル共有ポートを開いて、ファイアウォールの内側で実行されているディストリビューション エージェントが、外側にあるスナップショット ファイルにアクセスできるようにすることができます (ただし、これによって発生し得る、インフラストラクチャの他の部分へのセキュリティ面での影響に注意してください)。既定のスナップショットの場所としてローカル パスを構成した場合 (SSMS の既定値) は、スナップショット ファイルの取得場所をオーバーライドするために、ディストリビューション エージェントの /AltSnapshotFolder オプションの使用が必要になることがあります。
また、スナップショット ファイルの転送に FTP を使用するようにレプリケーションを構成することもできます (この場合はポート 21 を開く必要があります)。
質問 - SQL Server 2005 で SA アカウントを無効にすると不都合があるかどうか、また SA アカウントを無効にすることによって真のセキュリティ価値がもたらされるかどうかを知りたいと考えています。この問題に関するホワイト ペーパーはありますか。
回答 - 混合モードが有効になっていない場合、SQL Server 2005 を新しくインストールすると、SA アカウントは既定で無効になり、このアカウント用にランダム パスワードが生成されます。自分で無効にすることもできます。これに関するホワイト ペーパーはありませんが、ログインの無効化と名前変更については、ベスト プラクティスのホワイト ペーパーで説明されています。
SA アカウントを不正利用しようとする試みを防ぐために、名前を変更することもできます。無効にしたアカウントを有効にする場合、そのアカウントに新しいパスワードを設定する必要があることを覚えておいてください。
これによって真のセキュリティがもたらされるかどうかという質問の答えとしては、アカウントを無効にすることによってセキュリティが向上するのは、アカウントが無効になっている間はパスワードの推測が無意味であるという点を思い出してください。ハッカーやウイルスが何度試みても、ロックされているアカウントに対するブルート フォース攻撃は成功しません。SA の名前変更や無効化を行うと、接続に SA アカウントを使用しているアプリケーションは機能しなくなります。このようなアプリケーションを見つけて、修正または排除することは、何よりの優先事項と考えてください。既に説明したように、再び有効にするまではそのアカウントをデータベースへの接続に使用できません。また、早い段階で認証プロセスが失敗するため、システムが攻撃を受けた場合でも、攻撃が失敗するまでの被害が少なくて済みます。
質問 - 大規模オンライン トランザクション処理 (OLTP) データベースの 1 つに、データ ファイルの 2 倍のサイズのログ ファイルがあります。ログ ファイルを適切なサイズまで小さくするため、以下のコマンドを使ってみましたが、さらにサイズを小さくする必要があります。
backup database syslogs to backupfile
DBCC SHRINKFILE (syslogs_log)
回答 - backup database ステートメントを backup log ステートメントに変更してください。または、データベースを単純復旧モードにして、shrinkfile ステートメントを発行することもできます。ログの圧縮が完了したら、データベースを以前の復旧モデルの設定に戻し、データベースをバックアップします。それでも圧縮されなかったら、開いているトランザクションがないことを確認します (dbcc opentran を使用します)。詳細については、サポート技術情報の資料 (support.microsoft.com/kb/907511) を参照してください。
質問 - SQL Server エージェントの定期ジョブの実行中にフェールオーバーが発生した場合、フェールオーバー後にそのジョブはどうなりますか。手動でジョブを再開する必要はありますか。
回答 - はい。他のプロセスを用意していない場合は、手動で開始する必要があります。手動でジョブを再開する必要をなくすために、ジョブの完了時にテーブルを更新するスクリプトを記述できます。値が 1 の場合は、ジョブの実行が完了しています。その他の値の場合は、ジョブの実行が完了しておらず、後で 2 つ目のジョブが開始コマンドを発行することを示します。したがって、ジョブの実行中にフェールオーバーが発生した場合、そのジョブを再実行する必要がありますが、このスクリプトを記述することにより、翌営業日までに絶対に完了している必要のある、夜中に実行される重大なジョブに関する懸念をある程度軽減することができます。
ヒント : アップグレードと DBCC UPDATEUSAGE
SQL Server 2000 から SQL Server 2005 にアップグレードする予定はありますか。
その場合、データベースのアップグレード直後に DBCC UPDATEUSAGE を実行してください。
DBCC UPDATEUSAGE を実行すると、カタログ ビュー内のページ数と行数の誤りが報告および修正されます。このような誤りがあると、sp_spaceused システム ストアド プロシージャから間違った使用領域レポートが返される場合があるので、このような誤りは修正する必要があります。SQL Server 2005 では、これらの値が常に正しく保たれるので、これらのデータベースでは間違った数になることはありません。ただし、SQL Server 2005 にアップグレードしたデータベースには無効な数が含まれている場合があるため、アップグレード後に DBCC UPDATEUSAGE を実行する必要があります。
ここで、DBCC UPDATEUSAGE の動作について説明します。DBCC UPDATEUSAGE を実行すると、テーブル内またはインデックス内のパーティションごとに、行数、使用ページ数、予約ページ数、リーフ ページ数、およびデータ ページ数が修正されます。システム テーブルに誤りがなければ、データは返されません。誤りが検出および修正され、かつ、WITH NO_INFOMSGS を使用しなかった場合、システム テーブル内の更新された行と列が返されます。
DBCC UPDATEUSAGE は、使用領域カウンタの同期にも使用することができます。DBCC UPDATEUSAGE は、大規模テーブルやデータベースに対して実行すると少し時間がかかる場合があるので、通常、sp_spaceused から間違った値が返されている疑いがある場合のみに使用します。sp_spaceused にオプション パラメータを指定して、テーブルやインデックスの領域情報を返す前に DBCC UPDATEUSAGE を実行することもできます。
SQL Server 2005 では DBCC CHECKDB が強化されており、ページ数や行数が負になるタイミングを検出します。負の値が検出されると、警告と、問題に対処するために DBCC UPDATEUSAGE を実行するよう勧めるメッセージが出力されます。データベースを SQL Server 2005 にアップグレードしたことによりこの問題が発生したように見えますが、無効な数がアップグレードを行う前から存在していたことは確かです。
例として、現在のデータベース内の全オブジェクトのページ数か行数、またはその両方を更新する方法を示します。以下のコマンドでは、データベース名に 0 を指定しています。このコマンドを実行すると、現在のデータベースに関して更新された情報がレポートされます。
DBCC UPDATEUSAGE (0);
GO
たとえば AdventureWorks について、ページ数か行数またはその両方を更新し、さらに、情報メッセージを表示しないようにするには、以下のコマンドを実行します。このコマンドでは、データベース名として AdventureWorks を指定し、すべての情報メッセージを表示しないようにしています。
USE AdventureWorks;
GO
DBCC UPDATEUSAGE (‘AdventureWorks’) WITH NO_INFOMSGS; GO
詳細については、SQL Server Books Online で「DBCC UpdateUsage」を検索してください。
Thanks to the following Microsoft IT pros for their technical expertise: Ken Adamson, Sunil Agarwal, Siggi Bjarnason, Shaun Cox, Laurentiu Cristofor, Ernie DeVore, Michael Epprecht, Lucien Kleijkers, Raymond Mak, Chat Mishra (MSLI), Niraj Nagrani, Rick Salkind, Jacco Schalkwijk, Vijay Sirohi, Vijay Tandra Sistla, Matthew Stephen, and Buck Woody. Thanks to Saleem Hakani for this month's tip.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.