SQL Server のインスタンスに接続する仕組みと方法

f:id:knkomko:20191214144113p:plain

はじめに

今回SQL Server に接続する機会がありました。
そこで調べたインスタンスに接続する仕組みや方法についてまとめます。

インスタンスとは

SQL Server のサービス(常駐プログラム)のことを指します。
SQL Server はインスタンスごとに複数のデータベースを保存できます。
f:id:knkomko:20191214150148g:plain:w350
第1回 SQL Serverのインストールをチェックする (2/3)：SQL Serverミニマム管理Q＆A - ＠IT

SQL Serverがインストールされた順番によってインスタンスは２種類に分けられ
それぞれ接続時の仕組みに違いがあります。

・既定インスタンス
　1 番目にインストール(作成)されたSQL Serverのインスタンス

・名前付きインスタンス
　2 番目からインストール(作成)されたSQL Serverのインスタンス

SQL Server 接続の仕組み

・既定インスタンス
　既定インスタンスの場合、指定された接続先(コンピュータ名 or IPアドレス)と
　TCPポート 1433 を使ってSQL Serverに接続します。
f:id:knkomko:20191215005219p:plain:w550

・名前付きインスタンス
　名前付きインスタンスの場合、SQL Server Browser サービスへ問い合わせ、
　取得した待ち受けプロトコルやポート番号を使ってSQL Serverに接続します。
f:id:knkomko:20191215005228p:plain:w550

SQL Server 接続の方法

・ Windows アプリケーション
SqlClientに使用する接続文字列は以下の通りです。ポート番号は省略可能です。
また既定インスタンスに接続する場合はインスタンス名の省略が可能です。
Data Source=[コンピュータ名 or IPアドレス]\\[インスタンス名],[ポート番号];Initial Catalog=[データベース名];User ID=[ユーザ名];Password=[パスワード]

SqlConnection sqlConnection = new SqlConnection("Data Source=DESKTOP-RMBKTOC\\SQLEXPRESS,1433;Initial Catalog=sampleDB;User ID=Makoto;Password=password")

・SQL Server Management Studio
　SQL Server の管理ツールから接続する場合です。
　サーバ名に「コンピュータ名￥インスタンス名」と指定して接続を行います。
　既定インスタンスに接続する場合はインスタンス名の省略が可能です。
f:id:knkomko:20191214151444p:plain:w350

・コマンドプロンプト
　sqlcmd を使用する場合です。
　既定インスタンスに接続する場合はインスタンス名の省略が可能です。
　Windows認証による接続

sqlcmd -S [コンピュータ名 or IPアドレス]\[インスタンス名] -E

　SQL Server認証による接続
　　Enter した次にパスワードの入力を求められます。

sqlcmd -S [コンピュータ名 or IPアドレス]\[インスタンス名] -U [ユーザ名]

名前付きインスタンスの接続

名前付きインスタンスの接続では SQL Server Browser の有効化が必要です。
デフォルトでは無効となっているためサービスから有効にします。

コントロールパネル → 管理ツール → サービスから
「SQL Server Broeser」のプロパティを開きます。
f:id:knkomko:20191215020414p:plain:w400

スタートアップの種類を「自動」に変更して[OK]を押します。
f:id:knkomko:20191215020227p:plain:w400

構成マネージャのSQL ServerからSQL Browser Serviceを開始します。
f:id:knkomko:20191215020756p:plain:w400

IPアドレスを用いた接続

コンピュータ名では接続が成功してもIPアドレスでは接続出来ませんでした。

C:\Users\User1>sqlcmd -S DESKTOP-RMBKTOC\SQLEXPRESS -E
1> [:]quit

C:\Users\User1>sqlcmd -S 192.168.0.15\SQLEXPRESS -E
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server ネットワーク インターフェイス:  指定された Server/Instance の位置を特定しているときにエラーが発生しました [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : ログイン タイムアウトが時間切れになりました.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server への接続の確立中に、ネットワーク関連のエラーまたはインスタンス固有のエラーが発生しました。サーバーが見つからないか、アクセスできません。インスタンス名が正しいことと、SQL Server がリモート接続を許可するように構成されていることを確認 してください。詳細については、SQL Server オンライン ブックを参照してください。.

名前付きインスタンスの接続と同様にSQL Browser Serviceを有効にする事で接続が行えるようになります。

C:\Users\User1>sqlcmd -S 192.168.0.15\SQLEXPRESS -E
1> [:]quit

C:\Users\User1>sqlcmd -S DESKTOP-RMBKTOC\SQLEXPRESS -E
1> [:]quit

C:\Users\User1>sqlcmd -S DESKTOP-RMBKTOC -E
1> [:]quit