Skip to content

Instantly share code, notes, and snippets.

@koirand

koirand/00_mssql-docker.md

Last active March 19, 2017 14:15
Show Gist options
  • Save koirand/59ba71146a71874c41fbad43fab29e89 to your computer and use it in GitHub Desktop.
Save koirand/59ba71146a71874c41fbad43fab29e89 to your computer and use it in GitHub Desktop.
Download ZIP
Microsoft SQL Server on Docker のコンテナ作成と自動デプロイ
Raw
00_mssql-docker.md

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の設定

VMのメモリを3.5GB以上にする(3.5GB以上ないとSQLServerが起動しない)
image

(NATの場合のみ)SQLServerの使用ポートは1433なので、localhostからVMにポートフォワーディングさせる   image

コンテナ作成

環境変数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の場合
image

照合順序の設定

既にSQLServerがインストール済みのDockerイメージを利用するため、本来インストール時に設定するSQLServerの既定の設定をカスタマイズすることはできない。そこは仕方が無いのでデータベース毎に設定する。特に照合順序は変更しないと日本語が扱えないので変更しておく。 image

コンテナ作成と同時に自動でデプロイを行う

当初、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

参考

Raw
deploy.sh
#!/bin/bash
# Wait for SQLServer to start up
sleep 90s
# Exit if already deploied
test -f /etc/deploy_finished && exit
# Add sqlserver tools dir to $PATH
PATH=$PATH:/opt/mssql-tools/bin
sqlcmd -S 127.0.0.1 -U sa -P your_password -i CREATE_DATABASE.sql
date > /etc/deploy_finished
Raw
entrypoint.sh
#!/bin/bash
cd /root/
/bin/bash ./deploy.sh & /opt/mssql/bin/sqlservr.sh
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment