SQL Server 复制：事务发布

水深无声 2022-09-21 04:51 182阅读 0赞

**一、背景**

在复制的运用场景中，事务发布是使用最为广泛的，我遇到这样一个场景：在Task数据库中有Basic与Group两个表，需要提供这两个表的部分字段给其它程序读取放入缓存，程序需要比较及时的获取到这些数据，作为DBA你需要从权限和性能控制的角度出发，我采用了SQL Server的事务复制技术和timestamp，下面只讲述事务复制的搭建过程；

**二、实现过程**

**(一) 环境信息**

系统环境：Windows Server 2008 + SQL Server 2008 R2

发布服务器：192.168.1.151，服务器名称：USER-H2B2A89PEK

分发服务器：与发布服务器同一台机器

订阅服务器：192.168.1.152，服务器名称：USER-FJMO8L052U

发布数据库：Task

订阅数据库：TaskSiteInfo

数据库帐号：ReplicationUser/ ReplicationPassword

**(二) 搭建步骤**

**A. 发布服务器配置**

首先在发布数据库和订阅数据库上创建相同的帐号和密码（ReplicationUser/ ReplicationPassword），并且设置Task数据库的安全对象，设置这样的帐号的目的就是为了和程序连接到数据库的帐号区分开，可以做权限上的控制，方便问题的排查；

\--更改安全对象的所有权

ALTER AUTHORIZATION ON DATABASE::\[Task\] TO \[ReplicationUser\]

在E盘目录下创建文件夹：E:\\ReplData，并设置这个文件夹为共享目录，共享用户为barefootadmin；

[![image][]][image 1]

（Figure1\_1：文件夹权限）

这里需要设置SQL Server Agent登陆帐号为上面文件夹访问用户barefootadmin；

[![image][image 2]][image_image 2]

（Figure1\_2：SQL Server Agent登陆帐号）

[![image][image 3]][image_image 3]

（Figure2：分发服务器）

如果你设置快照文件夹路径为：E:\\ReplData，即使你的发布服务器本身就是分发服务器，如果订阅服务器是另外一台机器，那么在请求（Pull）订阅（如果是推送（Push）订阅就没有这个限制）模式下订阅代理是无法访问到这个快照文件的；除非你发布服务器、分发服务器和订阅服务器都是同一台机器；你应该设置快照文件夹路径为：\\\\USER-H2B2A89PEK\\ ReplData；

[![image][image 4]][image_image 4]

（Figure3：快照文件夹）

[![image][image 5]][image_image 5]

（Figure4：数据库）

*  快照发布：隔一段时间会覆盖订阅服务器的数据库，在订阅服务器上做的修改同样被覆盖；
 *  事务发布：是一种接近实时地从源到目标分发数据的方法；
 *  具有可更新订阅的事务发布：订阅服务器可更新发布服务器的数据；
 *  合并发布：发布服务器和订阅服务器的更新都会同步到对方，注意ID在合并发布上的冲突

[![image][image 6]][image_image 6]

（Figure5：事务发布）

注意表必须有主键才能进行复制，选择你必要的字段，这样可以减轻快照文件的大小和传输时间，而且在业务逻辑上更加安全，如果有需要，你还可以对记录进行过滤；

[![image][image 7]][image_image 7]

（Figure6\_1：表字段）

[![image][image 8]][image_image 8]

（Figure6\_2：表字段）

[![image][image 9]][image_image 9]

（Figure6\_3：表字段）

[![image][image 10]][image_image 10]

（Figure7：快照代理）

[![image][image 11]][image_image 11]

（Figure8：安全设置）

使用上面创建好的ReplicationUser帐号作为连接到发布服务器的帐号和密码；

[![image][image 12]][image_image 12]

（Figure9：使用刚刚创建的帐号密码）

[![image][image 13]][image_image 13]

（Figure10：创建发布）

[![image][image 14]][image_image 14]

（Figure11：发布名称）

[![image][image 15]][image_image 15]

（Figure12：查看复制情况）

**B. 订阅服务器配置**

创建完发布服务器（分发服务器也一起创建了），接下来就可以创建订阅服务器了，下面是具体的步骤：

