代码改变世界

SQL Server 复制：事务发布

2013-09-06 17:33  听风吹雨  阅读(17873)  评论(6)  编辑  收藏  举报

一、背景

　　在复制的运用场景中，事务发布是使用最为广泛的，我遇到这样一个场景：在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

（Figure1_1：文件夹权限）

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

image

（Figure1_2：SQL Server Agent登陆帐号）

image

（Figure2：分发服务器）

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

image

（Figure3：快照文件夹）

image

（Figure4：数据库）

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

image

（Figure5：事务发布）

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

image

（Figure6_1：表字段）

image

（Figure6_2：表字段）

image

（Figure6_3：表字段）

image

（Figure7：快照代理）

image

（Figure8：安全设置）

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

image

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

image

（Figure10：创建发布）

image

（Figure11：发布名称）

image

（Figure12：查看复制情况）

 

B. 订阅服务器配置

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

image

（Figure13：查找发布服务器）

image

（Figure14：查找发布服务器）

image

（Figure15：选择发布）

image

（Figure16：请求订阅）

image

（Figure17：订阅数据库）

image

（Figure18：订阅连接）

image

（Figure19：帐号密码）

image

（Figure20：代理计划）

image

（Figure21：初始化订阅）

image

（Figure22：创建订阅）

image

（Figure23：订阅）

image

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

image

（Figure25：复制监视器）

image

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

image

（Figure27：表数据）

 

三、注意事项

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

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

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

image

（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跨网段（跨机房）复制

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

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

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

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

 

五、参考文献

快照复制

SQL Server 复制

timestamp (Transact-SQL)

SQL Server 复制订阅与发布

SQL Server复制入门(一)----复制简介

SQL Server复制入门(二)----复制的几种模式

使用快照初始化订阅

复制