SQL Server常见复制功能的配置示例详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">SQL Server 复制功能配置示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 快照复制配置示例</a></li><li><a href="#_lab2_0_1">2. 事务复制配置示例</a></li><li><a href="#_lab2_0_2">3. 合并复制配置示例</a></li><li><a href="#_lab2_0_3">4. 使用T-SQL配置分发服务器</a></li><li><a href="#_lab2_0_4">5. 监控复制状态</a></li><li><a href="#_lab2_0_5">注意事项</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>SQL Server 复制功能配置示例</h2><p>SQL Server 提供了强大的数据复制功能，可以将数据从一个数据库复制到一个或多个其他数据库。以下是几种常见复制类型的配置示例。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 快照复制配置示例</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在发布服务器上配置
-- 1. 创建发布
USE
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';
</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 事务复制配置示例</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在发布服务器上配置
-- 1. 创建发布
USE
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';
</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 合并复制配置示例</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在发布服务器上配置
-- 1. 创建发布
USE
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';
</pre></div>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>4. 使用T-SQL配置分发服务器</h3>
<div class="jb51code"><pre class="brush: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;
</pre></div>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>5. 监控复制状态</h3>
<div class="jb51code"><pre class="brush:sql;">-- 检查发布状态
USE
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;
</pre></div>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>注意事项</h3>
<ul><li>配置复制前确保SQL Server代理服务正在运行</li><li>分发服务器、发布服务器和订阅服务器之间需要有适当的网络连接</li><li>确保使用的账户有足够的权限</li><li>对于大型数据库，初始快照生成可能需要较长时间</li><li>定期监控复制性能并根据需要调整参数</li></ul>
頁:
[1]