【From bky】Linux下Docker容器安装SQL Server为发布订阅功能做准备

容器安装SQL Server为发布订阅功能做准备"/>

【From bky】Linux下Docker容器安装SQL Server为发布订阅功能做准备

1. 宿主机pull最新镜像

docker pull mcr.microsoft/mssql/server:latest

2. 宿主机命令行跑docker run

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=pwd" -e "MSSQL_AGENT_ENABLED=true"  -e "MSSQL_LCID=2052"  -e "MSSQL_COLLATION=Chinese_PRC_CI_AS" \
-e "TZ=Asia/Shanghai"  -p 1501:1433 --name mssql -h mssql \
-d mcr.microsoft/mssql/server:latest

3. 进入容器终端新建快照文件夹

cd /var/opt/mssql
mkdir ReplData
chmod 777 ReplData

4. 查询容器服务器名称

select @@servername

5.创建分发库

其中distributor为容器hostname (docker run命令-h 指定的名称)

USE master
GO
EXEC sp_adddistributor @distributor = N'mssql'
GO
EXEC sp_adddistributiondb @database = N'distribution', @data_folder = N'/var/opt/mssql/data',
@log_folder = N'/var/opt/mssql/data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72,
@history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1,@password='pwd'
GO
SELECT name, create_date FROM sys.databases
GO

6.设置复制功能右键“发布服务器属性”菜单可见

其中publisher为容器hostname (dockers run命令-h 指定的名称)

USE [distribution]IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND type = 'U ')) CREATE TABLE UIProperties(id INT)GOIF (EXISTS (SELECT * FROM ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))EXEC sp_updateextendedproperty N'SnapshotFolder', N'/var/opt/mssql/ReplData', 'user', dbo, 'table', 'UIProperties'ELSEEXEC sp_addextendedproperty N'SnapshotFolder', N'/var/opt/mssql/ReplData', 'user', dbo, 'table', 'UIProperties'GOEXEC sp_adddistpublisher @publisher = N'mssql', @distribution_db = N'distribution', @security_mode = 1,@working_directory = N'/var/opt/mssql/ReplData', @trusted = N'false', @thirdparty_flag = 0,@publisher_type = N'MSSQLSERVER'GO

7. 还原或新建数据库

准备一个发布库和一个订阅库。

8.新建发布订阅