yuga/Microsoft SQL Server 環境の構築.md

## Microsoft SQL Server 環境の構築.md

      
    Raw
  

              Microsoft SQL Server 環境の構築.md
            
          
    Microsoft SQL Server 環境の構築


Server: Widnwos & SQL Server
Client: Linux

Microsoft SQL Server 2012 Express のインストール方法

Microsoft SQL Server 2012 の無料版を利用して、SQL Serverを構築する。

適当なWindows PCを用意する。

ダウンロード
以下のURLから、Express with Tools (SQLEXPRWT) のインストールパッケージを
ダウンロードする。

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) Express

http://www.microsoft.com/ja-jp/download/details.aspx?id=35579


インストール
インストール方法は、リンク先参照。
注意:

認証モードには「混合モード」を選択して「SQL Server認証」が有効になるようにする。

[SQL Server認証] 用のスパーユーザーである sa ユーザーを作成する。


管理者アカウントとして、SQL Serverサーバーを設定管理するWindowsユーザーアカウントを
追加しておく。
作成するインスタンス名はデフォルト (MSSQLSERVER) とする。


ネットワークの設定


SQL Server 構成マネージャー (SQL Server Configuration Manager) を起動する。


構成マネージャのツリーを [SQL Serer ネットワークの構成 > MSSQLSERVER のプロトコル] と開き、
[TCP/IP] を有効にする。


[TCP/IP] のプロパティを開き以下のように設定する。

[IP アドレス]    接続に使用するIPアドレス
[TCP ポート]     接続に使用するポート番号、デフォルトは1433
[TCP 動的ポート] 未設定
[アクティブ]     はい
[有効]           はい


サービス [SQL Server (MSSQLSERVER)] を再起動する。


Windows ファイアウォールの [受信の規則] の設定で、外部から1443ポートへの接続を許可する。


Microsoft ODBC Driver 11 for SQL Server (msodbcsql) のインストール方法

Linux 上で実行されるネイティブ アプリケーション(C/C++)から、
SQL Server 2008、SQL Server 2008 R2、および SQL Server 2012 に接続できるようにする。
以下のディストリビューション向けに、バイナリが提供されている。


64 ビット Red Hat Enterprise Linux 5 および 6 用

http://go.microsoft.com/fwlink/?LinkId=267321


64 ビット SUSE Linux Enterprise 11 Service Pack 2 用

http://go.microsoft.com/fwlink/?LinkId=264916


以降、Cent OS 6.4 環境を例として用いる。
###システム要件
必要なライブラリがインストール済みのいずれかのOS


Red Hat Enterprise Linux 5 (CentOS 5.x) x86_64

glibc
libgcc
libstdc++
e2fsprogs-libs
krb5-libs
openssl


Red Hat Enterprise Linux 6 (CentOS 6.x) x86_64

glibc
libgcc
libstdc++
libuuid
krb5-libs
openssl


SUSE Linux Enterprise 11 Service Pack 2  x86_64

glibc
libstdc++46
libgcc46
libuuid1
krb5
libopenssl0_9_8


ドライバマネージャー


UnixODBC 2.3.0  x86_64
他のバージョンを使用する場合、msodbcsqlのinstall.shに記述されたUnixODBCのバージョン番号を変更する。
以下のオプションでビルド:
$ export CPPFLAGS="-DSIZEOF_LONG_INT=8"
$ ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc \
    --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 \
    --with-iconv-ucode-enc=UTF16LE
$ make
$ su
# make install

msodbcsqlが前提としているライブラリ名にあわせてsymlinkを作成:
# cd /usr/lib64/
# ln -s libodbc.so.2.0.0     libodbc.so.1
# ln -s libodbcinst.so.2.0.0 libodbcinst.so.1


インストール手順


ファイルの展開

$ tar xf msodbcsql-11.0.2270.0.tar.gz
$ cd msodbcsql-11.0.2270.0


環境の確認

$ ./install.sh verify


インストール

$ ./install.sh install

以下のように表示されれば成功:
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft ODBC Driver 11 for SQL Server registered ................... INSTALLED

インストールの確認


ドライバを直接使用した接続の確認

$ sqlcmd -S <SQL Server> -U <user> -P <password>


システムファイル(odbcinst.ini)に登録されたドライバを確認する

$ cat /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1


ユーザーDSN(~/.odbc.ini)を作成する。
(注: MSのドライバはユーザーDSNでPortキーワードを使用できない)

$ cat > ~/.odbc.ini <<EOS
[testdb]
Driver = ODBC Driver 11 for SQL Server
Server = tcp:192.168.56.102,1433
# Server = [protocol:]server[,port]


UnixODBCを使用した接続の確認

$ sqlcmd -D -S testdb -U <user> -P <password>

または
$ isql testdb <user> <passwod>

Haskellからの確認


以下のパッケージをインストールする


HDBC
HDBC-odbc


テスト用データベースを構築する

$ ./runCreateDB.sh -D -S testdb -U sa -P <password>
$ ./runCreate.sh -D -S testdb -U test -P test


GHCi を使って接続を行う

$ ghci
GHCi, version 7.6.3: http://www.haskell.org/ghc/  :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.
Prelude> :set prompt "ghci> "
ghci> :m + Control.Arrow Database.HDBC Database.HDBC.ODBC
ghci> conn <- connectODBC "DSN=testdb;UID=test;PWD=test"
ghci> rows <- quickQuery conn "select * from TEST.test_table2" []
ghci> mapM_ (\x -> mapM_ (putStr . uncurry (:) . (id *** fromSql)) (zip "[,," x) >> putStrLn "]") rows
[実験,実験,実験              ]
[鷗鄧,鷗鄧,鷗鄧              ]
[𦿶丈,𦿶丈,𦿶丈             ]
ghci> 


## runCreate.sh
#! /bin/sh

PATH='/usr/bin:/bin'

usage() {
    echo "Usage: ./runCreate.sh [-D] -S <server> -U <user> -P <pass>"
    echo "  -S <server>  Server or DSN if -D is provided"
    echo "               examples:"
    echo "                 -S 127.0.0.1"
    echo "                 -S 127.0.0.1\instanceA"
    echo "                 -S 127.0.0.1,1433"
    echo "                 -D -S testdb"
    echo "  -U <user>    Login ID"
    echo "  -P <pass>    Password"
    exit 1
}

DSN=0
SERVER=
PORT=1433
USER=
PASS=

eval set -- "`getopt DS:P:U:P: $*`"
for opt; do
    case $opt in
        -D )
            DSN=1;;
        -S )
            SERVER=$2;;
        -U )
            USER=$2;;
        -P )
            PASS=$2;;
    esac
    shift
done

if [ -z "$SERVER" -o -z "$USER" -o -z "$PASS" ]; then
    usage
fi

create0="
CREATE TABLE [TEST].[test_table0] (
  [foo]      [smallint] NOT NULL ,
  [foo_bar]  [integer]  NOT NULL ,
  [par_ent]  [integer]  NOT NULL ,
  [bar]      [date] ,
  [bar_baz]  [text] ,
  [baz]      [VARCHAR] (10) ,
  CONSTRAINT [pk_test_table0] PRIMARY KEY([foo_bar])
)
"

create1="
CREATE TABLE [TEST].[test_table1] (
  [foo]      [integer]  NOT NULL ,
  CONSTRAINT [pk_test_table1] PRIMARY KEY ([foo])
)
"

create2="
CREATE TABLE [TEST].[test_table2] (
  [x] [ntext] ,
  [y] [nvarchar] (16) NOT NULL ,
  [z] [nchar]    (16) NOT NULL
)
"

if [ "$DSN" -eq 0 ]; then
    SQLCMD="sqlcmd -S $SERVER -U $USER -P $PASS"
else
    SQLCMD="sqlcmd -D -S $SERVER -U $USER -P $PASS"
fi

#set -x

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [testdb]
GO
if not exists (select * from sys.schemas where name = N'TEST')
BEGIN
    EXEC ('CREATE SCHEMA [TEST]')
END
GO
EOS

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [testdb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table0]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TEST].[test_table0]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TEST].[test_table1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TEST].[test_table2]
GO
$create0
GO
$create1
GO
$create2
GO
EOS

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [testdb]
GO
INSERT INTO [TEST].[test_table1] ([foo]) VALUES (1)
INSERT INTO [TEST].[test_table1] ([foo]) VALUES (2)
GO
INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'実験', N'実験', N'実験')
INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'鷗鄧', N'鷗鄧', N'鷗鄧')
INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'𦿶丈', N'𦿶丈', N'𦿶丈')
GO
EOS

