【必赢365net手机版】快速照相代理进程深入分析,复制业务和复制命令

必赢365net手机版 14

概述  

 快照代理准备已发布表的架构和初始数据文件以及其他对象、存储快照文件并记录分发数据库中的同步信息。
快照代理在分发服务器上运行;SQLServer2016版本对快照代理做了一些比较好的优化,接下来详细了解一下快照的执行过程。

 

 

–===============================================

一、快照代理文件

在执行快照作业是会在指定的快照目录生成4种类型的文件。

BCP文件:发布对象的数据文件。

IDX文件:索引创建脚本文件

PRE文件:复制快照脚本文件。

SCH文件:架构创建脚本文件

对复制一直属于一知半解浑浑噩噩的状态,仅知道一些皮毛,对很多细节没有深入学习过,

二、默认快照代理配置文件

必赢365net手机版 1

-BcpBachSize:每一次执行bcp操作copy的最大记录行数,默认是10万行。

-HistoryVerboseLevel:指定在快照操作过程中记录的历史记录大小。

-LoginTimeout:登录超时前等待的秒数。 默认值为 15
秒。

-QueryTimeOut:查询超时前等待的秒数。默认值为 1800

 备注:通过右键快照代理-快照代理配置文件;可以配置快照代理。

如果不对之处,请各位大神批评指正。

三、对比不同版本快照代理

接下来测试对比2亿的记录表生成快照

*以下讨论的基于事务复制,不考虑同步存储过程的情况

1.bcp文件数量对比

2008R2

 必赢365net手机版 2

2016SP1

必赢365net手机版 3

这里重点说一下BCP文件,因为应用快照到订阅服务器是以BCP文件为基本单位,也就是说不管你的BCP文件有多大都是一次性bulk到订阅服务器,所以BCP文件越大每次应用的时间就会越长。如果一个BCP文件太大可能会导致插入到订阅端失败。

从上图可以看到同样是2亿的记录,2008R2总共有8个BCP文件,而且最大的BCP文件大小将近1G其它的都才几兆;2016有16个BCP文件,并且前15个都是50M左右数据比较均匀。接下来看下图的每个BCP文件的记录对比。

 

–===============================================

2.快照生成详细过程对比

2008r2

必赢365net手机版 4

必赢365net手机版 5

2016SP1

必赢365net手机版 6

从生成的BCP文件记录对比来看:

2008R2:前7个文件每个文件记录数大概70万左右,最后一个文件记录1.1亿。

2016:前15个文件每个文件记录700万左右,最后一个文件78万。

说明:

2008R2前7个文件每个文件大概存储的记录量是70万剩下的记录都会存储到最后一个文件,所以2008R2比较适合的表记录数是600万左右。

2016前15个文件每个文件大概存储的记录量是700万剩下的记录都会存储到最后一个文件,2016适合的表记录数1.2亿左右。

必赢365net手机版 7

共同缺点:表记录超过“适合的复制表记录数”后剩下数据会全部存储到最后个bcp文件中。

当发布数据库上发生数据修改(增删改)时,会将相关操作写入日志,以保证数据一致性,而对发布表上聚集索引的修改的日志会被标记为‘REPLICATE’。

3.分发对比

接下来看一下分发的详细过程

必赢365net手机版 8

 从2008R2分发记录过程中可以看到每次BULK都是以bcp文件为单位,复制最后一个bcp文件花费了大概22分钟,而前面的每个文件都是十几秒;还是由于我当前的表只有三个字段而且除了主键没有索引否则的时间就更长了。

必赢365net手机版 9

四、快照生成过程

复制快照代理是一个可执行文件,用于准备快照文件(其中包含已发布表和数据库对象的架构及数据),然后将这些文件存储在快照文件夹中,并在分发数据库中记录同步作业。

必赢365net手机版 10

从上图可以了解整个快照的生成过程。

(PS:从上图中不难看出,在checkpoint时会记录复制相关的信息)

五、语法

