快捷導(dǎo)航

SQL Server常見復(fù)制功能的配置示例詳解

更新時(shí)間：2025年06月11日 09:19:00 作者：學(xué)亮編程手記

SQL Server 提供了強(qiáng)大的數(shù)據(jù)復(fù)制功能,可以將數(shù)據(jù)從一個(gè)數(shù)據(jù)庫復(fù)制到一個(gè)或多個(gè)其他數(shù)據(jù)庫,本文給大家介紹了幾種常見復(fù)制類型的配置示例,需要的朋友可以參考下

SQL Server 復(fù)制功能配置示例

SQL Server 提供了強(qiáng)大的數(shù)據(jù)復(fù)制功能，可以將數(shù)據(jù)從一個(gè)數(shù)據(jù)庫復(fù)制到一個(gè)或多個(gè)其他數(shù)據(jù)庫。以下是幾種常見復(fù)制類型的配置示例。

1. 快照復(fù)制配置示例

-- 在發(fā)布服務(wù)器上配置
-- 1. 創(chuàng)建發(fā)布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksProductPub',
    @description = 'AdventureWorks 產(chǎn)品數(shù)據(jù)快照發(fā)布',
    @sync_method = 'native',
    @repl_freq = 'snapshot',
    @status = 'active';

-- 2. 添加項(xiàng)目(表)
EXEC sp_addarticle 
    @publication = 'AdvWorksProductPub',
    @article = 'Product',
    @source_owner = 'Production',
    @source_object = 'Product';

-- 3. 添加訂閱
EXEC sp_addsubscription 
    @publication = 'AdvWorksProductPub',
    @subscriber = 'SUBSCRIBER_SERVER',
    @destination_db = 'AdventureWorksReplica',
    @subscription_type = 'Push';

2. 事務(wù)復(fù)制配置示例

-- 在發(fā)布服務(wù)器上配置
-- 1. 創(chuàng)建發(fā)布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksTranPub',
    @description = 'AdventureWorks 事務(wù)發(fā)布',
    @sync_method = 'concurrent',
    @repl_freq = 'continuous',
    @status = 'active',
    @allow_push = 'true',
    @allow_pull = 'true';

-- 2. 啟用事務(wù)復(fù)制
EXEC sp_addpublication_snapshot 
    @publication = 'AdvWorksTranPub';

-- 3. 添加項(xiàng)目(表)
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. 合并復(fù)制配置示例

-- 在發(fā)布服務(wù)器上配置
-- 1. 創(chuàng)建發(fā)布
USE [AdventureWorks]
EXEC sp_addmergepublication 
    @publication = 'AdvWorksMergePub',
    @description = 'AdventureWorks 合并發(fā)布',
    @sync_mode = 'native',
    @retention = 14,
    @allow_push = 'true',
    @allow_pull = 'true';

-- 2. 添加項(xiàng)目(表)
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配置分發(fā)服務(wù)器

-- 在主服務(wù)器上配置分發(fā)
-- 1. 安裝分發(fā)服務(wù)器
USE master
EXEC sp_adddistributor 
    @distributor = 'DISTRIBUTOR_SERVER',
    @password = 'distributor_admin_password';

-- 2. 創(chuàng)建分發(fā)數(shù)據(jù)庫
EXEC sp_adddistributiondb 
    @database = 'distribution',
    @data_folder = 'D:\SQLData',
    @log_folder = 'D:\SQLLogs';

-- 3. 啟用發(fā)布服務(wù)器使用此分發(fā)服務(wù)器
EXEC sp_adddistpublisher 
    @publisher = 'PUBLISHER_SERVER',
    @distribution_db = 'distribution',
    @security_mode = 1;

5. 監(jiān)控復(fù)制狀態(tài)

-- 檢查發(fā)布狀態(tài)
USE [distribution]
SELECT 
    publisher_db AS '發(fā)布數(shù)據(jù)庫',
    publication AS '發(fā)布名稱',
    article AS '項(xiàng)目',
    subscriber_id AS '訂閱者ID',
    status AS '狀態(tài)'
FROM dbo.MSarticles;

-- 檢查復(fù)制延遲
SELECT 
    publisher_db AS '發(fā)布數(shù)據(jù)庫',
    publication AS '發(fā)布名稱',
    subscriber_db AS '訂閱數(shù)據(jù)庫',
    latency AS '延遲(秒)'
FROM dbo.MSreplication_monitordata;

-- 檢查未分發(fā)的命令
EXEC sp_replcounters;