SQL Server常見復制功能的配置示例詳解

更新時間：2025年06月11日 09:19:00 作者：學亮編程手記

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

SQL Server 復制功能配置示例

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

1. 快照復制配置示例

-- 在發(fā)布服務器上配置
-- 1. 創(chuàng)建發(fā)布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksProductPub',
    @description = 'AdventureWorks 產品數(shù)據(jù)快照發(fā)布',
    @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_SERVER',
    @destination_db = 'AdventureWorksReplica',
    @subscription_type = 'Push';

2. 事務復制配置示例

-- 在發(fā)布服務器上配置
-- 1. 創(chuàng)建發(fā)布
USE [AdventureWorks]
EXEC sp_addpublication 
    @publication = 'AdvWorksTranPub',
    @description = 'AdventureWorks 事務發(fā)布',
    @sync_method = 'concurrent',
    @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. 合并復制配置示例

-- 在發(fā)布服務器上配置
-- 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. 添加項目(表)
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ā)服務器

-- 在主服務器上配置分發(fā)
-- 1. 安裝分發(fā)服務器
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ā)布服務器使用此分發(fā)服務器
EXEC sp_adddistpublisher 
    @publisher = 'PUBLISHER_SERVER',
    @distribution_db = 'distribution',
    @security_mode = 1;

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

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

-- 檢查復制延遲
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;