[![image][image 16]][image_image 16]

（Figure13：查找发布服务器）

[![image][image 17]][image_image 17]

（Figure14：查找发布服务器）

[![image][image 18]][image_image 18]

（Figure15：选择发布）

[![image][image 19]][image_image 19]

（Figure16：请求订阅）

[![image][image 20]][image_image 20]

（Figure17：订阅数据库）

[![image][image 21]][image_image 21]

（Figure18：订阅连接）

[![image][image 22]][image_image 22]

（Figure19：帐号密码）

[![image][image 23]][image_image 23]

（Figure20：代理计划）

[![image][image 24]][image_image 24]

（Figure21：初始化订阅）

[![image][image 25]][image_image 25]

（Figure22：创建订阅）

[![image][image 26]][image_image 26]

（Figure23：订阅）

[![image][image 27]][image_image 27]

（Figure24：发布服务器上的订阅）

[![image][image 28]][image_image 28]

（Figure25：复制监视器）

[![image][image 29]][image_image 29]

（Figure26：订阅数据库新增的表）

[![image][image 30]][image_image 30]

（Figure27：表数据）

**三、注意事项**

1. 在SQL SERVER下实现发布服务器和订阅服务器的通信正常（即可以互访），打开1433端口，在防火墙中设置入站规则；

2. 发布服务器与订阅服务器的SQL Server Agent代理帐号必须设置的一样，否则不能互访；

3. 如果你希望在复制的过程中一并复制非聚集索引，可以对发布属性-项目进行如下设置，修改完之后需要重新生成快照；

[![image][image 31]][image_image 31]

（Figure28：非聚集索引复制）

4. 复制代理：快照代理（snapshot agent） 分布式代理（Distribution agent）日志读代理（log Reader agent） 合并代理（Merge agent） 队列读代理（Queue Reader Agent）

5. 适合使用复制的一些场景包括：

1) 负载均衡：通过将数据复制到其它数据库服务器来减少当前服务器的负载，比如说最典型的应用就是分发数据来分离OLTP和OLAP环境；

2) 分区：将经常使用的数据和历史数据隔离，将历史数据复制到其它数据库中；

3) 授权：将一部分数据提供给需要使用数据的人，以供其使用；

4) 数据合并：每个区域都有其各自的数据，将其数据进行合并。比如一个大公司，每个地区都有其各自的销售数据，总部需要汇总这些数据；

5) 故障转移：复制所有数据，以便故障时进行转移；

6. 快照复制或事务复制生成快照文件的类型有：

架构 (.sch)、数据 (.bcp)、约束和索引 (.dri)、约束 (.idx)、触发器 (.trg)（只用于更新订阅服务器）、压缩的快照文件 (.cab)。

**四、疑问**

1. SQL Server 只有在完整日志模式下才能使用复制嘛？

解惑：在简单模式下一样可以使用复制；

2. 如果是跨网段（跨机房）的发布与订阅，有没办法实现？需要注意什么？

解惑：可以通过修改host文件的方式搭建复制，请参考：[SQL Server跨网段（跨机房）复制][SQL Server]

3. 如果说上面的情况可以在host设置，但是如果有端口映射的，host也无法设置吧？

解惑：在SQL Server配置管理器里建立别名，同样可参考：[SQL Server跨网段（跨机房）复制][SQL Server]

4. 订阅的形式可以选择推送订阅或者请求订阅，请求订阅降低分发服务器处理工作的开销，这个开销有多大呢？怎么计算影响？

解惑：只有在有很多订阅服务器的时候才比较明显，推送订阅与请求订阅更大的区别是在管理方面的不同；

**五、参考文献**

[**快照复制**][Link 1]

[**SQL Server 复制**][SQL Server 1]

[**timestamp (Transact-SQL)**][timestamp _Transact-SQL]

[**SQL Server 复制订阅与发布**][SQL Server 2]

[**SQL Server复制入门(一)----复制简介**][SQL Server_----]

[**SQL Server复制入门(二)----复制的几种模式**][SQL Server_---- 1]

[**使用快照初始化订阅**][Link 2]

[**复制**][Link 3]

