Troubleshooting Connectivity #5 - セッション確立までの動作

Article
04/11/2013

高橋理香
SQL Developer Support Escalation Engineer

こんにちは。

四半期に一回のペースでしかアップできていないのですが、この Troubleshooting Connectivity シリーズはまだネタはあるので、もう少し続けたいと思います。

さて今回ですが、Troubleshooting Connectivity #1 でご説明した接続 3 過程のうちの OS レベルのセッション確立の試行までの動作についてご紹介します。わかりやすくするために、TCP/IP プロトコルを使用した場合を例に説明します。

TCP/IP のセッションを確立するために必要な情報とは何か (復習)

TCP/IP プロトコルを使用して SQL Server との間にセッションを確立するためには、SQL Server が動作するサーバーの IP アドレス (フェールオーバークラスタ構成の場合には、SQL Server 用に割り当てた IP アドレス) と待ち受けているポート番号が必要です。

SQL Server 7.0 までは、そもそも同一マシン上に複数インスタンスをインストールする機能はなかったので、SQL Server のポート番号といえば 1433 でよかったのですが、SQL Server 2000 以降は複数インスタンスを単一マシン上で動作させることができますので、クライアントからの接続待ち受けには各インスタンスで異なるポート番号を使用するよう構成する必要があります。既定では、既定のインスタンスはこれまで通りの 1433 で待ち受け、名前付きインスタンスは動的にポート番号が割る振られるようになっています。

このように、サーバーでは待ち受けポート番号が各インスタンスによって異なりますので、接続元では、名前付きインスタンスがどのポート番号で待ち受けしているのかを都度確認する必要がある状況といえます。この、都度の確認に使用されるのが SQL Server Browser サービスです。(SQL Server 2000 は当サービスは SQL Server サービスに統合されていました。)接続元では SQL Server Browser サービスに待ち受けポートが何番なのかを問い合わせ、得られた情報を使って接続試行を行います。

SQL Server Browser サービスを介して名前付きインスタンスへアクセスするまでの動作については、以下のブログページの「SQL Server の名前付きインスタンスに接続するまでの動作の流れと問題発生のメカニズム」において図入りで説明がありますので、こちらも併せて参考にしてみてください。

[SQL Connectivity] Case Study : クラスタ化された構成の SQL Server 2008 Service Pack 1 以前のバージョンの名前付きインスタンスに Windows Vista 以降から接続できない

名前付きインスタンスへの接続で SQL Server Browser サービスは必須か

SQL Server Browser サービスの役割が待ち受けポート番号の通知ということだとするならば、名前付きインスタンスが動的ポートではなく、あらかじめ固定のポートを使用するように設定されており、そのポート番号をサーバーの管理者より通知されて知っている場合、それでも SQL Server Browser サービスを介した接続をしなければならないのでしょうか。

答えは No です。

接続に必要となる情報が揃っていれば、SQL Server Browser サービスを介さずに接続試行することができます。
具体的には、以下のいずれかの方法を使用します。

接続先として、プロトコルとポート番号を指定する。
クライアント側で別名を作成し、別名の設定内であらかじめ通知されているポート番号を指定しておく。

上記のような設定の場合には、最初から SQL Server に接続試行を行います。
いずれもサポートされる接続方法です。

以下に使用例をご紹介します。

例1: プロトコルとポート番号を指定した接続を行う

VBScript/ADO で SQLNCLI10 を使用して TCP/IP プロトコル、ポート番号 1435 に接続

Dim cn Set cn = CreateObject("ADODB.Connection") cn.ConnectionString = "Provider=SQLNCLI10;Data Source=tcp:SERVER01\INST01,1435;Integrated Security=SSPI;" cn.Open

C# のアプリケーションで System.Data.SqlClient を使用して TCP/IP プロトコル、ポート番号 1500 に接続

string strcn = "Data Source=tcp:SEREVER01\INST01,1500;Integrated Security=SSPI;"; SqlConnection connection = new SqlConnection(strcn); connection.Open();

例2: 別名を作成し、それを上記の VBScript で使用する

別名を作成するには、クライアント側で以下のいずれかのツールを使用します。

SQL Server 構成マネージャー (SQL Server Configuration Manager)
SQL クライアント設定ユーティリティ

SQL Server 構成マネージャーは、SQL Server の [クライアントツール] をインストールしている場合に利用できます。起動は、SQL Server のメニューから [構成ツール] - [SQL Server 構成マネージャー] を選択します。

以下では、新規に別名 "ALIAS01" を作成しており、その接続先 SQL Server は "SERVER01"、プロトコルは "TCP/IP"、ポート番号は "1500" です。

もしも 64 bit Windows 上で 32bit アプリケーション用に別名を作成する必要がある場合には、[SQL Native Client 10.0 の構成 (32 ビット)] の方で設定します。

SQL クライアント設定ユーティリティは OS 同梱のため、どの Windows OS であっても利用できます。64 bit Windows 上で 64 bit アプリケーション用のために別名を作成する場合には、System32 フォルダ配下にある cliconfg.exe を、32 bit アプリケーション用の場合には、SysWOW64 フォルダ配下にある cliconfg.exe を起動します。

以下は、SQL Server 構成マネージャーでの別名設定と同じ内容で設定した場合の例です。

別名はレジストリに格納されますが、どちらのツールを使って作成しても同じ場所に格納されます。

64bit 用の設定: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
32bit 用の設定: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

このように、作成した別名を C# のアプリケーションで使用する場合には接続文字列で次のように指定します。

