SQL Server 复制：事务发布（读写分离）

hq8501

　　一、背景
　　在复制的运用场景中，事务发布是使用最为广泛的，我遇到这样一个场景：在YangJiaLeClub数据库中有表、存储过程、视图、用户定义函数，需要提供给其它程序读取放入缓存，程序需要比较及时的获取到这些数据，需要从权限和性能控制的角度出发，我采用了SQL Server的事务复制技术和timestamp，下面只讲述事务复制的搭建过程；
　　二、实现过程
　　(一) 环境信息

	操作系统	IP	服务器名称	数据库版本	数据库名称	数据库帐号信息
发布服务器	Windows 10 企业版	192.168.2.100	DESKTOP-SDIK64J	SQL Server 2008 R2	YangJiaLeClub	sa/bwl 　　reg/bwl
分发服务器	与发布服务器同一台机器
订阅服务器1	Windows Server 2008r2 Enterprise	192.168.2.117	Win-DB-Slave1	SQL Server 2008 R2	YangJiaLeClub	sa/bwl
订阅服务器2	Windows Server 2008r2 Enterprise	192.168.2.118	Win-DB-Slave2	SQL Server 2008 R2	YangJiaLeClub	sa/bwl

　　1.发布服务器：
　　1.1 在防火墙中设置入栈规则，开放1433和1434端口

　　2.订阅服务器：
　　2.1.Sql Native Client 10配置（32位）新建别名：

　　2.Host 服务器名与IP映射（由于发布订阅服务器名称必须是：服务器+“\”+实例名访问）
　　目录： C:\Windows\System32\drivers\etc

　　3.控制面板\用户帐户\凭据管理器
　　创建凭据

　　(二) 搭建步骤
　　A. 发布服务器配置
　　首先在发布数据库和订阅数据库上创建相同的帐号和密码（reg/bwl），并且设置YangJiaLeClub数据库的安全对象，设置这样的帐号的目的就是为了和程序连接到数据库的帐号区分开，可以做权限上的控制，方便问题的排查；
　　--更改安全对象的所有权
　　ALTER AUTHORIZATION ON DATABASE::[YangJiaLeClub] TO [reg]
　　在F盘目录下创建文件夹：F:\DB\Net\ReplData，并设置这个文件夹为共享目录，共享用户为biwl

　　（Figure1_1：文件夹权限）
　　这里需要设置SQL Server Agent登陆帐号为上面文件夹访问用户biwl（发布服务器和订阅服务器都需要建立biwl相同帐号和密码设置SQLServer代理服务）

　　（Figure1_2：SQL Server Agent登陆帐号）

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

　　（Figure3：快照文件夹）

　　（Figure4：数据库）

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

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

　　（Figure6_1：表字段）
　　（Figure6_2：存储过程）
　　（Figure6_3：视图）
　　（Figure6_4：用户定义函数）

　　（Figure7：快照代理）

　　（Figure8：安全设置）
　　使用上面创建好的ReplicationUser帐号作为连接到发布服务器的帐号和密码；

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

　　（Figure10：创建发布）

　　（Figure11：发布名称）

　　（Figure12：查看复制情况）
　　B. 订阅服务器配置
　　创建完发布服务器（分发服务器也一起创建了），接下来就可以创建订阅服务器了，下面是具体的步骤：

　　（Figure13：查找发布服务器）

　　（Figure14：查找发布服务器）

　　（Figure15：选择发布）

　　（Figure16：请求订阅）

　　（Figure17：订阅数据库）

　　（Figure18：订阅连接）

　　（Figure19：帐号密码）

　　（Figure20：代理计划）

　　（Figure21：初始化订阅）

　　（Figure22：创建订阅）

　　（Figure23：订阅）

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

　　（Figure25：复制监视器）

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

　　（Figure27：表数据）
　　三、注意事项
　　1. 在SQL SERVER下实现发布服务器和订阅服务器的通信正常（即可以互访），打开1433端口，在防火墙中设置入站规则；
　　2. 发布服务器与订阅服务器的SQL Server Agent代理帐号必须设置的一样，否则不能互访；
　　3. 如果你希望在复制的过程中一并复制非聚集索引，可以对发布属性-项目进行如下设置，修改完之后需要重新生成快照；

　　（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复制入门(二)----复制的几种模式
　　使用快照初始化订阅
　　复制