SQL Server常见复制功能的配置示例详解_MsSqlserver

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_server',
    @destination_db = 'adventureworksreplica',
    @subscription_type = 'push';

2. 事务复制配置示例

-- 在发布服务器上配置
-- 1. 创建发布
use [adventureworks]
exec sp_addpublication 
    @publication = 'advworkstranpub',
    @description = 'adventureworks 事务发布',
    @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. 合并复制配置示例

-- 在发布服务器上配置
-- 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配置分发服务器

-- 在主服务器上配置分发
-- 1. 安装分发服务器
use master
exec sp_adddistributor 
    @distributor = 'distributor_server',
    @password = 'distributor_admin_password';

-- 2. 创建分发数据库
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;