sqlserver复制

迷失缠绵情愫中

服务器角色：发布服务器、分发服务器、订阅服务器

复制代理程序：快照代理程序、分发代理程序、日志读取器代理程序、合并代理程序、队列读取器代理程序

复制的类型：快照复制、事务复制、合并复制

订阅类型：推送、请求


事务复制：通过激发触发器把数据的更改发送到订阅服务器
默认情况下，事务发布的订阅服务器应视为只读，因为更改将不会传播回发到发布服务器
在一般情况下，当使用事务发布时，如果发布服务器和订阅服务器都对数据进行了更新，那么将以发布服务器的事务为准，覆盖订阅服务器对数据的更改

合并复制：与事务复制相同，合并复制通常也是从发布数据库对象和数据的快照开始，并且用触发器跟踪在发布服务器和订阅服务器上所做的后续
数据更改和架构修改。订阅服务器在连接到网络时将与发布服务器进行同步，并交换自上次同步以来发布服务器和订阅服务器之间发生更改的所有行
合并复制通常用于服务器到客户端的环境中，合并复制适用于下列各种情况：
（1）多个订阅服务器可能会在不同时间更新同一数据，并将其更改传播到发布服务器和其他订阅服务器
（2）订阅服务器需要接收数据，脱机更改数据，并在以后与发布服务器和其他订阅服务器同步更改
（3）每个订阅服务器都需要不同的数据分区
（4）可能会发生冲突，并且在冲突发生时，需要具有检测和解决冲突的能力
（5）应用程序需要最终的数据更改结果，而不是访问中间数据状态。例如，如果在订阅服务器与发布服务器进行同步之前，订阅服务器上的行更改了5次，则该行在发布服务器上仅更改一次来反映最终数据更改，即第五次更改的值。
简单来讲，就是订阅服务器不是只读的，用户既可以修改发布服务器上的数据，也可以修改订阅服务器上的数据，然后两者的修改都会进行合并

订阅
推送：发布服务器将更改传播到订阅服务器，而无需订阅服务器发出请求。数据更新可以按需、连续地或按照计划推送到订阅服务器。分发代理或合并代理在分发服务器上运行
使用场合：
数据将连续同步或按照经常重复执行的计划同步
发布要求数据近似实时地移动
分发服务器上较高的处理器开销不会影响性能，换言之分发服务器的CPU处理能力一定要高，分发服务器配置一定要高
通常与快照和事务性复制一起使用，换言之快照和事务复制一般使用推送

请求：订阅服务器请求在发布服务器上所做的更改。请求订阅允许订阅服务器上的用户确定同步数据更改的时间。分发代理或合并代理在订阅服务器上运行
使用场合：
数据通常根据需要进行同步，而非连续同步
订阅数量多，或在分发服务器上运行所有代理会消耗大量资源
订阅服务器是自主的、非实时连接的或移动的。订阅服务器将确定连接和同步更改的时间
通常与合并复制一起使用、

配置复制的步骤：
（1）标识分发服务器，在分发服务器上创建分发数据库
（2）配置发布服务器，准备发布的数据
（3）创建订阅，启用接收发布数据的订阅服务器

复制前的考虑：
（1）为了提高执行效率，可以限制订阅服务器获得的所有数据，或仅发布订阅者真正需要的数据或订阅者有权得到的数据
（2）在实现快照复制之前，为快照复制留出充足的磁盘空间
（3）在实现事务复制之前，分配足够的日志空间，为分发数据库留有足够的磁盘空间
（4）为每一个表创建主键
（5）实现合并复制前移去timestamp列，由于订阅服务器的数据也会传递到发布服务器，应确保数据的完整性在各个订阅服务器上都能得到保证，维护表之间的关联参照
（6）在所有IDENTITY属性字段上加上NOT FOR REPLICATION设置，以保证SQLSERVER在复制代理程序所添加的行上保留起始标识值，
但是继续在其他用户所添加的行上增加标识值。当用户将某个新行添加到表时，标志值以通常的方式增加。当复制代理程序将该新行复制到订阅服务器时，再将该行插入到订阅服务器表中时不更改标识值

“Not for Replication”属性也适用于发布方和订阅方的Check约束、外键约束和标识列-Identity Column上
我的理解：比如有一张表orders表,orders表有一列recordno 自增，发布方有这张表，订阅方也有这张表，当前有两个订阅，一个发布 只需要在发布方的orders表里的recordno列设置NOT FOR REPLICATION就可以了。

1. USE [tempdb]
   
2. GO
   
3. CREATE TABLE ttt(id INT IDENTITY  NOT FOR REPLICATION NOT null ,NAME nvarchar(200))
   
4. GO
   
5. INSERT INTO [dbo].[ttt] ( id,[NAME] )
   
6. VALUES  (6, N'sdf')
   
7. --DROP TABLE ttt

复制代码

不过只能够在合并复制里面用

1. 1 消息 544，级别 16，状态 1，第 1 行
   
2. 2 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'ttt' 中的标识列插入显式值。

复制代码

然后你做一次快照复制，订阅里的orders表里的recordno列自动会有NOT FOR REPLICATION属性
当订阅#1插入了一条记录，recordno为2，订阅#2插入两条记录，recordno为2和recordno为3，那么同步的时候，就会把recordno1
和recordno2和recordno3的记录插入到发布方里的orders表
recordno
2
2
3
而不是
1
2
3
不过这样做的话就会造成recordno有重复值，所以这时候不能将recordno设置为主键
当然了，当把recordno设置为NOT FOR REPLICATION之后就能手工插入自增值


