ものぐさ日記(仮)

特にテーマはありません。仕事のこと、趣味のこと、思いついたこと、だらだら綴っていきます。

【docker】超高速 SQL Server 環境構築

題名は言い過ぎ;

 

演習として Docker Desktop for Mac 環境下に SQL Server 2017 on Linux のコンテナを作成し、AdventureWorks DB を構築してみたので、手順をメモとして残す。

docker バージョン確認(docker version)


azurite:~ hoge$ docker version
Client: Docker Engine - Community
 Version:           18.09.1
 API version:       1.39
 Go version:        go1.10.6
 Git commit:        4c52b90
 Built:             Wed Jan  9 19:33:12 2019
 OS/Arch:           darwin/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          18.09.1
  API version:      1.39 (minimum version 1.12)
  Go version:       go1.10.6
  Git commit:       4c52b90
  Built:            Wed Jan  9 19:41:49 2019
  OS/Arch:          linux/amd64
  Experimental:     false

 

docker イメージ取得(docker image pull)

Docker Hub を確認し、タグを明示的に指定して、イメージを取得した。


azurite:~ hoge$ docker image pull microsoft/mssql-server-linux:2017-CU12
2017-CU12: Pulling from microsoft/mssql-server-linux
59ab41dd721a: Pull complete
57da90bec92c: Pull complete
06fe57530625: Pull complete
5a6315cba1ff: Pull complete
739f58768b3f: Pull complete
0b751601bca3: Pull complete
bcf04a22644a: Pull complete
dc7745d9cb0c: Pull complete
ffbc09bb4f1a: Pull complete
Digest: sha256:19b9392f035fc9f82b77f6833d1490bca8cb041b445cd451de0d1f1f3efe70e8
Status: Downloaded newer image for microsoft/mssql-server-linux:2017-CU12

 

docker イメージ一覧(docker image ls)


azurite:~ hoge$ docker image ls
REPOSITORY                     TAG                 IMAGE ID            CREATED             SIZE
centos                         centos7             1e1148e4cc2c        6 weeks ago         202MB
microsoft/mssql-server-linux   2017-CU12           4095d6d460cd        2 months ago        1.32GB

 

docker コンテナ作成(docker container run)

利便性の為、コンテナの名前は"sqlserver"にする。


azurite:~ hoge$ docker container run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd' -p 1433:1433 --name sqlserver -d microsoft/mssql-server-linux:2017-CU12
0784e915d43df0eccf04837b2d79658995557b634765581bc9ac84f98b045238

 

docker コンテナ一覧(docker container ls)


azurite:~ hoge$ docker container ls
CONTAINER ID        IMAGE                                    COMMAND                  CREATED              STATUS              PORTS  NAMES
0784e915d43d        microsoft/mssql-server-linux:2017-CU12   "/opt/mssql/bin/sqls…"   About a minute ago   Up About a minute   0.0.0.0:1433->1433/tcp  sqlserver
c53f66d4b5e5        centos:centos7                           "/bin/bash"              32 hours ago         Up 32 hours  centos7

 

サンプルDB(AdventureWorks2017)ダウンロード

curl を使用し、AdventureWorks2017.bak をダウンロードする。


azurite:~ hoge$ curl -O -L https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   612    0   612    0     0    858      0 --:--:-- --:--:-- --:--:--   858
100 47.9M  100 47.9M    0     0  2782k      0  0:00:17  0:00:17 --:--:-- 3902k

 

docker コンテナにデータコピー(docker container cp)


azurite:~ hoge$ docker container cp ./AdventureWorks2017.bak sqlserver:/AdventureWorks2017.bak

 

docker コンテナで bash を開始し、サンプルDBをリストア

この項、長いし色んなコマンドが混在していて、分かり辛いね;


azurite:~ hoge$ docker exec -it sqlserver /bin/bash
root@0784e915d43d:/# mkdir /var/opt/mssql/backup
root@0784e915d43d:/# mv ./AdventureWorks2017.bak /var/opt/mssql/backup
root@0784e915d43d:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd'
1> RESTORE FILELISTONLY
2> FROM DISK = '/var/opt/mssql/backup/AdventureWorks2017.bak'
3> GO
LogicalName                                                                                                                      PhysicalName                                                                                          Type FileGroupName                                                                          Size                 MaxSize              FileId               CreateLSN               DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint                              SnapshotUrl
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AdventureWorks2017                                                                                                               C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017RTM\MSSQL\DATA\AdventureWorks2017.mdf                                                                                          D    PRIMARY                                                                                     276824064       35184372080640                    1             0                           0 733940A8-D019-4DC5-80F8-13E869A504EC                           0                           0            215285760             512           1 NULL                                                           0 00000000-0000-0000-0000-000000000000          0      1 NULL                                       NULL
AdventureWorks2017_log                                                                                                           C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017RTM\MSSQL\DATA\AdventureWorks2017_log.ldf                                                                                          L    NULL                                                                                      75497472        2199023255552                    2             0                           0 B166C891-E43F-42DA-87FC-8D7F34022B35                           0                           0     0             512           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0      1 NULL                                       NULL

(2 rows affected)
1> RESTORE DATABASE AdventureWorks FROM DISK = '/var/opt/mssql/backup/AdventureWorks2017.bak'
2> WITH MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks2017_Data.mdf',
3> MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/data/AdventureWorks2017_Log.ldf'
4> GO
Processed 26280 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 3 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 26283 pages in 0.441 seconds (465.601 MB/sec).
1> SELECT TOP 5 * FROM AdventureWorks.Person.Address
2> GO
AddressID   AddressLine1                                                 AddressLine2                                                 City               StateProvinceID PostalCode      SpatialLocation rowguid                              ModifiedDate
----------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ --------------- --------------- --------------- ------------------------------------ -----------------------
          1 1970 Napa Ct.                                                NULL                                                         Bothell                            79 98011           0xE6100000010CA 9AADCB0D-36CF-483F-84D8-585C2D4EC6E9 2007-12-04 00:00:00.000
          2 9833 Mt. Dias Blv.                                           NULL                                                         Bothell                            79 98011           0xE6100000010CD 32A54B9E-E034-4BFB-B573-A71CDE60D8C0 2008-11-30 00:00:00.000
          3 7484 Roundtree Drive                                         NULL                                                         Bothell                            79 98011           0xE6100000010C1 4C506923-6D1B-452C-A07C-BAA6F5B142A4 2013-03-07 00:00:00.000
          4 9539 Glenside Dr                                             NULL                                                         Bothell                            79 98011           0xE6100000010C8 E5946C78-4BCC-477F-9FA1-CC09DE16A880 2009-02-03 00:00:00.000
          5 1226 Shoe St.                                                NULL                                                         Bothell                            79 98011           0xE6100000010C6 FBAFF937-4A97-4AF0-81FD-B849900E9BB0 2008-12-19 00:00:00.000

(5 rows affected)

sqlcmd で SELECT文を実行し、正しくリストアできていることが確認できた。

また、以下のように 別端末から SQL Server Management Studio で接続し、データが入っていることも確認できた。

f:id:michio_s:20190118033056p:plain

 

おわり