Docker上でSQL Serverを動かす。とてもお手軽に利用できて良い。利用期間制限(170日？)があるけどDockerイメージをダウンロードし直せばリセットされる。
- Windows 10 Enterprise
- VirtualBox v5.1.14 r112924 (Qt5.6.2)
- Docker version 1.13.1, build 092cba3
- Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64)
+---------------------------------+
| Docker Container |
| (microsoft/mssql-server-linux) |
+---------------------------------+
| Linux VM(boot2docker) |
+---------------------------------+
| Windows 10 |
+---------------------------------+
VMのメモリを3.5GB以上にする(3.5GB以上ないとSQLServerが起動しない)
(NATの場合のみ)SQLServerの使用ポートは1433なので、localhostからVMにポートフォワーディングさせる
環境変数ACCEPT_EULAとSA_PASSWORDを指定する。
docker run \
-d \
-e 'ACCEPT_EULA=Y' \
-e 'SA_PASSWORD=yourStrong(!)Password' \
-p 1433:1433 \
--name sqlserver \
microsoft/mssql-server-linux:latest
起動に１分程かかる
Configuring Microsoft(R) SQL Server(R)...
Configuration complete.
This is an evaluation version. There are [170] days left in the evaluation period.
RegQueryValueEx HADR for key "Software\Microsoft\Microsoft SQL Server\MSSQL\MSSQLServer\HADR" failed.
2017-02-27 09:38:54.97 Server Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64)
Feb 13 2017 16:49:12
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)
2017-02-27 09:38:54.99 Server UTC adjustment: 0:00
2017-02-27 09:38:54.99 Server (c) Microsoft Corporation.
2017-02-27 09:38:55.00 Server All rights reserved.
2017-02-27 09:38:55.01 Server Server process ID is 4116.
2017-02-27 09:38:55.01 Server Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-02-27 09:38:55.02 Server Registry startup parameters:
-d C:\var\opt\mssql\data\master.mdf
-l C:\var\opt\mssql\data\mastlog.ldf
-e C:\var\opt\mssql\log\errorlog
2017-02-27 09:38:55.08 Server SQL Server detected 1 sockets with 1 cores per socket and 1 logical processors per socket, 1 total logical processors; using 1 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2017-02-27 09:38:55.11 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2017-02-27 09:38:55.12 Server Detected 3160 MB of RAM. This is an informational message; no user action is required.
2017-02-27 09:38:55.13 Server Using conventional memory in the memory manager.
Enabling HADR for this XCluster instance. May not work properly if prerequisites are not met.
2017-02-27 09:38:55.30 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2017-02-27 09:38:55.61 Server Buffer pool extension is already disabled. No action is necessary.
2017-02-27 09:38:55.86 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2017-02-27 09:38:55.87 Server Implied authentication manager initialization failed. Implied authentication will be disabled.
2017-02-27 09:38:56.01 Server The maximum number of dedicated administrator connections for this instance is '1'
2017-02-27 09:38:56.02 Server Node configuration: node 0: CPU mask: 0x0000000000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2017-02-27 09:38:56.06 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2017-02-27 09:38:56.11 Server In-Memory OLTP initialized on lowend machine.
2017-02-27 09:38:56.22 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2017-02-27 09:38:56.26 Server Query Store settings initialized with enabled = 1,
2017-02-27 09:38:56.30 Server Software Usage Metrics is disabled.
2017-02-27 09:38:56.35 spid6s Starting up database 'master'.
2017-02-27 09:38:57.13 spid6s 14 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2017-02-27 09:38:57.17 spid6s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2017-02-27 09:38:57.22 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2017-02-27 09:38:57.35 spid6s Buffer pool extension is already disabled. No action is necessary.
2017-02-27 09:38:57.37 spid6s Resource governor reconfiguration succeeded.
2017-02-27 09:38:57.38 spid6s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2017-02-27 09:38:57.40 spid6s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2017-02-27 09:38:57.67 spid6s SQL Trace ID 1 was started by login "sa".
2017-02-27 09:38:57.70 spid6s Server name is '70b7cee4bb67'. This is an informational message only. No user action is required.
2017-02-27 09:38:57.73 spid6s The NETBIOS name of the local node that is running the server is '70b7cee4bb67'. This is an informational message only. No user action is required.
2017-02-27 09:38:57.84 spid17s Password policy update was successful.
2017-02-27 09:38:57.86 spid20s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2017-02-27 09:38:57.88 spid9s Starting up database 'mssqlsystemresource'.
2017-02-27 09:38:57.90 spid20s Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2017-02-27 09:38:57.96 spid6s Starting up database 'msdb'.
2017-02-27 09:38:58.00 spid9s The resource database build version is 14.00.304. This is an informational message only. No user action is required.
2017-02-27 09:38:58.17 spid9s Starting up database 'model'.
2017-02-27 09:38:59.55 spid6s 4 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required.
2017-02-27 09:38:59.75 spid6s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required.
2017-02-27 09:38:59.80 spid6s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2017-02-27 09:39:00.04 spid9s Polybase feature disabled.
2017-02-27 09:39:00.05 spid9s Clearing tempdb database.
2017-02-27 09:39:02.44 spid17s A self-generated certificate was successfully loaded for encryption.
2017-02-27 09:39:02.49 spid17s Server is listening on [ 0.0.0.0 <ipv4> 1433].
2017-02-27 09:39:02.52 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2017-02-27 09:39:02.56 Server Dedicated admin connection support was established for listening locally on port 1434.
2017-02-27 09:39:02.60 spid17s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-02-27 09:39:02.61 spid9s Starting up database 'tempdb'.
2017-02-27 09:39:03.46 spid9s The tempdb database has 1 data file(s).
2017-02-27 09:39:03.48 spid20s The Service Broker endpoint is in disabled or stopped state.
2017-02-27 09:39:03.50 spid20s The Database Mirroring endpoint is in disabled or stopped state.
2017-02-27 09:39:03.55 spid20s Service Broker manager has started.
2017-02-27 09:39:03.63 spid6s Recovery is complete. This is an informational message only. No user action is required.
2017-02-27 09:39:12.36 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2017-02-27 09:39:12.41 spid51 Using 'xpsqlbot.dll' version '2016.140.304' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2017-02-27 09:39:12.76 spid52 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2017-02-27 09:39:12.98 spid52 Using 'xpstar.dll' version '2016.140.304' to execute extended stored procedure 'xp_regread'. This is an informational message only; no user action is required.
クライアント端末からsaユーザで接続する
sqlcmdの場合
sqlcmd -S 127.0.0.1 -U sa -P PASSWORD
SSMSの場合
既にSQLServerがインストール済みのDockerイメージを利用するため、本来インストール時に設定するSQLServerの既定の設定をカスタマイズすることはできない。そこは仕方が無いのでデータベース毎に設定する。特に照合順序は変更しないと日本語が扱えないので変更しておく。
当初、microsoft/mssql-server-linux のDockerイメージにはsqlcmdやbcp等のコマンドラインツールが含まれていなかったので自分でインストールしたりしていたが、Ver.ctp1-4から含まれる様になった。(ただし/opt/mssql-tools/binにパスが通っていないので自分で通す必要がある。)コンテナを作成する際に、デプロイ用のスクリプトを置いてあるホストPCのディレクトリをマウントしつつ、CMDを上書きしてスクリプトを叩くようにしてやれば、自動でのデプロイが可能。これで開発環境がDockerコマンド一発で手に入る。
# 自動デプロイの例
docker run \
-d \
-e 'ACCEPT_EULA=Y' \
-e 'SA_PASSWORD=yourStrong(!)Password' \
-p 1433:1433 \
-v /host/dir/:/root/ \
--name sqlserver \
microsoft/mssql-server-linux:latest \
/bin/bash ./entrypoint.sh