podman を用いて SQL Server を導入した際のメモ
podman を用いて Linux 版 SQL Server 環境を構築した際のメモを整理します。
（殆ど同じ会社の方に教えていただいた情報ですが…）
ここでは JBoss EAP から SQL Server へ接続する前提で環境を用意します。
インストーラを用いて Windows 版の SQL Server を導入するのは以前の会社でかなり実施しましたが、docker や podman を用いると1行のコマンドを実行するだけで SQL Server があっと言う間に導入できてしまうのに目から鱗でした。
あと、以下の Microsoft さんのページも参考になるかと思います。
Microsoft SQL Server by Microsoft | Docker Hub
- SQL Server コンテナイメージの入手／起動
$ podman run --name demo_mssql2019 -p 1433:1433 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<任意のパスワード>" mcr.microsoft.com/mssql/server:2019-CU15-ubuntu-20.04
Trying to pull mcr.microsoft.com/mssql/server:2019-CU15-ubuntu-20.04...
Getting image source signatures
Copying blob 2e5e63d166b4 done
Copying blob dc034f624aa1 done
Copying blob cafda714f10f done
Copying blob ea362f368469 done
Copying blob c6af4ce68233 done
Copying config d78e982c2f done
Writing manifest to image destination
Storing signatures
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2022-08-08 07:03:06.61 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2022-08-08 07:03:06.82 Server Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.
2022-08-08 07:03:06.90 Server Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2022-08-08 07:03:06.94 Server Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2022-08-08 07:03:06.99 Server Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2022-08-08 07:03:07.04 Server Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2022-08-08 07:03:07.10 Server Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2022-08-08 07:03:07.16 Server Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2022-08-08 07:03:07.21 Server Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2022-08-08 07:03:07.27 Server Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2022-08-08 07:03:07.33 Server Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2022-08-08 07:03:07.55 Server Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
Jan 12 2022 22:30:08
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>
2022-08-08 07:03:07.59 Server UTC adjustment: 0:00
2022-08-08 07:03:07.61 Server (c) Microsoft Corporation.
2022-08-08 07:03:07.62 Server All rights reserved.
2022-08-08 07:03:07.64 Server Server process ID is 388.
2022-08-08 07:03:07.66 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2022-08-08 07:03:07.69 Server Registry startup parameters:
-d /var/opt/mssql/data/master.mdf
-l /var/opt/mssql/data/mastlog.ldf
-e /var/opt/mssql/log/errorlog
2022-08-08 07:03:07.74 Server SQL Server detected 1 sockets with 6 cores per socket and 6 logical processors per socket, 6 total logical processors; using 6 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2022-08-08 07:03:07.81 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2022-08-08 07:03:07.85 Server Detected 7957 MB of RAM. This is an informational message; no user action is required.
2022-08-08 07:03:07.92 Server Using conventional memory in the memory manager.
2022-08-08 07:03:07.98 Server Warning: Timer device resolution is greater than required. Required: 1. Found: 10.
2022-08-08 07:03:08.01 Server Page exclusion bitmap is enabled.
2022-08-08 07:03:08.09 Server Buffer pool extension is not supported on Linux platform.
2022-08-08 07:03:08.15 Server Buffer Pool: Allocating 2097152 bytes for 1050217 hashPages.
2022-08-08 07:03:08.98 Server Buffer pool extension is already disabled. No action is necessary.
2022-08-08 07:03:11.33 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2022-08-08 07:03:11.50 Server Query Store settings initialized with enabled = 1,
2022-08-08 07:03:11.56 Server The maximum number of dedicated administrator connections for this instance is '1'
2022-08-08 07:03:11.56 Server Node configuration: node 0: CPU mask: 0x000000000000003f:0 Active CPU mask: 0x000000000000003f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2022-08-08 07:03:11.70 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.
2022-08-08 07:03:11.80 Server In-Memory OLTP initialized on lowend machine.
2022-08-08 07:03:11.91 Server CLR version v4.0.30319 loaded.
2022-08-08 07:03:11.92 Server [INFO] Created Extended Events session 'hkenginexesession'
2022-08-08 07:03:11.96 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.
ForceFlush is enabled for this instance.
2022-08-08 07:03:12.12 Server Total Log Writer threads: 2. This is an informational message; no user action is required.
2022-08-08 07:03:12.20 Server clflushopt is selected for pmem flush operation.
2022-08-08 07:03:12.23 Server Software Usage Metrics is disabled.
2022-08-08 07:03:12.32 spid9s [1]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:03:12.36 spid9s Starting up database 'master'.
ForceFlush feature is enabled for log durability.
2022-08-08 07:03:12.80 Server Common language runtime (CLR) functionality initialized.
2022-08-08 07:03:12.89 spid9s Converting database 'master' from version 897 to the current version 904.
2022-08-08 07:03:12.93 spid9s Database 'master' running the upgrade step from version 897 to version 898.
2022-08-08 07:03:12.97 spid9s Database 'master' running the upgrade step from version 898 to version 899.
2022-08-08 07:03:13.06 spid9s Database 'master' running the upgrade step from version 899 to version 900.
2022-08-08 07:03:13.14 spid9s Database 'master' running the upgrade step from version 900 to version 901.
2022-08-08 07:03:13.19 spid9s Database 'master' running the upgrade step from version 901 to version 902.
2022-08-08 07:03:13.25 spid9s Database 'master' running the upgrade step from version 902 to version 903.
2022-08-08 07:03:13.29 spid9s Database 'master' running the upgrade step from version 903 to version 904.
2022-08-08 07:03:13.79 spid9s Resource governor reconfiguration succeeded.
2022-08-08 07:03:13.83 spid9s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2022-08-08 07:03:13.91 spid9s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2022-08-08 07:03:14.09 spid9s SQL Trace ID 1 was started by login "sa".
2022-08-08 07:03:14.15 spid35s Password policy update was successful.
2022-08-08 07:03:14.20 spid9s Server name is 'aa665da98ac0'. This is an informational message only. No user action is required.
2022-08-08 07:03:14.36 spid37s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2022-08-08 07:03:14.36 spid9s [4]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:03:14.37 spid12s [32767]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:03:14.46 spid37s 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.
2022-08-08 07:03:14.48 spid9s Starting up database 'msdb'.
2022-08-08 07:03:14.56 spid12s Starting up database 'mssqlsystemresource'.
2022-08-08 07:03:14.64 spid35s A self-generated certificate was successfully loaded for encryption.
2022-08-08 07:03:14.78 spid12s The resource database build version is 15.00.4198. This is an informational message only. No user action is required.
2022-08-08 07:03:14.84 spid35s Server is listening on [ 'any' <ipv6> 1433].
2022-08-08 07:03:14.90 spid35s Server is listening on [ 'any' <ipv4> 1433].
2022-08-08 07:03:14.99 Server Server is listening on [ ::1 <ipv6> 1434].
2022-08-08 07:03:15.05 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2022-08-08 07:03:15.09 Server Dedicated admin connection support was established for listening locally on port 1434.
2022-08-08 07:03:15.09 spid9s The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes. 3072 bytes at offset 50176 in file /var/opt/mssql/data/MSDBLog.ldf will be written.
2022-08-08 07:03:15.14 spid12s [3]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:03:15.23 spid35s Server is listening on [ ::1 <ipv6> 1431].
2022-08-08 07:03:15.31 spid12s Starting up database 'model'.
2022-08-08 07:03:15.36 spid35s Server is listening on [ 127.0.0.1 <ipv4> 1431].
2022-08-08 07:03:15.42 spid35s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2022-08-08 07:03:15.46 spid9s Converting database 'msdb' from version 897 to the current version 904.
2022-08-08 07:03:15.56 spid9s Database 'msdb' running the upgrade step from version 897 to version 898.
2022-08-08 07:03:15.61 spid12s The tail of the log for database model is being rewritten to match the new sector size of 4096 bytes. 512 bytes at offset 73216 in file /var/opt/mssql/data/modellog.ldf will be written.
2022-08-08 07:03:15.65 spid9s Database 'msdb' running the upgrade step from version 898 to version 899.
2022-08-08 07:03:15.78 spid9s Database 'msdb' running the upgrade step from version 899 to version 900.
2022-08-08 07:03:15.81 spid12s Converting database 'model' from version 897 to the current version 904.
2022-08-08 07:03:15.83 spid12s Database 'model' running the upgrade step from version 897 to version 898.
2022-08-08 07:03:15.87 spid9s Database 'msdb' running the upgrade step from version 900 to version 901.
2022-08-08 07:03:15.88 spid12s Database 'model' running the upgrade step from version 898 to version 899.
2022-08-08 07:03:15.94 spid9s Database 'msdb' running the upgrade step from version 901 to version 902.
2022-08-08 07:03:15.99 spid12s Database 'model' running the upgrade step from version 899 to version 900.
2022-08-08 07:03:16.06 spid12s Database 'model' running the upgrade step from version 900 to version 901.
2022-08-08 07:03:16.12 spid12s Database 'model' running the upgrade step from version 901 to version 902.
2022-08-08 07:03:16.17 spid12s Database 'model' running the upgrade step from version 902 to version 903.
2022-08-08 07:03:16.22 spid12s Database 'model' running the upgrade step from version 903 to version 904.
2022-08-08 07:03:16.43 spid12s Clearing tempdb database.
2022-08-08 07:03:16.74 spid12s [2]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:03:16.76 spid12s Starting up database 'tempdb'.
2022-08-08 07:03:17.00 spid12s The tempdb database has 1 data file(s).
2022-08-08 07:03:17.03 spid37s The Service Broker endpoint is in disabled or stopped state.
2022-08-08 07:03:17.08 spid37s The Database Mirroring endpoint is in disabled or stopped state.
2022-08-08 07:03:17.13 spid9s Database 'msdb' running the upgrade step from version 902 to version 903.
2022-08-08 07:03:17.15 spid37s Service Broker manager has started.
2022-08-08 07:03:17.18 spid9s Database 'msdb' running the upgrade step from version 903 to version 904.
2022-08-08 07:03:17.98 spid9s Recovery is complete. This is an informational message only. No user action is required.
2022-08-08 07:03:18.77 spid48s The default language (LCID 0) has been set for engine and full-text services.
2022-08-08 07:03:19.33 spid48s The tempdb database has 6 data file(s).
2022-08-08 07:08:22.57 spid56 [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2022-08-08 07:08:22.59 spid56 Starting up database 'test'.
2022-08-08 07:08:22.66 spid56 Parallel redo is started for database 'test' with worker pool size [3].
2022-08-08 07:08:22.77 spid56 Parallel redo is shutdown for database 'test' with worker pool size [3].
2022-08-08 07:09:57.96 spid59 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2022-08-08 07:09:58.07 spid59 Using 'xplog70.dll' version '2019.150.4198' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
- SQL Server が起動したら他の端末から sqlcmd を起動し、動作確認用の test データベースを作成
$ podman exec -it demo_mssql2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <指定したパスワード>
1> create database test
2> go
1> SELECT name, database_id, create_date FROM sys.databases
2> go
name database_id create_date
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------------------
master 1 2003-04-08 09:13:36.390
tempdb 2 2022-08-08 07:03:17.027
model 3 2003-04-08 09:13:36.390
msdb 4 2022-01-12 22:54:24.927
test 5 2022-08-08 07:08:22.333
(5 rows affected)
- test データベースへ切り替えて、動作確認用の book テーブルを作成
（補足）ここでは book テーブルは利用していないため、この手順は省略可能です。
1> use test
2> go
Changed database context to 'test'.
2> CREATE TABLE Book (id INT PRIMARY KEY, title varchar(100), author varchar(100))
3> go
1> select * from book
2> go
id title author
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
(0 rows affected)
1> quit
$
- 以下のページより SQL Server 用 JDBC ドライバ（Microsoft JDBC Driver 10.2 for SQL Server）を入手
ダウンロードした zip ファイルを任意のディレクトリで展開し、以下の jar ファイルを任意のディレクトリ（/usr/share/javaなど）へコピー
mssql-jdbc-10.2.1.jre11.jar
mssql-jdbc-10.2.1.jre17.jar
mssql-jdbc-10.2.1.jre8.jar
- JBoss EAP の CLI から SQL Server 用 JDBC ドライバモジュールをコアモジュールとして導入（dependenciesパラメータが正しくないかも…）
（補足）以下の手順では Java 11 用の JDBC ドライバを登録していますが、環境に応じて読み替えてください。
$JBOSS_HOME/bin/jboss-cli.sh -c
[standalone@localhost:9990 /] module add --name=com.microsoft --resources=/usr/share/java/mssql-jdbc-10.2.1.jre11.jar --dependencies=javaee.api,sun.jdk,ibm.jdk,javax.api,javax.transaction.api
[standalone@localhost:9990 /] quit
- JBoss EAP の standalone.xml の datasource 定義へ以下のように SQL Server に関する定義を追加
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
--- 省略 ---
<datasource jndi-name="java:jboss/datasources/MsSQLDS" pool-name="MsSQLDS">
<connection-url>jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test;trustServerCertificate=true</connection-url>
<driver>sqlserver</driver>
<pool>
<min-pool-size>10</min-pool-size>
<initial-pool-size>10</initial-pool-size>
<max-pool-size>10</max-pool-size>
<prefill>true</prefill>
</pool>
<security>
<user-name>sa</user-name>
<password><指定したパスワード></password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter"/>
</validation>
</datasource>
<drivers>
<driver name="h2" module="com.h2database.h2">
--- 省略 ---
<driver name="sqlserver" module="com.microsoft">
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
- JBoss EAP を起動し、SQL Server と接続されていることを確認
$ netstat -na | grep 1433
tcp 0 0 127.0.0.1:58894 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58852 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:52956 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58858 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58880 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58842 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:52978 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58872 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58850 127.0.0.1:1433 ESTABLISHED
tcp 0 0 127.0.0.1:58890 127.0.0.1:1433 ESTABLISHED
tcp6 0 0 :::1433 :::* LISTEN
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58872 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58858 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:52978 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58894 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58852 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58880 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:52956 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58850 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58890 ESTABLISHED
tcp6 0 0 127.0.0.1:1433 127.0.0.1:58842 ESTABLISHED
$
以上で JBoss EAP + SQL Server の環境が構築できました。
- SQL Server コンテナイメージの起動
$ podman start demo_mssql2019
demo_mssql2019
$ podman ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
aa665da98ac0 mcr.microsoft.com/mssql/server:2019-CU15-ubuntu-20.04 /opt/mssql/bin/sq... 17 hours ago Up 10 seconds ago 0.0.0.0:1433->1433/tcp demo_mssql2019
$
- SQL Server コンテナイメージの停止（一例）
$ podman exec -it demo_mssql2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <指定したパスワード>
1> shutdown
2> go
Server shut down by request from login sa.
$ podman ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
$
今後の作業として、時間がある時に Blue Prism のリポジトリ DB として Linux 版 SQL Server が利用可能か検証してみたいなと思います。（動作したとしてもサポート外の構成ですが…）