一般来说，复制包括以下几个阶段：配置发布，生成和应用初始快照，修改复制数据，以及同步和传播数据
分发服务器是快照复制和事务复制的首要组件，用于代理程序历史记录和监视。分发服务器可以是与发布服务器不同的独立服务器，也可以是同一台服务器

关于SQL AGENT服务
复制过程中，各代理程序的调度由SQL SERVER AGENT服务管理，应配置SQL SERVER AGENT服务能够在系统启动的时候自动启动，并且在意外停止时能够自动重新启动，由于复制操作跨越多个服务器传输数据，所以SQLSERVER AGENT服务的启动帐号应使用域用户账号

只发布必要的表或字段
发布项目可以是表中的全部数据或者部分数据，也可以是数据库对象，如存储过程、视图在事务发布中，只允许发布有主键的表，用户还可以选择发布这张表中指定的字段

定制性能标准
配置复制后，建议定制一个性能标准，以便确定复制在通常用于应用程序和拓扑的常见工作负荷中的行为方式。

例如：
（1）滞后时间：在复制拓扑中的节点之间传播数据更改所用的时间
（2）吞吐量：在某段时间内系统可以维持的复制活动量（用在某个时间段内传递的命令来度量）
（3）并发：可以在系统上同时运行的复制进程数
（4）同步持续时间：完成给定同步所用的时间
（5）资源消耗：用于复制处理的硬件和网络资源
复制监视器提供了设置复制事件警报的一种方法，当事件发生时，复制监视器可以自动响应，触发警报，执行管理员定义的任务或向某人发送电子邮件或传呼信息



提高常规复制的性能
复制经常用于在远程SQLSERVER之间进行数据分发，用户应当考虑SQLSERVER服务器和网络硬件、数据库设计、分发服务器配置、发布设计和选项、快照选项、订阅选项等对性能的影响。为了最大限度地提高复制的性能，请从下面着手提高复制的性能：
1、服务器和网络
（1）使用多处理器系统
复制代理可以利用服务器上的附加处理器。如果执行复制时CPU占用率高，则需要安装更快的CPU或多个CPU
（2）设置SQLSERVER的最小内存使用内存
默认情况下，SQLSERVER根据可用系统资源动态地改变其内存需求。为避免服务器内存竞争使用，对服务器设置“最小服务器内存”选项设置最小可用内存。如果服务器的角色是远程分发服务器或是发布服务器和分发服务器的组合，则必须分配至少16MB的内存
（3）使用单独的磁盘放置复制中涉及的所有数据库
将数据文件与事务日志存储在不同的磁盘驱动器上，可减少写日志所需的时间。如果需要容错，可以使用RAID1镜像该驱动器。其他数据库文件用RAID0或RAID0+1存储
（4）使用速度快的网络
网络可能是一个重要的性能瓶颈，尤其是对于事务复制而言。通过使用每秒100兆位（Mbps）或更快的网络，可以显著提高将更改传播到订阅服务器的速度
2、数据库的设计
（1）订阅服务器上的索引的设计
在使用事务复制、合并复制时，订阅服务器上使用索引时应谨慎。应为订阅服务器上的主键列建立索引，但附加的索引可能影响插入、更新和删除操作的性能
（2）订阅服务器中的触发器
在使用事务复制、合并复制时，在订阅服务器上的用户定义触发器中使用业务逻辑可能会降低将更改复制到订阅服务器的速度
（3）限制使用大型对象（LOB）数据类型
LOB（数据类型为text、ntext、image）比其他列数据类型需要更多的存储空间和处理。除非应用程序需要，否则不要在发布项目中包括这些列
（4）订阅服务器数据库使用简单恢复模式
简单恢复模式允许在订阅服务器上应用快照时执行最少的大容量插入日志记录
3、发布和订阅设计
（1）只发布需要的数据
发布超出实际需要的数据会使分发数据库和快照文件中耗费额外资源，应该避免发布不必要的数据表并考虑不要过于频繁地更改发布
（2）仅在必要时和非高峰时段运行快照代理
快照代理将发布服务器上的数据复制到分发服务器的“快照”文件夹中，生成快照依然是消耗大量资源的进程，所以最好在非高峰时间调度
（3）将快照文件夹放在分发服务器上不用于存储数据库或日志文件的本地驱动器中
快照代理将按顺序将数据写入快照文件夹。将快照文件夹放在与数据库或日志文件分开的驱动器上，可以减少磁盘间的争用，并有助于快照进程更快地完成
（4）连续运行代理程序代替过于频繁地调度
设置“连续运行代理”程序而不是创建“频繁调度”（如每隔一分钟）将提高复制性能。当设置分发代理程序或合并代理程序连续运行时，无论更改何时发生，都能立即传播到订阅服务器上
（5）考虑使用请求订阅
当存在大量订阅服务器时，应使用请求订阅。分发代理和合并代理在分发服务器上运行以实现推送订阅，在订阅服务器上运行以为实现请求订阅。使用请求订阅可以提高性能，因为他将代理从分发服务器移到了订阅服务器上。