SQL Server メモ

ダウンロードする種類

LocalDB
開発用。プログラミング機能。

Express
データーベースエンジンのみ。

Express with Management Tools
Management Studio が付属。

Express with Advanced Services
フルテキスト検索、Reporting Services 、Management Studio が付属。

Management Studio Express 
Management Studio本体。

接続文字列

・SQL Server認証
“Provider=SQLOLEDB;Data Source=xxx\SQLEXPRESS;Initial Catalog=my_database;User ID=sa;Password=1234;”

ODBCの場合
“DRIVER=ODBC Driver 11 for SQL Server;SERVER=xxx;DATABASE=xxx;uid=xxx;pwd=xxx;”

・Windows認証
“Provider=SQLOLEDB;Data Source=xxx\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;”

SSPI/True = Windows認証(SSPI推奨)
False = SQL Server認証

・接続ドライバー
SQLOLEDBは非推奨でMSOLEDBSQLが推奨されている。別途、OLE DB Driver for SQL Serverのインストールが必要。

コマンドラインの場合

ダウンロードする種類

64ビット環境
JPN\x64\sqlncli.msi
JPN\x64\SqlCmdLnUtils.msi
の2つを保存

32ビット環境
JPN\x86\sqlncli.msi
JPN\x86\SqlCmdLnUtils.msi
の2つを保存

C:\Program Files\Microsoft SQL Server\110\Tools\Binn
にパスを通す

コマンドでの利用方法

ヘルプ
sqlcmd /?

・接続
sqlcmd -S サーバ名 -d DB名 -U user -P pass
sqlcmd -E -S HOST名\sqlexpress
または、
sqlcmd -E -S localhost\sqlexpress

※接続と同時にクエリを渡さないと、対話状態になる。

・オプション

-E
Windows認証
-Q
インラインでSQL
-i
SQLのファイルを渡せる。

・データベース一覧の表示
1> select name from sys.databases
2> go

・データベースの指定
1> use my_database
2> go

・テーブル一覧の表示
1> select name from sys.tables
2> go

バッチファイルでバックアップ

バッチファイルには以下を記述。
sqlcmd -E -S host名\sqlexpress -i “sql.txt”

sql.txt

バックアップ情報

バックアップ復元

リモート接続の設定

・SQL Server Management Studio

セキュィティ>ログイン>sa>プロパティ>全般
パスワードを設定

セキュィティ>ログイン>sa>プロパティ>状態
ログインを有効化

インスタンス右クリック>プロパティ>セキュリティ
SQL Server認証とWindows 認証モード

インスタンス右クリック>プロパティ>接続
リモート接続を許可

・自動起動

services.msc
(もしなっていない場合)SQL Server Browserを自動へ

・SQL Server構成マネージャ
C:\Windows\System32\SQLServerManager15.msc

SQLEXPRESSのプロトコル
共有メモリ>有効
名前付きパイプ>無効
TCP/IP>有効

TCP/IPプロパティ>IPアドレス>IPALL
TCPポートを1433(任意)
TCP動的ポートを空白

・ファイアーウォールの設定(ローカルの場合)

firewall.cpl
操作>新しい規則>ポート指定>TCP>特定のローカルポート(1433)>接続を許可する>プライベートのみ選択

接続できない場合の対応の1つ

「SQL Server が存在しないか、アクセスが拒否されました」というエラーで接続できませんでした。

C:\Windows\System32\SQLServerManager15.msc
TCP/IPが有効になっているか

ポート番号はカンマ区切り。
インスタンス名はservices.mscから調べることができる。

サービスについて

SQL Server Browser
ネットワーク上のSQL Serverを自動検出。リモートで必要な場合も。

SQL Server CEIP サービス
カスタマーエクスペリエンス向上プログラム。

SQL Server VSS Writer
ボリューム シャドウ コピー サービス。

SQL Server エージェント
スケジュールされたジョブの実行。

弥生会計から利用

・SQL Server Management Studio

セキュリティ>ログイン>sa>全般
パスワードを指定

セキュリティ>ログイン>sa>状態
ログインを有効

インスタンス右クリック>プロパティ>セキュリティ
SQL Server認証とWindows認証モード

・弥生会計の設定

インストール後、弥生会計ではデータベースの新規作成を進めていき、サーバーの設定画面で、
保存場所:localhost\SQLEXPRESS
ユーザ名:sa
パスワード:1234
とすればOK。

・弥生会計のバックアップ

バックアップ中に、オペレーティングシステムエラー5のエラーがでる場合。

サービスからSQL Server (SQLEXPRESS)のプロパティを開いて、ログオンをローカルシステムに変更。対話サービスも許可。

・その他

Windowsの機能から.NET3.5.1のチェックを外すと起動しない。