SQLServerの基本事項メモ

今までアプリケーションのデータソースとして何気なく使っていて、デバッグ時は問題ないが、
本番環境への配置やリモートからの接続が発生した途端にエラーが発生し困ったことが多々ある。
とりあえず動くようにという感じで場当たり的な対応で乗り切っていたが、何で動くようになったかまでの深い理解は得ていない。
そこで、ここいらでちゃんと基本から学習しておかないとと思い立ったわけです。
以下に、まとめておきます。

認証モード

Windows認証

OSの認証機能が使用される。可能な限りこちらを使うことがセキュリティ的に望ましい。
非Windows環境が混在している場合にはSQLServer認証を使用するしかない。

SQLServer認証

SQLServerにユーザー名、パスワードを渡してSQLServerで認証する方式。

混合モード

Windows認証、SQLServer認証ともに使用する。下位互換のために存在している。

システムデータベース

SQLServerが使用するデータベース
システムデータベースとしてmaster、msdb、model、tempdb が存在する。
このうちmasterデータベースはシステム情報(ユーザー情報など)を保存する。
そのほかについては、システムデータベースを参照。

ユーザーインスタンス

ユーザーインスタンスとは？

Windowsユーザー毎に独立したSQLServerの使用を可能とする仕組み。SQLServer2005から追加された。
親インスタンス(デフォルトでは./sqlexpress)からデータベースをコピーし、指定した*.mdfへインポートしてそれを使用するイメージ。
通信は名前付パイプのみで、リモート接続は不可。

以降のSQLServerではこの機能が廃止される予定とのこと。
そして、これからはSQLServerCompactを使用する方向に変わっていくと思われる。

システムデータベースのコピー

各ユーザーに対して最初にユーザーインスタンスが生成された時点で、そのユーザーインスタンスによって排他的に使用されるユーザーの
ローカルアプリケーションデータリポジトリディレクトリの下位パスに、Template Data フォルダーからシステムデータベース master および msdb がコピーされます。
通常、このパスは C:\Documents and Settings\\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS です。
ユーザーインスタンスが開始されると、tempdb、ログ、およびトレースファイルもこのディレクトリに書き込まれます。
ユーザーインスタンスには、ユーザーごとに一意の名前が生成されます。
SQL Server Express ユーザーインスタンス

※実行ユーザーがこのフォルダに対する適切なアクセス権限がないとエラーが発生するので注意すること。

接続文字列

ユーザーインスタンスを使用する場合は下記のような感じ。

Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;
AttachDBFilename=|DataDirectory|\app_data\Northwind.mdf;
Initial Catalog=Northwind;

一部解説すると、DataSourceには親インスタンス名を指定。UserInstanceをtrueにしてユーザーインスタンスを有効化している。
AttachDBFilenameへ独立して使用するデータベースファイルパスを指定する。