使用 T-SQL 配置复制

项目
07/15/2024

适用于：SQL Server - Linux

在本教程中，你将使用 Transact-SQL (T-SQL) 在包含两个 SQL Server 实例的 Linux 上配置 SQL Server 快照复制。发布服务器和分发服务器位于同一实例上，订阅服务器位于单独的实例上。

启用 Linux 上的 SQL Server 复制代理
创建示例数据库
配置用于 SQL Server 代理访问的快照文件夹
配置分发服务器
配置发布服务器
创建发布和项目
配置订阅服务器
运行复制作业

通过复制存储过程配置所有复制配置。

先决条件

要完成本教程，您需要：

两个 SQL Server 实例和 Linux 上最新版本的 SQL Server
用于发起 T-SQL 查询以设置复制的工具，例如 sqlcmd 或 SQL Server Management Studio

查阅使用 Windows 上的 SQL Server Management Studio 管理 Linux 上的 SQL Server。

注意

SQL Server 2017 (14.x) (CU18) 及更高版本支持 Linux 上的 SQL Server 复制。

详细步骤

在 Linux 上启用 SQL Server 复制代理。在两台主机上，在终端中运行以下命令。
```
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server
```

创建示例数据库和表。在发布服务器上，创建示例数据库和表，将其作为发布项目。

CREATE DATABASE Sales;
GO

USE [Sales];
GO

CREATE TABLE Customer (
    [CustomerID] INT NOT NULL,
    [SalesAmount] DECIMAL NOT NULL
);
GO

INSERT INTO Customer (CustomerID, SalesAmount)
VALUES (1, 100), (2, 200), (3, 300);
GO

在另一个 SQL Server 实例，即订阅服务器上，创建用于接收这些项目的数据库。

CREATE DATABASE Sales;
GO

为 SQL Server 代理创建用于读取/写入的快照文件夹，在分发服务器上，创建快照文件夹并向“mssql”用户授予访问权限
```
sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/
```

配置分发服务器。在此示例中，发布服务器也是分发服务器。在发布服务器上运行以下命令，也为分发配置实例。

DECLARE @distributor AS SYSNAME;
DECLARE @distributorlogin AS SYSNAME;
DECLARE @distributorpassword AS SYSNAME;

-- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
SET @distributor = N'<distributor instance name>'; --in this example, it will be the name of the publisher
SET @distributorlogin = N'<distributor login>';
SET @distributorpassword = N'<distributor password>';

-- Specify the distribution database.
USE master

EXEC sp_adddistributor @distributor = @distributor -- this should be the hostname

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
EXEC sp_adddistributiondb @database = N'distribution',
    @log_file_size = 2,
    @deletebatchsize_xact = 5000,
    @deletebatchsize_cmd = 2000,
    @security_mode = 0,
    @login = @distributorlogin,
    @password = @distributorpassword;
GO

DECLARE @snapshotdirectory AS NVARCHAR(500);
SET @snapshotdirectory = N'/var/opt/mssql/data/ReplData/';

-- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
USE [distribution];
GO

IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND type = 'U'))
    CREATE TABLE UIProperties (id INT);

IF (EXISTS (SELECT * FROM::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)))
    EXEC sp_updateextendedproperty N'SnapshotFolder',
        @snapshotdirectory,
        'user',
        dbo,
        'table',
        'UIProperties';
ELSE
    EXEC sp_addextendedproperty N'SnapshotFolder',
        @snapshotdirectory,
        'user',
        dbo,
        'table',
        'UIProperties';
GO

配置发布服务器。在发布服务器上运行以下 T-SQL 命令。

DECLARE @publisher AS SYSNAME;
DECLARE @distributorlogin AS SYSNAME;
DECLARE @distributorpassword AS SYSNAME;

-- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
SET @publisher = N'<instance name>';
SET @distributorlogin = N'<distributor login>';
SET @distributorpassword = N'<distributor password>';

-- Specify the distribution database.
-- Adding the distribution publishers
EXEC sp_adddistpublisher @publisher = @publisher,
    @distribution_db = N'distribution',
    @security_mode = 0,
    @login = @distributorlogin,
    @password = @distributorpassword,
    @working_directory = N'/var/opt/mssql/data/ReplData',
    @trusted = N'false',
    @thirdparty_flag = 0,
    @publisher_type = N'MSSQLSERVER';
