SQL SERVER 批量指定同步的表+事务发布(主从同步)

若水
2025-11-19 19:59:34
技术
MYSQL/MSSQ

详细操作步骤

步骤 1：创建一个 “空” 的发布

打开 SSMS，连接到你的发布服务器。
展开「数据库」，找到你要创建发布的数据库（例如 MyDatabase）。
右键点击该数据库，选择「任务」-> 「复制」-> 「新建发布...」。
在「新建发布向导」中，点击「下一步」。
选择发布数据库：确认选中了你的数据库（MyDatabase），点击「下一步」。
选择发布类型：根据你的需求选择（例如「事务发布」），点击「下一步」。
指定订阅服务器类型：选择你的订阅服务器类型（通常是「SQL Server 订阅服务器」），点击「下一步」。
选择项目：

这是关键一步。在列表中，只选择一个无关紧要的小表（或者创建一个临时表 temp_publish 来用）。
确保只勾选了这一个表。
点击「下一步」。

筛选表行：直接点击「下一步」（我们之后会批量添加，这里无需设置）。
快照代理：保持默认设置，点击「下一步」。
代理安全性：设置快照代理和日志读取器代理的运行账户，点击「下一步」。
完成向导：
此时，你已经创建了一个名为 MyLargePublication 的发布，其中只包含了一个临时项目。

为你的发布命名（例如 MyLargePublication）。
点击「完成」。

步骤 2：生成批量添加脚本

在 SSMS 中，打开一个新的查询窗口，并确保连接到发布服务器和发布数据库（MyDatabase）。
下面是一个示例脚本，用于生成添加所有 dbo schema 下、表名以 t_ 开头的表的 sp_addarticle 语句。你可以根据你的实际需求修改 WHERE 子句。

-- 在发布服务器上，针对发布数据库执行此脚本
USE [MyDatabase]; -- 替换为你的发布数据库名
GO

-- 声明变量
DECLARE @PublicationName NVARCHAR(128) = N'MyLargePublication'; -- 替换为你已经创建的发布名称
DECLARE @SchemaName NVARCHAR(128) = N'dbo'; -- 替换为你要发布的表的schema

-- 生成 sp_addarticle 脚本（已包含 @force_invalidate_snapshot 参数）
SELECT 
    'EXEC sp_addarticle 
        @publication = N''' + @PublicationName + ''',
        @article = N''' + t.name + ''',
        @source_owner = N''' + @SchemaName + ''',
        @source_object = N''' + t.name + ''',
        @type = N''logbased'', -- 对于事务发布，类型是 ''logbased''
        @description = N'''',
        @creation_script = N'''',
        @pre_creation_cmd = N''drop'', -- 如果订阅端已存在表，则先删除
        @schema_option = 0x00000000000000F3, -- 常用的schema选项组合
        @identityrangemanagementoption = N''manual'', -- 身份范围管理
        @destination_table = N''' + t.name + ''',
        @destination_owner = N''' + @SchemaName + ''',
        @force_invalidate_snapshot = 1; -- 强制使现有快照无效（关键参数）' AS AddArticleScript
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    s.name = @SchemaName
  AND t.name LIKE N't_%'; -- 替换为你的筛选条件

执行以上脚本。查询结果会返回一列，每一行都是一条完整的 EXEC sp_addarticle ... 语句。

下面脚本是查询出所有带主键的表, 在同步中, 这个是比较重要的

PS: 可以通过 INNER JOIN sys.key_constraints 实现，筛选条件为 type = 'PK'（主键的类型标识）。

-- 在发布服务器上，针对发布数据库执行此脚本
USE [MyDatabase]; -- 替换为你的发布数据库名
GO

-- 声明变量
DECLARE @PublicationName NVARCHAR(128) = N'MyLargePublication'; -- 替换为你已经创建的发布名称
DECLARE @SchemaName NVARCHAR(128) = N'dbo'; -- 替换为你要发布的表的schema

-- 生成 sp_addarticle 脚本（已包含 @force_invalidate_snapshot 参数）
SELECT 
    'EXEC sp_addarticle 
        @publication = N''' + @PublicationName + ''',
        @article = N''' + t.name + ''',
        @source_owner = N''' + @SchemaName + ''',
        @source_object = N''' + t.name + ''',
        @type = N''logbased'', -- 对于事务发布，类型是 ''logbased''
        @description = N'''',
        @creation_script = N'''',
        @pre_creation_cmd = N''drop'', -- 如果订阅端已存在表，则先删除
        @schema_option = 0x00000000000000F3, -- 常用的schema选项组合
        @identityrangemanagementoption = N''manual'', -- 身份范围管理
        @destination_table = N''' + t.name + ''',
        @destination_owner = N''' + @SchemaName + ''',
        @force_invalidate_snapshot = 1; -- 强制使现有快照无效（关键参数）' AS AddArticleScript
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
-- 新增条件：只筛选带主键的表
JOIN 
    sys.key_constraints kc ON t.object_id = kc.parent_object_id AND kc.type = 'PK'
WHERE 
    s.name = @SchemaName
  AND t.name LIKE N't_%'; -- 替换为你的筛选条件

PS: 如果你想通过 SQL 语句查询某个数据库中的所有 schema，或者某个表所属的 schema，可以执行以下脚本：

在 SQL Server 中，dbo 是默认架构。如果你在创建表时没有指定 schema，那么该表会自动归属到 dbo schema 下。

-- 1. 查询当前数据库中所有的 schema
SELECT name AS schema_name
FROM sys.schemas
ORDER BY name;

-- 2. 查询某个表所属的 schema（示例：查询表名为 t_user 的表的 schema）
SELECT 
    t.name AS table_name,
    s.name AS schema_name
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = 't_user'; -- 替换为你的表名

步骤 3：执行添加脚本

将步骤 2 中查询结果里的所有 AddArticleScript 内容复制下来。
在同一个查询窗口中，粘贴这些复制的脚本。
确保查询窗口仍然连接到发布服务器和发布数据库。
执行这些粘贴的脚本。
执行过程中，你会看到类似 "命令已成功完成。" 的消息，这表示表已成功添加到发布中。

步骤 4：（可选）清理临时项目

如果你在步骤 1 中使用了一个临时表（如 temp_publish）作为占位符，现在可以将其从发布中删除。
在查询窗口中执行以下命令：

-- 在发布服务器上，针对发布数据库执行此脚本
USE [MyDatabase];
GO

EXEC sp_droparticle 
    @publication = N'MyLargePublication', -- 替换为你的发布名称
    @article = N'temp_publish',           -- 替换为你的临时表名
    @force_invalidate_snapshot = 1;       -- 允许使现有快照失效，这是必须的