详解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] 

参数

-?

输出所有可用的参数。

-Publisher server_name[\instance_name]

发布服务器的名称。 为该服务器上的 Microsoft SQL Server 默认实例指定 server_name。 为该服务器上的 server_name\instance_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_name\instance_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 [ 0| 1]

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

-DynamicFilterHostName dynamic_filter_host_name

在创建动态快照时,用来为筛选中的 HOST_NAME (Transact-SQL) 设置值。 例如,如果为项目指定了子集筛选器子句 rep_id = HOST_NAME() ,并且在调用合并代理之前将 DynamicFilterHostName 属性设置为“FBJones”,则只会复制 rep_id 列中具有“FBJones”的行。

-DynamicFilterLogin dynamic_filter_login

在创建动态快照时,用来为筛选中的 SUSER_SNAME (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 [ 1| 2| 3]
指定在快照操作过程中记录的历史记录大小。 选择 1可将历史日志记录对性能的影响减至最小。

HistoryVerboseLevel 值

说明

0

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

1

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

2 (默认值)

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

3

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

-HRBcpBlocks number_of_blocks

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

备注

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

-HRBcpBlockSizeblock_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 [ 0| 1]

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

备注

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

-Output output_path_and_file_name

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

-OutputVerboseLevel [ 0| 1| 2]

指定输出是否应提供详细内容。

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 [ 0| 1]

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

-QueryTimeOut query_time_out_seconds

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

-ReplicationType [ 1| 2]

指定复制的类型。 值 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

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

总结

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

(0)

相关推荐

  • 详解SQL Server 2016快照代理过程

    本文我们通过SQL Server 2016一个实例数据表,给大家详细分析了快照代理过程遇到的问题和解决办法,并对快照生成过程做了详细说明,以下是全部内容: 概述 快照代理准备已发布表的架构和初始数据文件以及其他对象.存储快照文件并记录分发数据库中的同步信息. 快照代理在分发服务器上运行:SQLServer2016版本对快照代理做了一些比较好的优化,接下来详细了解一下快照的执行过程. 一.快照代理文件 在执行快照作业是会在指定的快照目录生成4种类型的文件. BCP文件:发布对象的数据文件. IDX

  • 详解SQL Server数据库状态和文件状态

    数据库状态 (database states) 查询数据库的当前状态 : 1.查询所有数据库的状态 ,通过sys.databases目录视图的state_desc列 user master go select state_desc ,[name] from sys.databases go 2.查询指定数据库的状态,通过DATABASEPROPERTYEX函数的Status属性 select DATABASEPROPERTYEX('demoData','status') go 状态: ONLIN

  • 详解SQL Server 中 JSON_MODIFY 的使用

    SQL Server 中 JSON_MODIFY 的使用 Intro SQL Server 从 2016 开始支持了一些 JSON操作,最近的项目里也是好多地方字段直接存成了 JSON,需要了解一下怎么在SQL Server 中操作 JSON. JSON支持适用于 SQL Server 2016 及以上版本 和 Azure SQL Database. 最近在项目中很多地方字段都是存一个 JSON 字符串,尤其是针对一些有很多不确定的属性的数据来说,这样的信息我们一般都放在一个 Extra 之类的

  • 图文详解SQL Server 2008R2使用教程

    本文为大家分享了SQL Server 2008R2简单使用教程,供大家参考,具体内容如下 1 首先找到开始菜单中相关内容:如下图:安装的组件不同可能有所不同:我的电脑中包括如下项: 商业智能:管理控制台:导入和导出数据:分析服务:集成服务:配置工具:文档和教程:性能工具: 因为偶装的组件多: 2 进入管理控制台 首先是登录:服务器类型选择 数据库引擎:此处先用Windows身份验证登录: 3 进入管理控制台 界面如下:看到 数据库 下有四个子项,系统数据库.数据库快照.ReportServer.

  • 图文详解SQL Server 2008 R2安装配置方法

    安装sql server2008 R2的方法,轻松简单易操作,按照操作一步一步来. 工具: sql server 2008 R2安装包一个(从网上下载) 方法/步骤: 安装包解压 解压后,打开setup.exe文件,选择安装,显示如图: 选择全新安装或向现有安装添加功能 点确定 输入 企业版序列号:R88PF-GMCFT-KM2KR-4R7GB-43K4B 点我接受许可条款,点下一步 点安装,如果操作系统没有安装.net framet3.5 sp1 ,将会自动安装. 必备环境全部通过后,点下一步

  • 详解SQL Server 2008工具SQL Server Profiler

    一.SQL Profiler工具简介 SQL Profiler是一个图形界面和一组系统存储过程,其作用如下: 1.图形化监视SQL Server查询: 2.在后台收集查询信息: 3.分析性能: 4.诊断像死锁之类的问题: 5.调试T-SQL语句: 6.模拟重放SQL Server活动: 也可以使用SQL Profiler捕捉在SQL Server实例上执行的活动.这样的活动被称为Profiler跟踪. 1.Profiler跟踪 从开始=>所有程序=>Microsoft SQL Server 2

  • 详解SQL Server中的事务与锁问题

    一  概述 在数据库方面,对于非DBA的程序员来说,事务与锁是一大难点,针对该难点,本篇文章试图采用图文的方式来与大家一起探讨. "浅谈SQL Server 事务与锁"这个专题共分两篇,上篇主讲事务及事务一致性问题,并简略的提及一下锁的种类和锁的控制级别. 下篇主讲SQL Server中的锁机制,锁控制级别和死锁的若干问题. 二   事务 1   何为事务 预览众多书籍,对于事务的定义,不同文献不同作者对其虽有细微差别却大致统一,我们将其抽象概括为: 事务:指封装且执行单个或多个操作的

  • 详解SQL Server数据库链接查询的方式

    SQL Server数据库链接查询的方式的相关知识是本文我们主要要介绍的内容,我们知道,通过连接运算符可以实现多个表查询.连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志.多表连接查询是使用Sql的基本操作,但连接的方式却有多种,熟练使用这些连接方式能够简化Sql语句,提高数据库运行效率. 在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中.当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息.连接操作给用户带来很

  • 详解SQL Server中的数据类型

    前言 前面几篇文章我们讲解了索引有关知识,这一节我们再继续我们下面内容讲解,简短的内容,深入的理解. 数据类型 SQL Server支持两种字符数据类型,一种是常规,另外一种则是Unicode.常规数据类型包括CHAR和VARCHAR,Unicode数据类型包括NCAHR和NVARCHAR.常规字符的每个字符使用1个字节存储,而Unicode数据的每个字符要求2个字节.常规字符列限制为仅仅只针对于英语,而Unicode则是针对于多种语言.两种字符数据类型的文本表示方式也不相同,在表示常规字符文本

  • 详解SQL Server的简单查询语句

    前言 对于一些原理性文章园中已有大量的文章尤其是关于索引这一块,我也是花费大量时间去学习,对于了解索引原理对于后续理解查询计划和性能调优有很大的帮助,而我们只是一些内容进行概括和总结,这一节我们开始正式步入学习SQL中简单的查询语句,简短的内容,深入的理解. 简单查询语句 所有复杂的语句都是由简单的语句组成基本都是由SELECT.FROM.WHERE.GROUP BY.HAVING.ORDER BY等组成,当然还包括一些谓词等等.比如当我们要查询某表中所有数据时我们会像如下进行. SELECT

随机推荐

其他