【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 で接続し、データが入っていることも確認できた。
おわり