## runCreateDB.sh
#! /bin/sh

PATH='/usr/bin:/bin'

usage() {
    echo "Usage: ./runCreateDB.sh [-D] -S <server> -U <user> -P <pass>"
	echo "  -S <server>  Server or DSN if -D is provided"
	echo "               examples:"
	echo "                 -S 127.0.0.1"
	echo "                 -S 127.0.0.1\instanceA"
	echo "                 -S 127.0.0.1,1433"
	echo "                 -D -S testdb"
	echo "  -U <user>    Login ID"
	echo "  -P <pass>    Password"
	exit 1
}

DSN=0
SERVER=
PORT=1433
USER=
PASS=

eval set -- "`getopt DS:P:U:P: $*`"
for opt; do
    case $opt in
    	-D )
            DSN=1;;
    	-S )
            SERVER=$2;;
        -U )
            USER=$2;;
        -P )
            PASS=$2;;
    esac
    shift
done

if [ -z "$SERVER" -o -z "$USER" -o -z "$PASS" ]; then
    usage
fi

create="
-- create a database with name "testdb"
IF DB_ID(N'testdb') IS NULL
CREATE DATABASE [testdb]
GO

-- Create a login with name 'test' and password 'test'
USE [master]
GO
if not exists (select * from sys.server_principals where name = N'test')
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[testdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Create a user with name 'test' and map it to a login
USE [testdb]
GO
if not exists (select * from sys.database_principals where name = N'test')
CREATE USER [test] FOR LOGIN [test]
GO
EXEC sp_addrolemember N'db_datareader', N'test'
EXEC sp_addrolemember N'db_datawriter', N'test'
EXEC sp_addrolemember N'db_ddladmin', N'test'
GO

-- Grant permission to user "test"
GRANT EXECUTE TO [test]
GO
"

if [ "$DSN" -eq 0 ]; then
    SQLCMD="sqlcmd -S $SERVER -U $USER -P $PASS"
else
    SQLCMD="sqlcmd -D -S $SERVER -U $USER -P $PASS"
fi

#set -x

$SQLCMD > /dev/null <<EOS
:error STDERR
$create
EOS

## runDrop.sh
#! /bin/sh

PATH='/usr/bin:/bin'

usage() {
    echo "Usage: ./runDrop.sh [-D] -S <server> -U <user> -P <pass>"
    echo "  -S <server>  Server or DSN if -D is provided"
    echo "               examples:"
    echo "                 -S 127.0.0.1"
    echo "                 -S 127.0.0.1\instanceA"
    echo "                 -S 127.0.0.1,1433"
    echo "                 -D -S testdb"
    echo "  -U <user>    Login ID"
    echo "  -P <pass>    Password"
    exit 1
}

DSN=0
SERVER=
PORT=1433
USER=
PASS=

eval set -- "`getopt DS:P:U:P: $*`"
for opt; do
    case $opt in
        -D )
            DSN=1;;
        -S )
            SERVER=$2;;
        -U )
            USER=$2;;
        -P )
            PASS=$2;;
    esac
    shift
done

if [ -z "$SERVER" -o -z "$USER" -o -z "$PASS" ]; then
    usage
fi

#set -x

if [ "$DSN" -eq 0 ]; then
    SQLCMD="sqlcmd -S $SERVER -U $USER -P $PASS"
else
    SQLCMD="sqlcmd -D -S $SERVER -U $USER -P $PASS"
fi

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [testdb]
GO
DROP TABLE [TEST].[test_table2]
DROP TABLE [TEST].[test_table1]
DROP TABLE [TEST].[test_table0]
GO
EOS

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [testdb]
GO
if exists (select * from sys.schemas where name = N'TEST')
BEGIN
    EXEC ('DROP SCHEMA [TEST]')
END
GO
EOS

## runDropDB.sh
#! /bin/sh

PATH='/usr/bin:/bin'

usage() {
    echo "Usage: ./runDropDB.sh [-D] -S <server> -U <user> -P <pass>"
    echo "  -S <server>  Server or DSN if -D is provided"
    echo "               examples:"
    echo "                 -S 127.0.0.1"
    echo "                 -S 127.0.0.1\instanceA"
    echo "                 -S 127.0.0.1,1433"
    echo "                 -D -S testdb"
    echo "  -U <user>    Login ID"
    echo "  -P <pass>    Password"
    exit 1
}

DSN=0
SERVER=
PORT=1433
USER=
PASS=

eval set -- "`getopt DS:P:U:P: $*`"
for opt; do
    case $opt in
        -D )
            DSN=1;;
        -S )
            SERVER=$2;;
        -U )
            USER=$2;;
        -P )
            PASS=$2;;
    esac
    shift
done

if [ -z "$SERVER" -o -z "$USER" -o -z "$PASS" ]; then
    usage
fi

#set -x

if [ "$DSN" -eq 0 ]; then
    SQLCMD="sqlcmd -S $SERVER -U $USER -P $PASS"
else
    SQLCMD="sqlcmd -D -S $SERVER -U $USER -P $PASS"
fi

$SQLCMD > /dev/null <<EOS
:error STDERR
USE [master]
GO

IF DB_ID(N'testdb') IS NOT NULL
DROP DATABASE [testdb]
GO

if exists (select * from sys.server_principals where name = N'test')
DROP LOGIN test
GO
EOS
	#! /bin/sh

	PATH='/usr/bin:/bin'

	usage() {
	echo "Usage: ./runCreate.sh [-D] -S <server> -U <user> -P <pass>"
	echo " -S <server> Server or DSN if -D is provided"
	echo " examples:"
	echo " -S 127.0.0.1"
	echo " -S 127.0.0.1\instanceA"
	echo " -S 127.0.0.1,1433"
	echo " -D -S testdb"
	echo " -U <user> Login ID"
	echo " -P <pass> Password"
	exit 1
	}

	DSN=0
	SERVER=
	PORT=1433
	USER=
	PASS=

	eval set -- "`getopt DS:P:U:P: $*`"
	for opt; do
	case $opt in
	-D )
	DSN=1;;
	-S )
	SERVER=$2;;
	-U )
	USER=$2;;
	-P )
	PASS=$2;;
	esac
	shift
	done

	if [ -z "$SERVER" -o -z "$USER" -o -z "$PASS" ]; then
	usage
	fi

	create0="
	CREATE TABLE [TEST].[test_table0] (
	[foo] [smallint] NOT NULL ,
	[foo_bar] [integer] NOT NULL ,
	[par_ent] [integer] NOT NULL ,
	[bar] [date] ,
	[bar_baz] [text] ,
	[baz] [VARCHAR] (10) ,
	CONSTRAINT [pk_test_table0] PRIMARY KEY([foo_bar])
	)
	"

	create1="
	CREATE TABLE [TEST].[test_table1] (
	[foo] [integer] NOT NULL ,
	CONSTRAINT [pk_test_table1] PRIMARY KEY ([foo])
	)
	"

	create2="
	CREATE TABLE [TEST].[test_table2] (
	[x] [ntext] ,
	[y] [nvarchar] (16) NOT NULL ,
	[z] [nchar] (16) NOT NULL
	)
	"

	if [ "$DSN" -eq 0 ]; then
	SQLCMD="sqlcmd -S $SERVER -U $USER -P $PASS"
	else
	SQLCMD="sqlcmd -D -S $SERVER -U $USER -P $PASS"
	fi

	#set -x

	$SQLCMD > /dev/null <<EOS
	:error STDERR
	USE [testdb]
	GO
	if not exists (select * from sys.schemas where name = N'TEST')
	BEGIN
	EXEC ('CREATE SCHEMA [TEST]')
	END
	GO
	EOS

	$SQLCMD > /dev/null <<EOS
	:error STDERR
	USE [testdb]
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table0]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [TEST].[test_table0]
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [TEST].[test_table1]
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'[TEST].[test_table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [TEST].[test_table2]
	GO
	$create0
	GO
	$create1
	GO
	$create2
	GO
	EOS

	$SQLCMD > /dev/null <<EOS
	:error STDERR
	USE [testdb]
	GO
	INSERT INTO [TEST].[test_table1] ([foo]) VALUES (1)
	INSERT INTO [TEST].[test_table1] ([foo]) VALUES (2)
	GO
	INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'実験', N'実験', N'実験')
	INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'鷗鄧', N'鷗鄧', N'鷗鄧')
	INSERT INTO [TEST].[test_table2] ([x], [y], [z]) VALUES (N'𦿶丈', N'𦿶丈', N'𦿶丈')
	GO
	EOS