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

1. 宿主机pull最新镜像

docker pull mcr.microsoft.com/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.com/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)

GO

IF (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'

ELSE

  EXEC sp_addextendedproperty N'SnapshotFolder', N'/var/opt/mssql/ReplData', 'user', dbo, 'table', 'UIProperties'

GO

EXEC 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.新建发布订阅