[image]: /images/20220721/1ab065949cdf4543b3d670882519ae69.png
[image 1]: http://images.cnitblog.com/blog/48305/201309/06173239-30d1915debd847b9bab7a12f6d866a26.png
[image 2]: http://images.cnitblog.com/blog/48305/201309/06173241-3e7735c792b64096843cc27f600ccddf.png
[image_image 2]: /images/20220721/9b41454e90834c209773a48ce6547e40.png
[image 3]: http://images.cnitblog.com/blog/48305/201309/06173242-b4e2ea83350142d987f417f4dcd03a90.png
[image_image 3]: /images/20220721/c6f32f2c4e6f4cbbb44b0b4c1039621c.png
[image 4]: http://images.cnitblog.com/blog/48305/201309/06173243-5421b1c862094a899b44770d795e91eb.png
[image_image 4]: /images/20220721/b8ec7146ac614c79bee2a1d60e581056.png
[image 5]: http://images.cnitblog.com/blog/48305/201309/06173243-977e6a3687f648eba3a294a24c694fc7.png
[image_image 5]: /images/20220721/581b2125058b47b2bc0dc93af8cfb729.png
[image 6]: http://images.cnitblog.com/blog/48305/201309/06173244-4e4aae9e959a44ab85ae9641fd75cd49.png
[image_image 6]: /images/20220721/d5559552d9734d479643df288b3b5035.png
[image 7]: http://images.cnitblog.com/blog/48305/201309/06173245-2b2da969f6464a9fa0f26fad0a48776e.png
[image_image 7]: /images/20220721/9728666999194126a4d510aa7cdb92a2.png
[image 8]: http://images.cnitblog.com/blog/48305/201309/06173246-2b710938b0de4b61943d6af3f37e3e37.png
[image_image 8]: /images/20220721/e5d10a75e490470e805017ef12998f68.png
[image 9]: http://images.cnitblog.com/blog/48305/201309/06173247-cb0d860d8a734b988b021157c2d20ac4.png
[image_image 9]: /images/20220721/04846333ee5b44fb919f3703d962267a.png
[image 10]: http://images.cnitblog.com/blog/48305/201309/06173248-495d569e9f724a20b00e23ae63c45432.png
[image_image 10]: /images/20220721/75d7af1f025545c1a852d55a820a53ec.png
[image 11]: http://images.cnitblog.com/blog/48305/201309/06173249-1f44cf26942c4ee9a0938038c69350aa.png
[image_image 11]: /images/20220721/7077338ed4ba4fb1abb0d0fe0965132b.png
[image 12]: http://images.cnitblog.com/blog/48305/201309/06173250-9364196e4dc445e8baa18c7162e8be8a.png
[image_image 12]: /images/20220721/fc08cecce64b4551b6699f69cfa7beed.png
[image 13]: http://images.cnitblog.com/blog/48305/201309/06173251-7a17a2086895407d88af01310d6e219e.png
[image_image 13]: /images/20220721/eb59835b2ed44a9cbde977736c786516.png
[image 14]: http://images.cnitblog.com/blog/48305/201309/06173252-83b2899ce0ba499daf699ac0e4780529.png
[image_image 14]: /images/20220721/534bc443c63145478243ab76daad24f2.png
[image 15]: http://images.cnitblog.com/blog/48305/201309/06173253-b04889ca015441e7b3d44b69e3c42164.png
[image_image 15]: /images/20220721/a707c1b9f2154b089c356645a0544887.png
[image 16]: http://images.cnitblog.com/blog/48305/201309/06173254-6c1c22e2327845a58f727e43a128a982.png
[image_image 16]: /images/20220721/05a259d5d2664908bb29affbf30ef9b3.png
[image 17]: http://images.cnitblog.com/blog/48305/201309/06173255-8b4d6f8c7ff14793a4aca82f7a3f9cb7.png
[image_image 17]: /images/20220721/98461d1afeff41f9bcfa2ad6094b8841.png
[image 18]: http://images.cnitblog.com/blog/48305/201309/06173256-96952ddef51a46cca3cc1dde72660cb4.png
[image_image 18]: /images/20220721/3e3a6bdcf1134300a17e0609d6997477.png
[image 19]: http://images.cnitblog.com/blog/48305/201309/06173258-8c9fe15d95bd4e97a7b876377fff1a56.png
[image_image 19]: /images/20220721/23ea85c1e94c4fdcba8c4064a640b5bc.png
[image 20]: http://images.cnitblog.com/blog/48305/201309/06173259-0ee69025c5614275b85c2b56fc1e39e9.png
[image_image 20]: /images/20220721/362d2eb72c584e9694baade59ba8b619.png
[image 21]: http://images.cnitblog.com/blog/48305/201309/06173300-0f57e01bf3594d41965c3cfde904a482.png
[image_image 21]: /images/20220721/702562a4f72147cfa4d1af874b3ed5b0.png
[image 22]: http://images.cnitblog.com/blog/48305/201309/06173301-58e21c4df7174fc0a506cbbb22a85704.png
[image_image 22]: /images/20220721/f79a8f2168c142c08ce46c59fb00a12c.png
[image 23]: http://images.cnitblog.com/blog/48305/201309/06173302-a5a25d5ca05b44298e5d8ae2d308fb4e.png
[image_image 23]: /images/20220721/53166ee734e04bfa8cf4dbc8c56a95e2.png
[image 24]: http://images.cnitblog.com/blog/48305/201309/06173303-2c413342ea1643d29d8af73d1d3f6ebc.png
[image_image 24]: /images/20220721/d37ab0fc714d4518a00a13e4bed4587a.png
[image 25]: http://images.cnitblog.com/blog/48305/201309/06173304-fbedcf58910f42209dde8bb98357c208.png
[image_image 25]: /images/20220721/46f3d23d9ffa44d391e1057feb9faa6f.png
[image 26]: http://images.cnitblog.com/blog/48305/201309/06173304-f5dab3b95b134e4cadc9de34192fa60f.png
[image_image 26]: /images/20220721/4bc72616a33c4be489f1650a69005749.png
[image 27]: http://images.cnitblog.com/blog/48305/201309/06173305-a4587e3460854e0eafa091f9fa47d93c.png
[image_image 27]: /images/20220721/23db14f1ba9741a6990865c90ab1ff9b.png
[image 28]: http://images.cnitblog.com/blog/48305/201309/06173306-a458bc71492e4fa98a1f867260d6c08c.png
[image_image 28]: /images/20220721/9d714bc3a74649db87a86e1006aec843.png
[image 29]: http://images.cnitblog.com/blog/48305/201309/06173306-4ec5aca413974eb68e316ab050ee0191.png
[image_image 29]: /images/20220721/b1079d42e68d4f208963592e2cded67f.png
[image 30]: http://images.cnitblog.com/blog/48305/201309/06173308-99d525e02faa4a9b9f83c5f526350bac.png
[image_image 30]: /images/20220721/450dc0cc10e24b8499af21aff5d10b30.png
[image 31]: http://images.cnitblog.com/blog/48305/201309/06173309-64fa5627d4bc4050bcf7cfcb8b28274f.png
[image_image 31]: /images/20220721/b3a4b6b18bca45f68e34c8b469d3b31b.png
[SQL Server]: http://www.cnblogs.com/gaizai/p/3328511.html
[Link 1]: http://technet.microsoft.com/zh-cn/library/ms151832.aspx
[SQL Server 1]: http://technet.microsoft.com/zh-cn/library/ms151198.aspx
[timestamp _Transact-SQL]: http://msdn.microsoft.com/zh-cn/library/ms182776%28SQL.90%29.aspx
[SQL Server 2]: http://www.cnblogs.com/yuqilin/archive/2011/04/28/2031274.html
[SQL Server_----]: http://www.cnblogs.com/CareySon/archive/2012/06/20/IntroductToSQLServerReplicationPart1.html
[SQL Server_---- 1]: http://www.cnblogs.com/CareySon/archive/2012/06/25/IntroductToSQLServerReplicationPart2.html
[Link 2]: http://technet.microsoft.com/zh-cn/library/ms151795%28v=sql.105%29.aspx
[Link 3]: http://www.cnblogs.com/stswordman/tag/replication/