string strcn = "Data Source=ALIAS01;Integrated Security=SSPI;"; SqlConnection connection = new SqlConnection(strcn); connection.Open();

※注意: SQL Server が動的ポートの設定となっている場合には、サービス再起動によって異なるポート番号で待ち受けする可能性があります。別名や接続文字列に指定したポート番号とは異なる番号で待ち受けしている場合には接続は失敗しますので、都度変更する必要があります。別名や接続文字列でのポート番号の指定を行う場合には、SQL Server のポート番号も固定することをお勧めします。

※補足：作成した別名は削除することもできます。SQL Server 構成マネージャーでの別名操作については以下のトピックをご覧ください。

クライアントが使用するサーバーの別名の作成または削除 (SQL Server 構成マネージャー)

SQL Server Browser サービスが停止していたら必ず接続は失敗するか

ご紹介した 2 つの方法 (接続文字列でのプロトコルとポート番号指定、または、別名の作成) を採用していない場合、SQL Server Browser サービスが停止していると接続は失敗するものでしょうか。

答えは Yes でもあるし No でもあります。

実は、Microsoft が提供している SQL Server 用の各種接続コンポーネントには、接続情報のキャッシュが存在します。

キャッシュは接続元の環境 (クライアント/サーバーの場合にはクライアント側) に存在し、以前に SQL Server への接続が成功したときに使用したポート番号を保持します。これにより、次回の同一接続先への接続要求では、そのキャッシュされたポート番号を使用して SQL Server に接続試行することになります。(※) したがって、SQL Server Browser サービスが停止していても、そのポート番号が接続試行時点で有効であれば、接続は成功することになります。

では、SQL Server サービス再起動によって以前とは異なるポート番号で待ち受けしてしまったらどうなるのでしょう。

このようにサーバー側で待ち受けポート番号が変わっても、その時点で同サーバーを使用する各クライアントのキャッシュの情報までクリアするような操作は行いません。そして、新たな接続要求では、キャッシュ内の古いポート番号の情報を使用して接続試行が行われることになります。そのため、この接続試行は失敗することになります。でも、ご安心ください。接続コンポーネントでは、キャッシュを使用した接続試行が失敗した場合には、キャッシュを古いものとして認識してクリアし、SQL Server Browser サービスへ問い合わせることで適切なポート番号を取得してから接続試行を行うよう動作します。つまり、この時点で SQL Server Browser サービスが停止していれば接続が失敗することになります。

もしも SQL Server Browser サービスが起動していれば、セッション確立まで処理を進めることができます。

このように、キャッシュは SQL Server Browser サービスへの問い合わせ分のオーバーヘッドを減らす目的として用意されてはいますが、キャッシュによって接続できることを保証するものではなく、また、キャッシュが古い場合には SQL Server Browser サービスへの問い合わせは接続成功のためには必要となります。別名や接続文字列へのポート番号指定がない限りは、SQL Server Browser サービスを開始した状態にて利用するようにしましょう。

※ここで説明しているキャッシュは接続プールとは異なるものです。接続プールは、SQL Server への接続は切断せずに保持した状態でプールし、以降の接続要求で接続試行の段階からの処理を行うことなく再利用することで接続時のオーバーヘッドを減らす目的で使用されます。同一プロセス内の接続要求にのみ再利用が許可されます。一方で今回ご紹介しているキャッシュは、プロトコルやポート番号の情報のみを保持し、接続そのものの実体は保持していません。同一のマシン同一の接続コンポーネントを利用した接続にのみ再利用が許可されます。

別名やキャッシュはどのような順番で利用されるのか

別名が存在し、キャッシュも存在する環境の場合など、接続試行までに何がどのような順番でチェックされるのでしょうか。
その順番をまとめると以下の通りとなります。

指定された接続先と合致する別名が存在するかどうかをチェックし、存在する場合には、その別名の設定にしたがって接続試行する。
別名が存在せず接続先としてプロトコルやポート番号が明示的に指定されている場合には、その設定にしたがって接続試行する。
別名が存在せずプロトコルやポート番号の指定がない場合において、以前に成功した接続の情報がキャッシュ内に存在する場合、そのキャッシュの情報を使用して接続試行する。
キャッシュが存在しない場合において、接続先に \ マークが含まれる場合、接続先は名前付きインスタンスと判断する。
名前付きインスタンスの場合、指定された接続先が IP アドレスである場合、そのホスト名の取得のために名前解決 (逆引き) を行う。
名前付きインスタンスの場合、解決した名前を使って SQL Server Browser サービスへ問い合わせ、待ち受けプロトコルやポート番号を取得する。
既定インスタンスの場合、指定された接続先と TCP ポート 1433 を使い、名前付きインスタンスの場合、取得したプロトコル情報やポート番号を使って、SQL Server に接続試行する。

※上記で使用されている「接続試行する」とは、SYN パケット送信によるセッション確立からの処理を指します。

もしも名前付きインスタンスへの接続でセッション確立に失敗しているような状況が見られた場合には、上記を参考に、プロトコルやポート番号を指定することで SQL Server Browser サービスを介さずに接続ができるかを確認してみてください。これによって、SQL Server Browser サービスへのアクセスの問題か、キャッシュによる恩恵を受けていたかなどを切り分けることもできます。

この内容がトラブル時の対策の一助となればと思います。

次回は・・・接続における問題のトラブルシューティングの一助となるようなものをご紹介したいと思います。

過去の Troubleshooting Connectivity シリーズはこちら。