snapshot [ -?]   
-Publisher server_name[instance_name]   
-Publication publication_name   
[-70Subscribers]   
[-BcpBatchSize bcp_batch_size]  
[-DefinitionFile def_path_and_file_name]  
[-Distributor server_name[instance_name]]  
[-DistributorDeadlockPriority [-1|0|1] ]  
[-DistributorLogin distributor_login]  
[-DistributorPassword distributor_password]  
[-DistributorSecurityMode [0|1] ]  
[-DynamicFilterHostName dynamic_filter_host_name]  
[-DynamicFilterLogin dynamic_filter_login]  
[-DynamicSnapshotLocation dynamic_snapshot_location]   
[-EncryptionLevel [0|1|2]]  
[-FieldDelimiter field_delimiter]  
[-HistoryVerboseLevel [0|1|2|3] ]  
[-HRBcpBlocks number_of_blocks ]  
[-HRBcpBlockSize block_size ]  
[-HRBcpDynamicBlocks ]  
[-KeepAliveMessageInterval keep_alive_interval]  
[-LoginTimeOut login_time_out_seconds]  
[-MaxBcpThreads number_of_threads ]  
[-MaxNetworkOptimization [0|1]]  
[-Output output_path_and_file_name]  
[-OutputVerboseLevel [0|1|2] ]  
[-PacketSize packet_size]  
[-ProfileName profile_name]  
[-PublisherDB publisher_database]  
[-PublisherDeadlockPriority [-1|0|1] ]  
[-PublisherFailoverPartner server_name[instance_name] ]  
[-PublisherLogin publisher_login]  
[-PublisherPassword publisher_password]   
[-PublisherSecurityMode [0|1] ]  
[-QueryTimeOut query_time_out_seconds]  
[-ReplicationType [1|2] ]  
[-RowDelimiter row_delimiter]  
[-StartQueueTimeout start_queue_timeout_seconds]  
[-UsePerArticleContentsView use_per_article_contents_view]  

参数

-?
输出所有可用的参数。

-Publisher server_name[*instance_name]
发布服务器的名称。 为该服务器上的 Microsoft SQL Server 默认实例指定
server_name。 为该服务器上的 
server_nameinstance_name* instance_name SQL
Server 默认实例指定 server_name。

-Publication 发布
发布的名称。 只有将发布设置为总是使快照可用于新订阅或重新初始化的订阅时,此参数才有效。

-70Subscribers
如果有任何订阅服务器在运行 SQL Server 7.0 版,则必须使用此参数。

-BcpBatchSize bcp batch size
在一次大容量复制操作中发送的行数。 执行 bcp
in
 操作时,批的大小为要作为一个事务发送到服务器的行数,并且也是分发代理记录 bcp 进度消息之前必须发送的行数。 当执行 bcp
out
 操作时,将使用固定批大小 1000。 值为 0 表示不记录任何消息。

-DefinitionFile def_path_and_file_name
代理定义文件的路径。 代理定义文件中包含该代理的命令行参数。 文件的内容被当作可执行文件进行分析。 使用双引号
(“) 指定包含任意字符的参数值。

-Distributor server_name[*instance_name]
分发服务器名称。 为该服务器上的 
默认实例指定 server_name SQL
Server 。 为该服务器上的 
server_nameinstance_name* instance_name SQL
Server 默认实例指定 server_name。

-DistributorDeadlockPriority [-1|0|1]
死锁发生时快照代理连接到分发服务器的优先级。 指定此参数是为了解决快照生成期间在快照代理和用户应用程序之间发生的死锁问题。

DistributorDeadlockPriority 值

说明

-1

在分发服务器上发生死锁时,应用程序而非快照代理优先。

0 (默认值)

未分配优先级。

1

在分发服务器上发生死锁时,快照代理优先。

-DistributorLogin distributor_login
使用 SQL Server 身份验证连接到分发服务器时所用的登录名。

-DistributorPassword distributor_password
使用 SQL Server 身份验证连接到分发服务器时使用的密码。 。

-DistributorSecurityMode [ 01]
指定分发服务器的安全模式。 值 0 指示 SQL
Server 身份验证模式(默认设置),值 1 指示 Windows 身份验证模式。

-DynamicFilterHostName dynamic_filter_host_name
在创建动态快照时,用来为筛选中的 [HOST_NAME

标记为‘REPLICATE’的日志会被日志读取代理器读取,转换成复制事务和命令,然后写入到分发库中的表dbo.MSrepl_transactions和dbo.MSrepl_commands。

(Transact-SQL)]() 设置值。 例如,如果为项目指定了子集筛选器子句 rep_id

HOST_NAME() ,并且在调用合并代理之前将 DynamicFilterHostName 属性设置为“FBJones”,则只会复制 rep_id 列中具有“FBJones”的行。

