digoal
2023-01-14
PostgreSQL , PolarDB , sql server
docker on macOS 一些操作例子可以参考:
SqlServer 安装时企业版会有两种选项：Microsoft SQL Server Enterprise (64-bit)，Microsoft SQL Server Enterprise: Core -based Licensing (64-bit)。 前者为 Enterprise Server +CAL license 模式，最大计算能力限制为20核；后者为 Enterprise Per Core license 模式，最大计算能力限制为操作系统最大CPU核数。
下面的例子中会使用Core -based Licensing 进行测试, 即docker run时输入的MSSQL_PID=EnterpriseCore变量.
https://hub.docker.com/_/microsoft-mssql-server
假设你已经根据前面的提示安装好docker on macOS.
1、下载镜像
docker pull mcr.microsoft.com/mssql/server:2022-latest
当前镜像如下
digoaldeMacBook-Pro:~ digoal$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
polardb/polardb_pg_local_instance htap 5299da4278f5 4 weeks ago 11.6GB
mcr.microsoft.com/mssql/server 2022-latest 9e28798be691 2 months ago 1.6GB
查看sql server 2022镜像的dockerfile:
digoaldeMacBook-Pro:~ digoal$ docker history --no-trunc mcr.microsoft.com/mssql/server:2022-latest
IMAGE CREATED CREATED BY SIZE COMMENT
sha256:9e28798be691ea67f36eddcb897d65bbdb42047b44e11cf00f98e4b8b421a5d7 2 months ago /bin/sh -c #(nop) CMD ["/opt/mssql/bin/sqlservr"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ENTRYPOINT ["/opt/mssql/bin/permissions_check.sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) USER mssql 0B
<missing> 2 months ago /bin/sh -c EXTRA_APT_PACKAGES="" /tmp/install.sh 179MB
<missing> 2 months ago /bin/sh -c #(nop) COPY dir:7e5e3d0a0346a806b89676ff7ff2908b6f3638bf1ddbad94093fa9f873663812 in / 1.35GB
<missing> 2 months ago /bin/sh -c #(nop) ENV CONFIG_EDGE_BUILD= 0B
<missing> 2 months ago /bin/sh -c #(nop) ENV MSSQL_RPC_PORT=135 0B
<missing> 2 months ago /bin/sh -c #(nop) EXPOSE 1433 0B
<missing> 2 months ago /bin/sh -c #(nop) LABEL vendor=Microsoft com.microsoft.product=Microsoft SQL Server com.microsoft.version=16.0.1000.6 0B
<missing> 2 months ago /bin/sh -c #(nop) CMD ["bash"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:7633003155a1059419aa1a6756fafb6e4f419d65bff7feb7c945de1e29dccb1e in / 72.8MB
2、运行容器
运行容器的命令例子:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
更多环境变量参考:
我这边的执行如下:
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name mssql2022 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mssql2o2o-" -e "MSSQL_PID=EnterpriseCore" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
3、查看容器启动日志
发现了一些警告, 文末已修复, 和/opt/mssql/bin/permissions_check.sh脚本里的一个变量有关.
docker logs mssql2022
/opt/mssql/bin/permissions_check.sh: line 4: [: : integer expression expected
/opt/mssql/bin/permissions_check.sh: line 59: [: : integer expression expected
SQL Server 2022 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.
2023-01-14 09:46:30.37 Server The licensing PID was successfully processed. The new edition is [Enterprise Edition: Core-based Licensing].
2023-01-14 09:46:30.55 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2023-01-14 09:46:30.56 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.
2023-01-14 09:46:30.57 Server Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2023-01-14 09:46:30.57 Server Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2023-01-14 09:46:30.58 Server Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2023-01-14 09:46:30.59 Server Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2023-01-14 09:46:30.60 Server Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2023-01-14 09:46:30.61 Server Setup step is copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
...
2023-01-14 09:46:33.90 spid42s Database 'msdb' running the upgrade step from version 953 to version 954.
2023-01-14 09:46:33.91 spid42s Database 'msdb' running the upgrade step from version 954 to version 955.
2023-01-14 09:46:33.92 spid42s Database 'msdb' running the upgrade step from version 955 to version 956.
2023-01-14 09:46:33.94 spid42s Database 'msdb' running the upgrade step from version 956 to version 957.
2023-01-14 09:46:33.98 spid22s Recovery is complete. This is an informational message only. No user action is required.
2023-01-14 09:46:34.03 spid33s The default language (LCID 0) has been set for engine and full-text services.
2023-01-14 09:46:34.20 spid33s The tempdb database has 6 data file(s).
4、进入容器, 测试用sqlcmd连接sql server 2022.
docker exec -it mssql2022 /bin/bash
mssql@d01375d78155:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Mssql2o2o-
1> select * from generate_series(1,10);
2> go
value
-----------
1
2
3
4
5
6
7
8
9
10
(10 rows affected)
1> select 1;
2> go
-----------
1
(1 rows affected)
1> create table t (id int, info text);
2> go
1> insert into t values (1,'test'),(2,'test1');
2> go
(2 rows affected)
1> select * from t;
2> go
id info
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 test
2 test1
(2 rows affected)
5、修复那个启动错误, 先检查一下报错脚本对应4,59行的内容, 实际上就是判断启动的是azure sql edge还是sql server 2022? 所以docker run加一个变量输入即可.
mssql@d01375d78155:/$ cat -n /opt/mssql/bin/permissions_check.sh
1 #!/bin/bash
2
3 username=$(whoami)
4 if [ "$CONFIG_EDGE_BUILD" -eq 1 ] ; then
5 message="Azure SQL Edge will run as non-root by default.\nThis container is running as user $username."
6 else
7 message="SQL Server 2022 will run as non-root by default.\nThis container is running as user $username."
8 fi
9
10 # Find the master database file
11 master_path=""
12 mssql_conf="/opt/mssql/bin/mssql-conf"
13
14 # Check for master.mdf using environment settings
15 if [ -n "$MSSQL_MASTER_DATA_FILE" ] && [ -f "$MSSQL_MASTER_DATA_FILE" ]
16 then
17 master_path="$MSSQL_MASTER_DATA_FILE"
18 elif [ -n "$MSSQL_DATA_DIR" ] && [ -f "$MSSQL_DATA_DIR/master.mdf" ]
19 then
20 # trim any trailing slashes from the path
21 trimmed_dir=$(echo "$MSSQL_DATA_DIR" | sed 's:/*$::')
22 if [ -f "$trimmed_dir/master.mdf" ]
23 then
24 master_path="$trimmed_dir/master.mdf"
25 fi
26 fi
27
28 # If not found, check mssql.conf for location
29 if [ -z "$master_path" ] && [ -f /var/opt/mssql/mssql.conf ]
30 then
31 # check for master data file
32 master_data_file=$($mssql_conf get filelocation masterdatafile | cut -d ':' -f 2 | tr -d ' ')
33 if [ -f "$master_data_file" ]
34 then
35 master_path="$master_data_file"
36 else
37 # check for default data dir
38 default_data_dir=$($mssql_conf get filelocation defaultdatadir | cut -d ':' -f 2 | tr -d ' ')
39 trimmed_dir=$(echo "$default_data_dir" | sed 's:/*$::')
40 if [ -f "$trimmed_dir/master.mdf" ]
41 then
42 master_path="$trimmed_dir/master.mdf"
43 fi
44 fi
45 fi
46
47 # If not found, check /var/opt/mssql
48 if [ -f "/var/opt/mssql/data/master.mdf" ] && [ -z "$master_path" ]
49 then
50 master_path="/var/opt/mssql/data/master.mdf"
51 fi
52
53 if [ -n "$master_path" ] && [ -f "$master_path" ]
54 then
55 master_mdf_owner=$(stat -c '%U' "$master_path")
56 message="$message\nYour master database file is owned by $master_mdf_owner."
57 fi
58
59 if [ "$CONFIG_EDGE_BUILD" -eq 1 ] ; then
60 message="$message\nTo learn more visit https://go.microsoft.com/fwlink/?linkid=2140520."
61 else
62 message="$message\nTo learn more visit https://go.microsoft.com/fwlink/?linkid=2099216."
63 fi
64
65 echo -e "$message"
66
67 # Execute the cmd from the dockerfile
68 exec "$@"mssql@d01375d78155:/$
加-e "CONFIG_EDGE_BUILD=0"即可:
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name mssql2022 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mssql2o2o-" -e "MSSQL_PID=EnterpriseCore" -e "CONFIG_EDGE_BUILD=0" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
然后就不报错了
digoaldeMacBook-Pro:~ digoal$ docker logs mssql2022
SQL Server 2022 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.
...
更多的sql server使用请参考手册:
