第2回では「データベース ファイル」についてまとめてみました。今回は SQL Server インスタンス について、自分の整理も兼ねて、まとめてみようと思います。
SQL Server インスタンスについて
SQL Server インスタンス (データベース エンジン インスタンス) は、 SQL Server 実行可能ファイル (sqlservr.exe) をサービスとして起動したものであり、システム データベース (master, msdb, model, tempdb, resource) および 1つ以上のユーザー データベースを管理します。
また、SQL Server プロセスのメモリ、スレッド管理、データベースの管理、トランザクションの管理、デッドロックの監視、クエリの実行 (クエリ実行プランの生成)、クライアントからの接続要求の受付などの様々な役割を担っています。
SQL Server インスタンスは「既定のインスタンス」、「名前付きインスタンス」の２種類があり、同一サーバー上に「既定のインスタンス」は1つのみしかインストールすることが出来ませんが、「名前付きインスタンス」は複数インストールすることが出来ます。
既定のインスタンスについて
既定のインスタンスのサービス名は「MSSQLSERVER」 となり、同一サーバーに１つのみインストールが可能になります。
- [既定のインスタンス] (例: SQL Server 2019)
- 既定のインスタンスの実行ファイルのパス (既定)
"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER
※ MSSQL15 は「SQL Server 2019」､MSSQL14 は「SQL Server 2017」、MSSQL13 は「SQL Server 2016」と SQL Serverのバージョン毎にファイルパスが決まっています。
既定のインスタンスは、TCP：1433 ポートで待ち受け (リッスン) されています。
| 2020-08-27 09:05:30.38 spid27s Server is listening on [ 'any' <ipv6> 1433]. 2020-08-27 09:05:30.38 spid27s Server is listening on [ 'any' <ipv4> 1433]. |
TCP：1433 ポートは、SQL Server 既定のインスタンス用として登録済みポート番号として登録されており、クライアントから SQL Server インスタンスへ接続する際、「ホスト名」のみを指定した場合は、自動的に TCP：1433 ポート に対して接続を試みる動作が行われます。
※ 既定のインスタンスの待ち受けポートを TCP：1433 ポート以外に設定することも可能ですが、一般的には変更することが推奨されておらず、変更した場合、SQL Server 既定のインスタンスへの接続時に、毎回 明示的にポート指定 (以下の例では、TCP：30000 ポート を明示的に指定) をする必要があります。
名前付きインスタンスについて
名前付きインスタンスのサービス名は「MSSQL＄＜任意のインスタンス名>」 となり、同一サーバーに複数インストールすることが可能になります。
- [名前付きインスタンス] (例: SQL Server 2017 : 名前付きインスタンス名 S2K17)
- 名前付きインスタンスの実行ファイルのパス (既定)
"C:\Program Files\Microsoft SQL Server\MSSQL14.S2K17\MSSQL\Binn\sqlservr.exe" -sS2K17
名前付きインスタンスは、動的ポート (Windows Server 2008 R2 以降、既定 49152–65535) で待ち受け (リッスン) されています。
| 2020-08-27 09:25:15.99 spid17s Server is listening on [ 'any' <ipv6> 49154]. 2020-08-27 09:25:15.99 spid17s Server is listening on [ 'any' <ipv4> 49154]. |
名前付きインスタンスは、動的ポートに設定された TCP ポートの範囲で待ち受け (リッスン) されますが、前回 リッスンされた TCP ポート情報を保持しています。
そのため、名前付きインスタンスを起動し、前回 リッスンされた TCP ポートが他のアプリケーションなどで使用されていない場合は、前回 リッスンされた TCP ポートで再度 待ち受け (リッスン) されます。
なお、前回 リッスンされた TCP ポートが他のアプリケーションなどで使用されていた場合、動的ポートの範囲で利用されていない TCPポートで待ち受け (リッスン) されます。
[名前付きインスタンス再起動後]
| 2020-08-27 10:25:15.99 spid17s Server is listening on [ 'any' <ipv6> 49154]. 2020-08-27 10:25:15.99 spid17s Server is listening on [ 'any' <ipv4> 49154]. |
※ 前回と同じく TCP : 49154 ポート で待ち受け (リッスン) していることが確認できます。
なお、名前付きインスタンスの場合においても、毎回 同じ TCPポートで待ち受け (リッスン) させることが可能です。一般的に ファイア ウォールで許可する TCP ポートを制限したい場合、毎回 同じ TCPポートで待ち受け (リッスン) させることを検討することが多いかと思います。
しかしながら、毎回 同じ TCPポートで待ち受け (リッスン) させた場合、名前付きインスタンス起動時に既に該当のTCPポートが使用されていると、名前付きインスタンスの起動時が失敗するようになる点に注意が必要です。
毎回 同じ TCPポートで待ち受け (リッスン) させる方法
1) SQL Server 構成マネージャーを起動し、「SQL Server ネットワークの構成」-「該当の名前付きインスタンス名」(今回は「S2K17」) -「TCP/IP」- 右クリック -「プロパティ」を選択します。
2) 「IP アドレス」タブ - IPALL「TCPポート」: 空白、「TCP 動的ポート」: 任意のポート(今回は 49154) に変更後、「適用」-「OK」をクリックし、該当の名前付きインスタンスを再起動します。
※ 名前付きインスタンス(サービス)の再起動後から設定が反映されます。
[補足]
動的ポートの範囲は変更することが可能となっており、「netsh int ipv4 show dynamicport tcp」コマンドを実行することで現在の動的ポートの設定を確認することができます。
[動的ポート範囲 確認コマンド 例]
※ 確認コマンドを実行した環境では、動的ポートの範囲を「netsh int ipv4 set dynamicport tcp start=1025 num=64510」コマンドで変更しています。
名前付きインスタンスへの接続について
名前付きインスタンスは動的ポートで待ち受け (リッスン)されており、 既定のインスタンスとは異なり、待ち受け (リッスン) されているポートが異なります。
そのため、クライアントから名前付きインスタンスへ接続する際に、SQL Server Browser サービス から 接続先の名前付きインスタンス がどの TCP ポートで待ち受け (リッスン) しているかの情報を受け取り、名前付きインスタンスへの接続が実施されています。
[SQL Server Browser サービス]
〇 名前付きインスタンスへの接続の流れ (簡易)
A. 接続文字列に明示的にポート番号が指定されている場合
接続文字列に指定されたポートでリッスンされている 名前付きインスタンス へのセッションの確立を試みる。
B. 接続文字列に明示的にポート番号が指定されていない場合
- UDP 1434 ポートを介して、SQL Server Browser サービス から 接続先の名前付きインスタンスの待ち受け (リッスン) ポート情報を取得。
- 1. の情報をもとに、接続文字列に指定された 名前付きインスタンス へのセッションの確立を試みる。
[補足]
- 接続文字列のインスタンス名に 「\」(例: Server1\S2K17 など) が含まれている場合、名前付きインスタンスと判断されます。
- 接続文字列に明示的にポート番号が指定されていない状態で、SQL Server Browser サービスを停止した場合、接続先の名前付きインスタンスの待ち受け (リッスン) ポートが分からないため、接続に失敗します。
- SQL Server Browser サービスを停止している状態であったとしても、接続文字列に明示的にポート番号が指定されている場合、名前付きインスタンスに接続することが可能となる。
まとめ
今回は、SQL Server インスタンスの役割、種類、および、 待ち受け (リッスン) ポートの違いなどをまとめてみました。
次回は、SQL Server へ接続する際に使用するツールについてまとめてみようと思います。
【第4回】基本から始める SQL Server【SQL GUI ツール】へ
