在Docker中快速使用SQL Server 2022环境

AiDBA宝典

发布于 2023-04-27 13:14:46

3.9K0

发布于 2023-04-27 13:14:46

文章被收录于专栏：小麦苗的DB宝专栏

简介

docker hub地址：https://hub.docker.com/_/microsoft-mssql-server

使用 Docker 请求和运行 SQL Server 2022 (16.x) Linux 容器映像 mssql-server-linux。然后可以使用 sqlcmd 进行连接，创建第一个数据库并运行查询。

此映像包含在基于 Ubuntu 20.04 的 Linux 上运行的 SQL Server。它可在 Linux 上与 Docker 引擎 1.8+ 配合使用。

本文中的示例使用 docker 命令。但大多数这些命令也可用于 Podman。Podman 有一个类似于 Docker 引擎的命令行接口。可以详细了解 Podman。

快速安装部署

下载镜像

docker search mssql
docker pull mcr.microsoft.com/mssql/server:2022-latest

创建容器

SA_PASSWORD 环境变量已弃用。请改用 MSSQL_SA_PASSWORD。

docker rm -f mssql2022
docker run -d --name mssql2022 --hostname  mssql2022 \
-p 1433:1433  \
-e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=lhr@zr123" \
-e "TZ=Asia/Shanghai" \
-e "MSSQL_PID=Enterprise" \
-e "MSSQL_COLLATION=Chinese_PRC_BIN" \
mcr.microsoft.com/mssql/server:2022-latest

1、密码应符合 SQL Server 默认密码策略，否则容器无法设置 SQL Server，将停止工作。默认情况下，密码必须为至少八个字符且包含以下四种字符中的三种：大写字母、小写字母、十进制数字、符号。可使用 docker logs 命令检查错误日志。

2、下表对前一个 docker run 示例中的参数进行了说明：

将 ACCEPT_EULA 变量设置为任意值，以确认接受最终用户许可协议。SQL Server 映像的必需设置。

容器配置

Requirements

This image requires Docker Engine 1.8+ in any of their supported platforms.
At least 2GB of RAM (3.25 GB prior to 2017-CU2). Make sure to assign enough memory to the Docker VM if you're running on Docker for Mac or Windows.
Requires the following environment flags "ACCEPT_EULA=Y" "MSSQL_SA_PASSWORD=" "MSSQL_PID=(default: Developer)"
A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

Environment Variables

You can use environment variables to configure SQL Server on Linux Containers.

ACCEPT_EULA confirms your acceptance of the End-User Licensing Agreement.

MSSQL_SA_PASSWORD is the database system administrator (userid = 'sa') password used to connect to SQL Server once the container is running. Important note: This password needs to include at least 8 characters of at least three of these four categories: uppercase letters, lowercase letters, numbers and non-alphanumeric symbols.

MSSQL_PID is the Product ID (PID) or Edition that the container will run with. Acceptable values:

Developer : This will run the container using the Developer Edition (this is the default if no MSSQL_PID environment variable is supplied)
Express : This will run the container using the Express Edition
Standard : This will run the container using the Standard Edition
Enterprise : This will run the container using the Enterprise Edition
EnterpriseCore : This will run the container using the Enterprise Edition Core

For a complete list of environment variables that can be used, refer to the documentation here.

使用容器

docker exec -it mssql2022 bash
/opt/mssql-tools/bin/sqlcmd  -S localhost -U sa -P "lhr@zr123"


SELECT Name from sys.Databases
go

SELECT @@VERSION
SELECT SERVERPROPERTY('Edition')
go

-- 修改sa密码
USE [master]
GO
ALTER LOGIN [sa] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'lhr'
GO


-- 远程访问
sqlcmd -S 192.168.8.8,1433 -U sa -P lhr
sqlcmd -S 192.168.8.8,1433 -d master -h -1 -U sa -P lhr -W -Q "set nocount on;SELECT name FROM master..sysdatabases"


-- linux客户端SQL Server安装
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
yum install -y mssql-tools unixODBC-devel mssql-cli

echo "export PATH=/opt/mssql-tools/bin:$PATH" >> /root/.bashrc
source /root/.bashrc


-- windows下客户端
https://learn.microsoft.com/zh-cn/sql/tools/sqlcmd-utility?view=sql-server-ver16
路径：C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn

-- SSMS
https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

示例：

[root@lhr ~]# docker exec -it mssql2022 bash
mssql@mssql2022:/$ 
mssql@mssql2022:/$ /opt/mssql-tools/bin/sqlcmd  -S localhost -U SA -P "lhr@zr123"
1> SELECT Name from sys.Databases
2> go
Name                                                                                                                            
-----------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            

(4 rows affected)
1> SELECT @@VERSION
2> go

-----------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
        Oct  8 2022 05:58:25 
        Copyright (C) 2022 Microsoft Corporation
        Enterprise Edition (64-bit) on Linux (Ubuntu 20.04.5 LTS) <X64>                                                                                                                      

(1 rows affected)
1> USE [master]
2> GO
Changed database context to 'master'.
1> ALTER LOGIN [sa] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
2> GO
1> USE [master]
2> GO
Changed database context to 'master'.
1> ALTER LOGIN [sa] WITH PASSWORD=N'lhr'
2> GO
1> 

-- 远程访问

PS C:\Users\lhrxxt> sqlcmd -S 192.168.8.8,1433 -U sa -P lhr
1> exit
PS C:\Users\lhrxxt> sqlcmd -S 192.168.8.8,1433 -d master -h -1 -U sa -P lhr -W -Q "set nocount on;SELECT name FROM master..sysdatabases"
master
tempdb
model
msdb

PS C:\Users\lhrxxt> sqlcmd -S 192.168.8.8,1433 -U sa -P lhr
1> set nocount on;SELECT name FROM master..sysdatabases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
1>

SSMS客户端访问：