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

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

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

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

这是关键一步。在列表中，只选择一个无关紧要的小表（或者创建一个临时表 temp_publish 来用）。

筛选表行：直接点击「下一步」（我们之后会批量添加，这里无需设置）。
快照代理：保持默认设置，点击「下一步」。
代理安全性：设置快照代理和日志读取器代理的运行账户，点击「下一步」。
完成向导：

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

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

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 内容复制下来。
在同一个查询窗口中，粘贴这些复制的脚本。
确保查询窗口仍然连接到发布服务器和发布数据库。
执行这些粘贴的脚本。
执行过程中，你会看到类似 "命令已成功完成。" 的消息，这表示表已成功添加到发布中。

PS: 这里是完整的加表流程, 一定要注意刷新表的订阅关系!!

-- 1. 添加文章
EXEC sp_addarticle 
    @publication = N'K3-2026-PUB',
    @article = N'T_ECC_AFTERSALEORDERENTRY_C',
    @source_owner = N'dbo',
    @source_object = N'T_ECC_AFTERSALEORDERENTRY_C',
    @type = N'logbased',
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,  -- 更安全的选项
    @identityrangemanagementoption = N'manual',
    @destination_table = N'T_ECC_AFTERSALEORDERENTRY_C',
    @destination_owner = N'dbo',
    @force_invalidate_snapshot = 1;

   
-- 2. 刷新所有订阅（关键！）
EXEC sp_refreshsubscriptions @publication = N'K3-2026-PUB';


-- 3. 重新初始化每个订阅（可选但推荐）
EXEC sp_reinitsubscription 
    @publication = N'K3-2026-PUB', -- 发布名称
    @subscriber = N'YourSubscriber', -- 替换为实际订阅服务器名称
    @destination_db = N'YourDB', -- 发布数据库名
    @article = N'all';  -- 或指定具体article
    
    
-- 4. 手动启动快照代理(重新生成快照)

EXEC sp_startpublication_snapshot @publication = N'K3-2026-PUB';

-- 5.  (可选) 查看当前已经发布的表

USE AIS20251214222026; -- 替换为你的实际发布数据库名
GO
SELECT 
    p.name AS publication_name,
    a.name AS article_name,
    s.name AS source_schema,
    o.name AS source_table,
    a.dest_table AS destination_table
FROM 
    dbo.syspublications p
INNER JOIN 
    dbo.sysarticles a ON p.pubid = a.pubid
INNER JOIN 
    sys.objects o ON a.objid = o.object_id
INNER JOIN 
    sys.schemas s ON o.schema_id = s.schema_id
ORDER BY 
    p.name, s.name, o.name;

步骤 4：重新生成快照(如果照上面做了这一步省略即可)

在 SSMS 中，展开「复制」-> 「本地发布」。
右键点击你的发布（MyLargePublication），选择「生成快照」。(PS: 在2014和之前和版本中, 直接选择重新初始化)
在弹出的对话框中，点击「是」。
等待快照代理完成快照生成。这可能需要一些时间，具体取决于你发布的表的大小和数量。

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

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

-- 在发布服务器上，针对发布数据库执行此脚本

USE AIS202140222026001;
GO

-- ============= 步骤1：关键！先删除【该表】的订阅（核心解决报错的语句） =============
EXEC sp_dropsubscription
    @publication = N'2026-PUB',        -- 你的发布名称，不变
    @article = N'temp_publish',      -- 要取消订阅的目标表，不变
    @subscriber = N'all',                 -- 删除所有订阅端对该表的订阅，推荐用all
    @destination_db = N'all';             -- 删除订阅端所有数据库的该表订阅
GO

-- ============= 步骤2：再执行你原来的删除发布项目语句，此时不会再报错 =============
EXEC sp_droparticle 
    @publication = N'K3-2026-PUB',
    @article = N'temp_publish',
    @force_invalidate_snapshot = 1;       -- 必须=1，允许快照失效，不影响业务
GO

-- 你现在的状态：发布端执行成功 → 订阅端什么都不用做，就是最优解.