SQL Server常见复制功能的配置示例详解

SQL Server 复制是一种将数据和数据库对象从一个数据库复制和分发到另一个数据库，并在数据库之间进行同步以保持一致性的技术。它主要分为三种类型：事务复制、合并复制 和 快照复制。

下面我将通过一个事务复制的示例，详细说明其配置步骤。

场景设定

发布服务器（Publisher）: SRV-SQL01，数据库 AdventureWorks
分发服务器（Distributor）: 使用与发布服务器相同的实例（本地分发）
订阅服务器（Subscriber）: SRV-SQL02，数据库 AdventureWorks_Replica
复制对象: Sales.SalesOrderHeader 和 Sales.SalesOrderDetail 表

配置步骤

步骤 1: 配置分发服务器（在发布服务器上执行） 在 SSMS 中，连接到发布服务器 SRV-SQL01。右键单击 “复制” 文件夹，选择 “配置分发”。在配置分发向导中：

选择 “将 ‘SRV-SQL01’ 用作其自身的分发服务器”（本地分发）。
指定快照文件夹（例如 \\SRV-SQL01\ReplSnapshot），确保订阅服务器有权限访问。
保留其他默认设置，完成向导。

步骤 2: 创建发布 展开 “复制” → 右键单击 “本地发布” → “新建发布”。选择源数据库 AdventureWorks。选择发布类型：“事务性发布”。选择要发布的表和其他对象：

展开表，勾选 Sales.SalesOrderHeader 和 Sales.SalesOrderDetail。

可选：添加筛选器（例如仅复制特定年份的数据）。设置快照代理计划：

选择 “立即创建快照” 并 “计划运行快照代理”。

设置代理安全性：

单击 “安全设置”，为快照代理和日志读取器代理指定一个具有足够权限的域账户（如 DOMAIN\SQLReplAgent）。

输入发布名称：AdventureWorks_Sales_Pub。 步骤 3: 创建订阅 展开 “本地发布” → 右键单击新创建的发布 AdventureWorks_Sales_Pub → “新建订阅”。选择发布服务器和发布。选择分发代理位置：

推送订阅：分发代理在分发服务器上运行（推荐用于中央管理）。
请求订阅：分发代理在订阅服务器上运行。
本例选择 “推送订阅”。

选择订阅服务器：单击 “添加订阅服务器”，选择 SRV-SQL02 实例。选择目标数据库：

在订阅服务器上选择现有数据库 AdventureWorks_Replica，或创建新数据库。

设置分发代理安全性：

指定连接分发服务器和订阅服务器的账户（同上 DOMAIN\SQLReplAgent）。

设置同步计划：

选择 “连续运行”（最小延迟）或自定义计划。

初始化订阅：

选择 “立即” 初始化，从快照开始同步。

完成向导。

验证复制

查看复制状态：

在发布服务器上，展开 “复制” → “本地发布” → 右键单击发布 → “查看快照代理状态” 或 “查看日志读取器代理状态”。

测试数据同步：

在发布服务器 AdventureWorks 中插入或更新 Sales.SalesOrderHeader 表的记录。
在订阅服务器 AdventureWorks_Replica 中查询对应表，确认数据已同步。

关键脚本示例

1. 配置分发（T-SQL）

-- 在发布服务器上执行
USE master;
EXEC sp_adddistributor @distributor = N'SRV-SQL01';
EXEC sp_adddistributiondb @database = N'distribution';
EXEC sp_adddistpublisher @publisher = N'SRV-SQL01', @distribution_db = N'distribution';

2. 创建事务发布（T-SQL）

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

-- 添加事务发布
EXEC sp_addpublication 
    @publication = N'AdventureWorks_Sales_Pub',
    @status = N'active',
    @repl_freq = N'continuous',
    @sync_method = N'native';

-- 添加项目（表）
EXEC sp_addpublication_snapshot @publication = N'AdventureWorks_Sales_Pub';
EXEC sp_addarticle 
    @publication = N'AdventureWorks_Sales_Pub',
    @article = N'SalesOrderHeader',
    @source_object = N'SalesOrderHeader',
    @destination_table = N'SalesOrderHeader';

EXEC sp_addarticle 
    @publication = N'AdventureWorks_Sales_Pub',
    @article = N'SalesOrderDetail',
    @source_object = N'SalesOrderDetail',
    @destination_table = N'SalesOrderDetail';

3. 添加推送订阅（T-SQL）

-- 在发布服务器上执行
EXEC sp_addsubscription 
    @publication = N'AdventureWorks_Sales_Pub',
    @subscriber = N'SRV-SQL02',
    @destination_db = N'AdventureWorks_Replica',
    @subscription_type = N'Push';

-- 添加推送订阅代理
EXEC sp_addpushsubscription_agent 
    @publication = N'AdventureWorks_Sales_Pub',
    @subscriber = N'SRV-SQL02',
    @subscriber_db = N'AdventureWorks_Replica',
    @job_login = N'DOMAIN\SQLReplAgent',
    @job_password = 'Password123';

常见问题与注意事项

权限要求：代理账户需具有 db_owner 权限，并对快照共享文件夹有读写权限。 网络与防火墙：确保发布服务器、分发服务器和订阅服务器之间的网络连通性，开放相应端口（默认1433）。 初始化：首次同步需要生成快照，大型数据库可能耗时较长，需规划维护窗口。监控：使用复制监视器（Replication Monitor）或系统表（如 distribution..MSrepl_errors）监控状态和错误。 清理任务：定期清理分发数据库的历史数据，防止过度增长。