详解SQL Server 2016快照代理过程
|
本文我们通过SQL Server 2016一个实例数据表，给大家详细分析了快照代理过程遇到的问题和解决办法，并对快照生成过程做了详细说明，以下是全部内容： 概述 快照代理准备已发布表的架构和初始数据文件以及其他对象、存储快照文件并记录分发数据库中的同步信息。 快照代理在分发服务器上运行；SQLServer2016版本对快照代理做了一些比较好的优化，接下来详细了解一下快照的执行过程。 一、快照代理文件 在执行快照作业是会在指定的快照目录生成4种类型的文件。 BCP文件：发布对象的数据文件。 IDX文件：索引创建脚本文件 PRE文件：复制快照脚本文件。 SCH文件：架构创建脚本文件 二、默认快照代理配置文件 -BcpBachSize:每一次执行bcp操作copy的最大记录行数，默认是10万行。 -HistoryVerboseLevel:指定在快照操作过程中记录的历史记录大小。 -LoginTimeout:登录超时前等待的秒数。 默认值为 15 秒。 -QueryTimeOut:查询超时前等待的秒数。默认值为 1800 秒 备注：通过右键快照代理-快照代理配置文件；可以配置快照代理。 三、对比不同版本快照代理 接下来测试对比2亿的记录表生成快照 1.bcp文件数量对比 2008R2 2016SP1
这里重点说一下BCP文件，因为应用快照到订阅服务器是以BCP文件为基本单位，也就是说不管你的BCP文件有多大都是一次性bulk到订阅服务器，所以BCP文件越大每次应用的时间就会越长。如果一个BCP文件太大可能会导致插入到订阅端失败。 从上图可以看到同样是2亿的记录，2008R2总共有8个BCP文件，而且最大的BCP文件大小将近1G其它的都才几兆；2016有16个BCP文件，并且前15个都是50M左右数据比较均匀。接下来看下图的每个BCP文件的记录对比。 2.快照生成详细过程对比 2008r2
2016SP1
从生成的BCP文件记录对比来看： 2008R2：前7个文件每个文件记录数大概70万左右，最后一个文件记录1.1亿。 2016：前15个文件每个文件记录700万左右，最后一个文件78万。 说明： 2008R2前7个文件每个文件大概存储的记录量是70万剩下的记录都会存储到最后一个文件,所以2008R2比较适合的表记录数是600万左右。 2016前15个文件每个文件大概存储的记录量是700万剩下的记录都会存储到最后一个文件，2016适合的表记录数1.2亿左右。
共同缺点：表记录超过“适合的复制表记录数”后剩下数据会全部存储到最后个bcp文件中。 3.分发对比 接下来看一下分发的详细过程
从2008R2分发记录过程中可以看到每次BULK都是以bcp文件为单位，复制最后一个bcp文件花费了大概22分钟，而前面的每个文件都是十几秒；还是由于我当前的表只有三个字段而且除了主键没有索引否则的时间就更长了。 四、快照生成过程 复制快照代理是一个可执行文件，用于准备快照文件（其中包含已发布表和数据库对象的架构及数据），然后将这些文件存储在快照文件夹中，并在分发数据库中记录同步作业。
从上图可以了解整个快照的生成过程。 五、语法 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]参数 -? 输出所有可用的参数。 -Publisherserver_name[instance_name] 发布服务器的名称。为该服务器上的MicrosoftSQL Server默认实例指定 server_name。为该服务器上的server_nameinstance_nameinstance_nameSQL Server默认实例指定 server_name。 -Publication发布 发布的名称。只有将发布设置为总是使快照可用于新订阅或重新初始化的订阅时，此参数才有效。 -70Subscribers 如果有任何订阅服务器在运行SQL Server7.0 版，则必须使用此参数。 -BcpBatchSizebcpbatchsize 在一次大容量复制操作中发送的行数。执行bcp in操作时，批的大小为要作为一个事务发送到服务器的行数，并且也是分发代理记录bcp进度消息之前必须发送的行数。当执行bcp out操作时，将使用固定批大小 1000。值为 0 表示不记录任何消息。 -DefinitionFiledef_path_and_file_name 代理定义文件的路径。代理定义文件中包含该代理的命令行参数。文件的内容被当作可执行文件进行分析。使用双引号 (") 指定包含任意字符的参数值。 -Distributorserver_name[instance_name] 分发服务器名称。为该服务器上的默认实例指定server_nameSQL Server。为该服务器上的server_nameinstance_nameinstance_nameSQL Server默认实例指定 server_name。 -DistributorDeadlockPriority[-1|0|1] 死锁发生时快照代理连接到分发服务器的优先级。指定此参数是为了解决快照生成期间在快照代理和用户应用程序之间发生的死锁问题。
DistributorDeadlockPriority 值 说明 -1 在分发服务器上发生死锁时，应用程序而非快照代理优先。 0（默认值） 未分配优先级。 1 在分发服务器上发生死锁时，快照代理优先。 -DistributorLogindistributor_login 使用SQL Server身份验证连接到分发服务器时所用的登录名。 -DistributorPassworddistributor_password 使用SQL Server身份验证连接到分发服务器时使用的密码。。 -DistributorSecurityMode[0|1] 指定分发服务器的安全模式。值0指示SQL Server身份验证模式（默认设置），值1指示 Windows 身份验证模式。 -DynamicFilterHostNamedynamic_filter_host_name 在创建动态快照时，用来为筛选中的HOST_NAME (Transact-SQL)设置值。例如，如果为项目指定了子集筛选器子句rep_id = HOST_NAME()，并且在调用合并代理之前将DynamicFilterHostName属性设置为“FBJones”，则只会复制rep_id列中具有“FBJones”的行。 -DynamicFilterLogindynamic_filter_login 在创建动态快照时，用来为筛选中的SUSER_SNAME (Transact-SQL)设置值。例如，如果为项目指定了子集筛选器子句user_id = SUSER_SNAME()，并且在调用SQLSnapshot对象的Run方法之前将DynamicFilterLogin属性设置为“rsmith”，则只将user_id列中具有“rsmith”的行包括在快照中。 -DynamicSnapshotLocationdynamic_snapshot_location 应生成动态快照的位置。 -EncryptionLevel[0|1|2]
EncryptionLevel 值 说明 0 指定不使用 SSL。 1 指定使用 SSL，但是代理不验证 SSL 服务器证书是否已由可信的颁发者进行签名。 2 指定使用 SSL，并验证证书。 -FieldDelimiterfield_delimiter -HistoryVerboseLevel[1|2|3]
HistoryVerboseLevel 值 说明 0 进度消息将写入控制台或输出文件。不在分发数据库中记录历史记录。 1 总是更新具有相同状态（启动、进行中、成功等）的上一历史记录消息。如果不存在状态相同的上一记录，将插入新记录。 2（默认值） 除非记录为空闲消息或长时间运行的作业消息等信息（此时将更新上一记录），否则插入新的历史记录。 3 始终插入新记录，除非它与空闲消息有关。 -HRBcpBlocksnumber_of_blocks 在编写器线程和读取器线程之间排队的bcp数据块的数量。默认值为 50。HRBcpBlocks仅用于 Oracle 发布。 备注 此参数用于通过 Oracle 发布服务器优化bcp的性能。 -HRBcpBlockSizeblock_size 每个bcp数据块的大小（以 KB 为单位）。默认值为 64 KB。HRBcpBlocks仅用于 Oracle 发布。 备注 此参数用于通过 Oracle 发布服务器优化bcp的性能。 -HRBcpDynamicBlocks 每个bcp数据块的大小是否可以动态增长。HRBcpBlocks仅用于 Oracle 发布。 备注 此参数用于通过 Oracle 发布服务器优化bcp的性能。 -KeepAliveMessageIntervalkeep_alive_interval 快照代理在向MSsnapshot_history表中记录“waiting for backend message”之前等待的时间（以秒为单位）。默认值为 300 秒。 -LoginTimeOutlogin_time_out_seconds 登录超时前等待的秒数。 默认值为 15 秒。 -MaxBcpThreadsnumber_of_threads 指定可以并行执行的大容量复制操作的数量。同时存在的线程和 ODBC 连接的最大数量为MaxBcpThreads或显示在分发数据库中同步事务中的大容量复制请求数中较小的那一个。MaxBcpThreads的值必须大于0，并且不存在任何硬编码的上限。默认值为1。 -MaxNetworkOptimization[0|1] 是否将无关删除操作发送到订阅服务器。无关删除操作是针对不属于订阅服务器分区的行发送到订阅服务器的 DELETE 命令。无关删除操作不会影响数据的完整性或收敛，但它们会导致不必要的网络通信。MaxNetworkOptimization的默认值是0。将MaxNetworkOptimization设置为1可将不相关的删除操作发生的机会减至最小，从而减少网络通信，并最大程度地优化网络。如果存在多个级别的联接筛选器和复杂子集筛选器，则将此参数设置为1还会增加元数据的存储并导致发布服务器性能下降。您应仔细评估您的复制拓扑，仅当无关删除操作导致的网络通信高到无法接受时才应将MaxNetworkOptimization设置为1。 备注 仅当合并发布的同步优化选项（sp_addmergepublication (Transact-SQL)的@keep_partition_changes参数）设置为true时，将此参数设置为1才是有用的。 -Outputoutput_path_and_file_name 代理输出文件的路径。如果未提供文件名，则向控制台发送该输出。如果指定的文件名已存在，会将输出追加到该文件。 -OutputVerboseLevel[0|1|2] 指定输出是否应提供详细内容。
OutputVerboseLevel 值 说明 0 仅输出错误消息。 1（默认值） 输出所有进度报告消息（默认值）。 2 输出所有错误消息和进度报告消息，这对于调试很有用。 -PacketSizepacket_size 快照代理连接到SQL Server时使用的数据包大小（以字节为单位）。默认值为 8192 字节。 备注 除非您确信能够提高性能，否则不要更改数据包的大小。对于大多数应用程序而言，默认数据包大小为最佳数值。 -ProfileNameprofile_name 指定用于代理参数的代理配置文件。如果ProfileName为 NULL，则将禁用代理配置文件。如果未指定ProfileName，则使用该代理类型的默认配置文件。 -PublisherDBpublisher_database 发布数据库的名称。Oracle 发布服务器不支持该参数。 -PublisherDeadlockPriority[-1|0|1] 死锁发生时快照代理连接到发布服务器的优先级。指定此参数是为了解决快照生成期间在快照代理和用户应用程序之间发生的死锁问题。
PublisherDeadlockPriority 值 说明 -1 在发布服务器上发生死锁时，应用程序而非快照代理优先。 0（默认值） 未分配优先级。 1 在发布服务器上发生死锁时，快照代理优先。 -PublisherFailoverPartnerserver_name[instance_name] 指定参加与发布数据库进行的数据库镜像会话的SQL Server故障转移伙伴实例。 -PublisherLoginpublisher_login 使用SQL Server身份验证连接到发布服务器时所用的登录名。 -PublisherPasswordpublisher_password 使用SQL Server身份验证连接到发布服务器时使用的密码。。 -PublisherSecurityMode[0|1] 指定发布服务器的安全模式。值0指示SQL Server身份验证（默认值），值1指示 Windows 身份验证模式。 -QueryTimeOutquery_time_out_seconds 查询超时前等待的秒数。默认值为 1800 秒。 -ReplicationType[1|2] 指定复制的类型。值1指示事务复制，值2指示合并复制。 -RowDelimiterrow_delimiter 在SQL Server大容量复制数据文件中用于标记行尾的字符或字符序列。默认值为 n<,@g>n。 -StartQueueTimeoutstart_queue_timeout_seconds 当运行的并发动态快照进程数达到由sp_addmergepublication (Transact-SQL)的@max_concurrent_dynamic_snapshots属性设置的限制值时，快照代理等待的最大秒数。如果在经过最大秒数之后快照代理仍在等待，快照代理将退出。值 0 表示代理将无限期地等待，尽管可以将其取消。 -UsePerArticleContentsViewuse_per_article_contents_view 已不推荐使用此参数，支持它是为了能够向后兼容。 总结由于在生成快照需要拥有对象的架构锁，所以在生成快照的过程中表对象是只读的。如果对大表生成快照千万不要选择在业务繁忙的时候否则有可能造成系统瘫痪，2016生成快照的时间比2008要快很多。通过对比可以发现2016的复制生成快照比2008性能提升了很多。但是从2014到2016BCP文件从32个变成16个不知道是出于什么原因。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
