SQL Server常见复制功能的配置示例详解_数据库_开发者

开发者 https://www.devze.com 2025-06-12 09:10 出处：网络作者：学亮编程手记

目录SQL Server 复制功能配置示例1. 快照复制配置示例2. 事务复制配置示例3. 合并复制配置示例4. 使用T-SQL配置分发服务器5. 监控复制状态注意事项SQL Server 复制功能配置示例

SQL Server 复制功能配置示例

SQL Server 提供了强大的数据复制功能，可以将数据从一个数据库复制到一个或多个其他数据库。以下是几种常见复制类型的配置示例编程客栈。

1. 快照复制配置示例

-- 在发布服务器上配置
-- 1. 创建发布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksProductPub',
    @description = 'AdventureWorks 产品数据快照发布',
    @sync_method = 'native',
    @repl_freq = 'snapshot',
    @status = 'active';

-- 2. 添加项目(表)
EXEC sp_adDarticle 
    @publication = 'AdvWorksProductPub',
    @article = 'Product',
    @source_owner = 'Production',
    @source_object = 'Product';

-- 3. 添加订阅
EXEC sp_addsubscription 
    @publication = 'AdvWorksProductPub',
    @subscriber = 'SUBSCRIBER_SERVE编程客栈R',
    @destination_db = 'AdventureWorksReplica',
    @subscription_type = 'Push';

2. 事务复制配置示例

-- 在发布服务器上配置
-- 1. 创建发布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksTranPub',
    @description = 'AdventureWorks 事务发布',
    @sync_method = 'concurrentpython',
    @repl_freq = 'continuous',
    @status = 'active',
    @allow_push = 'true',
    @allow_pull = 'true';

-- 2. 启用事务复制
EXEC sp_addpublication_snapshot 
    @publication = 'AdvWorksTranPub';

-- 3. 添加项目(表)
EXEC sp_addarticle 
    @publication = 'AdvWorksTranPub',
    @article = 'SalesOrderHeader',
    @source_owner = 'Sales',
    @source_object = 'SalesOrderHeader';

-- 4. 添加推送订阅
EXEC sp_addpushsubscription_agent 
    @publication = 'AdvWorksTranPub',
    @subscriber = 'SUBSCRIBER_SERVER',
    @subscriber_db = 'AdventureWorksReplica',
    @job_login = 'domain\account',
    @job_password = 'password';

3. 合并复制配置示例

-- 在发布服务器上配置
-- 1. 创建发布
USE [AdventureWorks]
EXEC sp_addmergepublication 
    @publication = 'AdvWorksMergePub',
    @description = 'AdventureWorks 合并发布',
    @sync_mode = 'native',
    @retention = 14,
    @allow_push = 'true',
    @allow_pull = 'true';

-- 2. 添加项目(表)
EXEC sp_addmergearticle 
    @publication = 'AdvWorksMergePub',
    @article = 'Customer',
    @source_owner = 'Sales',
    @source_object = 'Customer',
    @type = 'table',
    @identityrangemanagementoption = 'auto';

-- 3. 添加上拉订阅
EXEC sp_addmergepullsubscription 
    @publication = 'AdvWorksMergePub',
    @publisher = 'PUBLISHER_SERVER',
    @publisher_db = 'AdventureWorks',
    @subscriber_type = 'local';

-- 4. 添加上拉订阅代理
EXEC sp_addmergepullsubscription_agent 
    @publication = 'AdvWorksMergePub',
    @publisher = 'PUBLISHER_SERVER',
    @publisher_db = 'AdventureWorks',
    @job_login = 'domain\account',
    @job_password = 'password';

4. 使用T-SQL配置分发服务器

-- 在主服务python器上配置分发
-- 1. 安装分发服务器
USE master
EXEC sp_adddistributor 
    @distributor = 'DISTRIBUTOR_SERVER',
    @password = 'distributor_admin_password';

-- 2. 创建分发数www.devze.com据库
EXEC sp_adddistributiondb 
    @database = 'distribution',
    @data_folder = 'D:\SQLData',
    @log_folder = 'D:\SQLLogs';

-- 3. 启用发布服务器使用此分发服务器
EXEC sp_adddistpublisher 
    @publisher = 'PUBLISHER_SERVER',
    @distribution_db = 'distribution',
    @security_mode = 1;

5. 监控复制状态

-- 检查发布状态
USE [distribution]
SELECT 
    publisher_db AS '发布数据库',
    publication AS '发布名称',
    article AS '项目',
    subscriber_id AS '订阅者ID',
    status AS '状态'
FROM dbo.MSarticles;

-- 检查复制延迟
SELECT 
    publisher_db AS '发布数据库',
    publication AS '发布名称',
    subscriber_db AS '订阅数据库',
    latency AS '延迟(秒)'
FROM dbo.MSreplication_monitordata;

-- 检查未分发的命令
EXEC sp_replcounters;