GO

创建发布作业。在发布服务器上运行以下 T-SQL 命令。

DECLARE @replicationdb AS SYSNAME;
DECLARE @publisherlogin AS SYSNAME;
DECLARE @publisherpassword AS SYSNAME;

SET @replicationdb = N'Sales';
SET @publisherlogin = N'<Publisher login>';
SET @publisherpassword = N'<Publisher Password>';

USE [Sales];
GO

EXEC sp_replicationdboption @dbname = N'Sales',
    @optname = N'publish',
    @value = N'true';

-- Add the snapshot publication
EXEC sp_addpublication @publication = N'SnapshotRepl',
    @description = N'Snapshot publication of database ''Sales'' from Publisher ''<PUBLISHER HOSTNAME>''.',
    @retention = 0,
    @allow_push = N'true',
    @repl_freq = N'snapshot',
    @status = N'active',
    @independent_agent = N'true';

EXEC sp_addpublication_snapshot @publication = N'SnapshotRepl',
    @frequency_type = 1,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 8,
    @frequency_subday_interval = 1,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @publisher_security_mode = 0,
    @publisher_login = @publisherlogin,
    @publisher_password = @publisherpassword;

根据销售表创建项目。

在发布服务器上运行以下 T-SQL 命令。

USE [Sales];
GO

EXEC sp_addarticle @publication = N'SnapshotRepl',
    @article = N'customer',
    @source_owner = N'dbo',
    @source_object = N'customer',
    @type = N'logbased',
    @description = NULL,
    @creation_script = NULL,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509D,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'customer',
    @destination_owner = N'dbo',
    @vertical_partition = N'false';

配置订阅。在发布服务器上运行以下 T-SQL 命令。

DECLARE @subscriber AS SYSNAME;
DECLARE @subscriber_db AS SYSNAME;
DECLARE @subscriberLogin AS SYSNAME;
DECLARE @subscriberPassword AS SYSNAME;

SET @subscriber = N'<Instance Name>'; -- for example, MSSQLSERVER
SET @subscriber_db = N'Sales';
SET @subscriberLogin = N'<Subscriber Login>';
SET @subscriberPassword = N'<Subscriber Password>';

USE [Sales];
GO

EXEC sp_addsubscription @publication = N'SnapshotRepl',
    @subscriber = @subscriber,
    @destination_db = @subscriber_db,
    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;

EXEC sp_addpushsubscription_agent @publication = N'SnapshotRepl',
    @subscriber = @subscriber,
    @subscriber_db = @subscriber_db,
    @subscriber_security_mode = 0,
    @subscriber_login = @subscriberLogin,
    @subscriber_password = @subscriberPassword,
    @frequency_type = 1,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 0,
    @active_start_date = 0,
    @active_end_date = 19950101;
GO

运行复制代理作业。运行以下查询以获取作业列表：

SELECT name, date_modified
FROM msdb.dbo.sysjobs
ORDER BY date_modified DESC;

运行快照复制作业以生成快照：

USE msdb;
GO

--generate snapshot of publications, for example
EXEC dbo.sp_start_job N'PUBLISHER-PUBLICATION-SnapshotRepl-1';
GO

运行快照复制作业以生成快照：

USE msdb;
GO
--distribute the publication to subscriber, for example
EXEC dbo.sp_start_job N'DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER';
GO

连接订阅服务器并查询复制数据。

在订阅服务器上，通过运行以下查询检查复制是否正常工作：
```
SELECT * from [Sales].[dbo].[Customer];
```

在本教程中，将使用 T-SQL 通过两个 SQL Server 实例配置 Linux 上的 SQL Server 快照复制。

启用 Linux 上的 SQL Server 复制代理
创建示例数据库
配置用于 SQL Server 代理访问的快照文件夹
配置分发服务器
配置发布服务器
创建发布和项目
配置订阅服务器
运行复制作业

通过

使用 T-SQL 配置复制

先决条件

详细步骤

反馈

其他资源

通过

使用 T-SQL 配置复制

先决条件

详细步骤

相关内容

反馈

其他资源