-DynamicFilterLogin dynamic_filter_login
在创建动态快照时,用来为筛选中的 [SUSER_SNAME

为保证订阅服务器上的数据一致性,日志读取代理默认情况下不会将发布数据库中的事务拆分成多个事务存放到分发数据库(可以修改日志读取代理中的MaxCmdsInTran默认值来拆分大事务)

(Transact-SQL)]() 设置值。 例如,如果为项目指定了子集筛选器子句 user_id

SUSER_SNAME() ,并且在调用 SQLSnapshot 对象的 Run 方法之前将 DynamicFilterLogin 属性设置为“rsmith”,则只将 user_id 列中具有“rsmith”的行包括在快照中。

-DynamicSnapshotLocation dynamic_snapshot_location
应生成动态快照的位置。

-EncryptionLevel [ 0 | 1 | 2 ]
建立连接时快照代理使用的安全套接字层 (SSL) 加密的等级。

EncryptionLevel 值

说明

0

指定不使用 SSL。

1

指定使用 SSL,但是代理不验证 SSL
服务器证书是否已由可信的颁发者进行签名。

2

指定使用 SSL,并验证证书。

-FieldDelimiter field_delimiter
在 SQL
Server 大容量复制数据文件中用于标记字段末尾的字符或字符序列。 默认值为
n<x$3>n。

-HistoryVerboseLevel [ 123]
指定在快照操作过程中记录的历史记录大小。 选择 1可将历史日志记录对性能的影响减至最小。

HistoryVerboseLevel 值

说明

0

进度消息将写入控制台或输出文件。 不在分发数据库中记录历史记录。

1

总是更新具有相同状态(启动、进行中、成功等)的上一历史记录消息。 如果不存在状态相同的上一记录,将插入新记录。

2 (默认值)

除非记录为空闲消息或长时间运行的作业消息等信息(此时将更新上一记录),否则插入新的历史记录。

3

始终插入新记录,除非它与空闲消息有关。

-HRBcpBlocks number_of_blocks
在编写器线程和读取器线程之间排队的 bcp 数据块的数量。 默认值为
50。 HRBcpBlocks 仅用于 Oracle 发布。

备注

此参数用于通过 Oracle 发布服务器优化 bcp 的性能。

HRBcpBlockSize*block_size*
每个 bcp 数据块的大小(以 KB 为单位)。 默认值为 64
KB。 HRBcpBlocks 仅用于 Oracle 发布。

备注

此参数用于通过 Oracle 发布服务器优化 bcp 的性能。

-HRBcpDynamicBlocks
每个 bcp 数据块的大小是否可以动态增长。 HRBcpBlocks 仅用于
Oracle 发布。

备注

此参数用于通过 Oracle 发布服务器优化 bcp 的性能。

-KeepAliveMessageInterval keep_alive_interval
快照代理在向 MSsnapshot_history 表中记录“waiting
for backend message”之前等待的时间(以秒为单位)。 默认值为 300 秒。

-LoginTimeOut login_time_out_seconds
登录超时前等待的秒数。 默认值为 15 秒。

-MaxBcpThreads number_of_threads
指定可以并行执行的大容量复制操作的数量。 同时存在的线程和 ODBC
连接的最大数量为 MaxBcpThreads 或显示在分发数据库中同步事务中的大容量复制请求数中较小的那一个。 MaxBcpThreads 的值必须大于 0 ,并且不存在任何硬编码的上限。 默认值为 1

– MaxNetworkOptimization [ 01]
是否将无关删除操作发送到订阅服务器。 无关删除操作是针对不属于订阅服务器分区的行发送到订阅服务器的
DELETE
命令。 无关删除操作不会影响数据的完整性或收敛,但它们会导致不必要的网络通信。 MaxNetworkOptimization 的默认值是 0。 将 MaxNetworkOptimization 设置为 1 可将不相关的删除操作发生的机会减至最小,从而减少网络通信,并最大程度地优化网络。如果存在多个级别的联接筛选器和复杂子集筛选器,则将此参数设置为 1 还会增加元数据的存储并导致发布服务器性能下降。 您应仔细评估您的复制拓扑,仅当无关删除操作导致的网络通信高到无法接受时才应将 MaxNetworkOptimization 设置为 1 。

备注

仅当合并发布的同步优化选项(sp_addmergepublication
(Transact-SQL) 的 @keep_partition_changes 参数)设置为 true 时,将此参数设置为 1 才是有用的。

-Output output_path_and_file_name
代理输出文件的路径。 如果未提供文件名,则向控制台发送该输出。 如果指定的文件名已存在,会将输出追加到该文件。

-OutputVerboseLevel [ 012]
指定输出是否应提供详细内容。

OutputVerboseLevel 值

说明

0

仅输出错误消息。

1 (默认值)

输出所有进度报告消息(默认值)。

2

输出所有错误消息和进度报告消息,这对于调试很有用。

-PacketSize packet_size
快照代理连接到 SQL Server时使用的数据包大小(以字节为单位)。 默认值为
8192 字节。

备注

除非您确信能够提高性能,否则不要更改数据包的大小。 对于大多数应用程序而言,默认数据包大小为最佳数值。

-ProfileName profile_name
指定用于代理参数的代理配置文件。 如果 ProfileName 为
NULL,则将禁用代理配置文件。 如果未指定 ProfileName ,则使用该代理类型的默认配置文件。 

-PublisherDB publisher_database
发布数据库的名称。 Oracle 发布服务器不支持该参数。

-PublisherDeadlockPriority [-1|0|1]
死锁发生时快照代理连接到发布服务器的优先级。 指定此参数是为了解决快照生成期间在快照代理和用户应用程序之间发生的死锁问题。

PublisherDeadlockPriority 值

说明

-1

在发布服务器上发生死锁时,应用程序而非快照代理优先。

0 (默认值)

未分配优先级。

1

在发布服务器上发生死锁时,快照代理优先。

-PublisherFailoverPartner server_name[*instance_name*]
指定参加与发布数据库进行的数据库镜像会话的 SQL Server 故障转移伙伴实例。

-PublisherLogin publisher_login
使用 SQL Server 身份验证连接到发布服务器时所用的登录名。

-PublisherPassword publisher_password
使用 SQL Server 身份验证连接到发布服务器时使用的密码。 。

-PublisherSecurityMode [ 01]
指定发布服务器的安全模式。 值 0 指示 SQL
Server 身份验证(默认值),值 1 指示 Windows 身份验证模式。

-QueryTimeOut query_time_out_seconds
查询超时前等待的秒数。默认值为 1800 秒。

-ReplicationType [ 12]
指定复制的类型。 值 1 指示事务复制,值 2 指示合并复制。

-RowDelimiter row_delimiter
在 SQL
Server 大容量复制数据文件中用于标记行尾的字符或字符序列。 默认值为
n<,@g>n。

-StartQueueTimeout start_queue_timeout_seconds
当运行的并发动态快照进程数达到由 sp_addmergepublication
(Transact-SQL) 的 @max_concurrent_dynamic_snapshots 属性设置的限制值时,快照代理等待的最大秒数。 如果在经过最大秒数之后快照代理仍在等待,快照代理将退出。 值
0 表示代理将无限期地等待,尽管可以将其取消。

– UsePerArticleContentsView use_per_article_contents_view
已不推荐使用此参数,支持它是为了能够向后兼容。

 

针对事务中修改的每一行数据生成一条或多条复制命令,尤其是对varchar(max)+xml+text等大字段操作(再次感谢肖磊)。

总结

由于在生成快照需要拥有对象的架构锁,所以在生成快照的过程中表对象是只读的。如果对大表生成快照千万不要选择在业务繁忙的时候否则有可能造成系统瘫痪,2016生成快照的时间比2008要快很多。通过对比可以发现2016的复制生成快照比2008性能提升了很多。但是从2014到2016BCP文件从32个变成16个不知道是出于什么原因。

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

 

--=====================================
--插入一条数据,生成16条复制命令
INSERT INTO [TestDemo].[dbo].[TB5](C1,C2)
SELECT REPLICATE('AC',10000),1
--查看生成的复制事务和命令
SELECT * FROM dbo.MSrepl_transactions
SELECT * FROM dbo.MSrepl_commands T
WHERE T.xact_seqno=0x00000100000002BA0020

必赢365net手机版 11

 

默认日志读取代理配置中,日志读取代理会以间隔5秒的频率查询发布库日志(PollingInterval),每个处理周期从发布数据库的事务日志中读取至多500条事务。当发布服务器上运行大事务时,会导致单个处理周期读取过多事务日志,因此应尽量避免对发布库做大事务修改(PS:大事务对订阅数据库也会造成影响)

PS:设置代理参数ReadBatchThreshold来控制单个处理周期读取最大的命令数(我没在生产服务器上设置过,但发现一片关于这个设置的文章:

如果发布数据库上事务变更比较频繁时,可以修改PollingInterval参数的值来降低复制延迟(Paddy_张充在生产服务器上配置过)。过小的PollingInterval值会加重发布服务器负载。

 

当发布数据库上的事务日志被日志读取代理读取后,日志便可以由checkpoint或日志备份截断。

 

对于复制事务和复制命令,分发代理按照配置参数CommitBatchSize(默认100)和CommitBatchThreshold(默认1000)来将事务打包成一个bacth提交给订阅服务器(肖磊如是说:同时指定CommitBatchSize和CommitBatchThreshold时,也会出现不到命令数和事务数就提交给订阅服务器),分发代理作业按照指定的调度运行。

 

在分发服务器上,复制清理作业默认按照每10分钟一次的频率执行,查找移除那些无需保留的事务和命令。复制事务和复制命令会按照分发服务器属性设置中指定“事务保持期”来存放,当超过最大事务保持期后,复制事务和复制命令会被清除,未同步的订阅会被标记为“过期”。

如果创建发布时未选择“立即初始化”(发布属性immediate_sync为false)时,复制事务和复制命令已传递给所有订阅,且存放时间超过最小“事务保持期”,复制事务和复制命令便可以被复制清理作业删除。(PS:清理作业不会清楚最后一条事务事务及其相关命令)

 

–===================================================================

MSDN 快速通道

链接:

=====================================================================
日志读取器代理相关配置

MaxCmdsInTran:number_of_commands
默认值:0
指定在日志读取器将命令写入到分发数据库时可分组到一个事务中的语句的最大数目。
如果使用此参数,在发布服务器上的大事务(包含许多命令)应用于订阅服务器时,日志读取器代理和分发代理可将这些大事务拆分为若干个较小的事务。
指定此参数可以减少分发服务器的争用问题并缩短发布服务器与订阅服务器之间的滞后时间。
由于初始事务是以较小的单元应用的,订阅服务器可以在初始事务结束之前访问一个较大的逻辑发布服务器事务的行,因而会破坏事务的原子性。
默认值为 0,这将保持发布服务器的事务边界。

PollingInterval: polling_interval
默认配置值:5秒
对日志进行已复制事务查询的频率(以秒计)。

ReadBatchSize:number_of_transactions
默认值:500
每个处理周期从发布数据库的事务日志中读取的最大事务数目。代理不断读取批次中的事务,直到从该日志中读取所有事务为止。
Oracle 发布服务器不支持该参数。

ReadBatchThreshold:number_of_commands
默认值:0
在复制命令由分发代理发送给订阅服务器之前,从事务日志读取的复制命令的数目。
如果未指定此参数,日志读取器代理会一直读取完此日志,或者读取到
-ReadBatchSize 中指定的数字(事务数)为止。

==============================================================
分发复制代理相关配置
BcpBatchSize:bcp_batch_size
默认值:2147473647
在一次大容量复制操作中发送的行数。 执行 bcp in
操作时,批的大小为要作为一个事务发送到服务器的行数,并且也是分发代理记录
bcp 进度消息之前必须发送的行数。 当执行 bcp out 操作时,将使用固定批大小
1000。

CommitBatchSize:commit_batch_size
默认值:100
发出 COMMIT 语句前要发给订阅服务器的事务数。

CommitBatchThreshold:commit_batch_threshold
默认值:1000
发出 COMMIT 语句前要发给订阅服务器的复制命令数。

MaxBcpThreads:number_of_threads
默认值:处理器数目的 2 倍,最大值为 8。
指定可以并行执行的大容量复制操作的数量。 同时存在的线程和 ODBC
连接的最大数量为 MaxBcpThreads
或显示在分发数据库中同步事务中的大容量复制请求数中较小的那一个。
MaxBcpThreads 的值必须大于 0,并且不存在任何硬编码的上限。
应用于使用并发快照选项在发布服务器上生成的快照时,不管为 MaxBcpThreads
指定了什么数值,都将使用一个线程。

MaxDeliveredTransactions:number_of_transactions
默认值:0
一次同步期间应用于订阅服务器的推送事务或请求事务的最大数量。 值为
0,表示最大值为无穷多个事务。
订阅服务器可使用其他值缩短从发布服务器请求的同步的持续时间。
如果MaxDeliveredTransactions设置为非0,即使订阅配置为连续运行,在传递指定数量的事务后,分发代理便会停止运行,需要重新手动启动。

PollingInterval:polling_interval
默认值:5 秒。

对分发数据库进行已复制事务查询的频率(以秒计)。

 惯例依旧是妹子

必赢365net手机版 12

 

必赢365net手机版 13

必赢365net手机版 14

Leave a Comment.