本文我們通過(guò)SQL Server 2016一個(gè)實(shí)例數(shù)據(jù)表，給大家詳細(xì)分析了快照代理過(guò)程遇到的問(wèn)題和解決辦法，并對(duì)快照生成過(guò)程做了詳細(xì)說(shuō)明，以下是全部?jī)?nèi)容：

　　概述

　　快照代理準(zhǔn)備已發(fā)布表的架構(gòu)和初始數(shù)據(jù)文件以及其他對(duì)象、存儲(chǔ)快照文件并記錄分發(fā)數(shù)據(jù)庫(kù)中的同步信息。 快照代理在分發(fā)服務(wù)器上運(yùn)行;SQLServer2016版本對(duì)快照代理做了一些比較好的優(yōu)化，接下來(lái)詳細(xì)了解一下快照的執(zhí)行過(guò)程。

一、快照代理文件

　　在執(zhí)行快照作業(yè)是會(huì)在指定的快照目錄生成4種類(lèi)型的文件。

　　BCP文件：發(fā)布對(duì)象的數(shù)據(jù)文件。

　　IDX文件：索引創(chuàng)建腳本文件

　　PRE文件：復(fù)制快照腳本文件。

　　SCH文件：架構(gòu)創(chuàng)建腳本文件

二、默認(rèn)快照代理配置文件

　　-BcpBachSize:每一次執(zhí)行bcp操作copy的最大記錄行數(shù)，默認(rèn)是10萬(wàn)行。

　　-HistoryVerboseLevel:指定在快照操作過(guò)程中記錄的歷史記錄大小。

　　-LoginTimeout:登錄超時(shí)前等待的秒數(shù)。 默認(rèn)值為 15 秒。

　　-QueryTimeOut:查詢超時(shí)前等待的秒數(shù)。默認(rèn)值為 1800 秒

　　備注：通過(guò)右鍵快照代理-快照代理配置文件;可以配置快照代理。

三、對(duì)比不同版本快照代理

　　接下來(lái)測(cè)試對(duì)比2億的記錄表生成快照

　　1.bcp文件數(shù)量對(duì)比

　　2008R2

　　2016SP1

　　這里重點(diǎn)說(shuō)一下BCP文件，因?yàn)閼?yīng)用快照到訂閱服務(wù)器是以BCP文件為基本單位，也就是說(shuō)不管你的BCP文件有多大都是一次性bulk到訂閱服務(wù)器，所以BCP文件越大每次應(yīng)用的時(shí)間就會(huì)越長(zhǎng)。如果一個(gè)BCP文件太大可能會(huì)導(dǎo)致插入到訂閱端失敗。

　　從上圖可以看到同樣是2億的記錄，2008R2總共有8個(gè)BCP文件，而且最大的BCP文件大小將近1G其它的都才幾兆;2016有16個(gè)BCP文件，并且前15個(gè)都是50M左右數(shù)據(jù)比較均勻。接下來(lái)看下圖的每個(gè)BCP文件的記錄對(duì)比。

　　2.快照生成詳細(xì)過(guò)程對(duì)比

　　2008r2

　　2016SP1

　　從生成的BCP文件記錄對(duì)比來(lái)看：

　　2008R2：前7個(gè)文件每個(gè)文件記錄數(shù)大概70萬(wàn)左右，最后一個(gè)文件記錄1.1億。

　　2016：前15個(gè)文件每個(gè)文件記錄700萬(wàn)左右，最后一個(gè)文件78萬(wàn)。

　　說(shuō)明：

　　2008R2前7個(gè)文件每個(gè)文件大概存儲(chǔ)的記錄量是70萬(wàn)剩下的記錄都會(huì)存儲(chǔ)到最后一個(gè)文件,所以2008R2比較適合的表記錄數(shù)是600萬(wàn)左右。

　　2016前15個(gè)文件每個(gè)文件大概存儲(chǔ)的記錄量是700萬(wàn)剩下的記錄都會(huì)存儲(chǔ)到最后一個(gè)文件，2016適合的表記錄數(shù)1.2億左右。

　　共同缺點(diǎn)：表記錄超過(guò)“適合的復(fù)制表記錄數(shù)”后剩下數(shù)據(jù)會(huì)全部存儲(chǔ)到最后個(gè)bcp文件中。

　　3.分發(fā)對(duì)比

　　接下來(lái)看一下分發(fā)的詳細(xì)過(guò)程

　　從2008R2分發(fā)記錄過(guò)程中可以看到每次BULK都是以bcp文件為單位，復(fù)制最后一個(gè)bcp文件花費(fèi)了大概22分鐘，而前面的每個(gè)文件都是十幾秒;還是由于我當(dāng)前的表只有三個(gè)字段而且除了主鍵沒(méi)有索引否則的時(shí)間就更長(zhǎng)了。

四、快照生成過(guò)程

　　復(fù)制快照代理是一個(gè)可執(zhí)行文件，用于準(zhǔn)備快照文件(其中包含已發(fā)布表和數(shù)據(jù)庫(kù)對(duì)象的架構(gòu)及數(shù)據(jù))，然后將這些文件存儲(chǔ)在快照文件夾中，并在分發(fā)數(shù)據(jù)庫(kù)中記錄同步作業(yè)。

　　從上圖可以了解整個(gè)快照的生成過(guò)程。

五、語(yǔ)法

　　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]

　　參數(shù)

　　-?

　　輸出所有可用的參數(shù)。

　　-Publisherserver_name[\instance_name]

　　發(fā)布服務(wù)器的名稱。為該服務(wù)器上的MicrosoftSQL Server默認(rèn)實(shí)例指定 server_name。為該服務(wù)器上的server_name\instance_nameinstance_nameSQL Server默認(rèn)實(shí)例指定 server_name。

　　-Publication發(fā)布

　　發(fā)布的名稱。只有將發(fā)布設(shè)置為總是使快照可用于新訂閱或重新初始化的訂閱時(shí)，此參數(shù)才有效。

　　-70Subscribers

　　如果有任何訂閱服務(wù)器在運(yùn)行SQL Server7.0 版，則必須使用此參數(shù)。

　　-BcpBatchSizebcpbatch\size

　　在一次大容量復(fù)制操作中發(fā)送的行數(shù)。執(zhí)行bcp in操作時(shí)，批的大小為要作為一個(gè)事務(wù)發(fā)送到服務(wù)器的行數(shù)，并且也是分發(fā)代理記錄bcp進(jìn)度消息之前必須發(fā)送的行數(shù)。當(dāng)執(zhí)行bcp out操作時(shí)，將使用固定批大小 1000。值為 0 表示不記錄任何消息。

　　-DefinitionFiledef_path_and_file_name

　　代理定義文件的路徑。代理定義文件中包含該代理的命令行參數(shù)。文件的內(nèi)容被當(dāng)作可執(zhí)行文件進(jìn)行分析。使用雙引號(hào) (") 指定包含任意字符的參數(shù)值。

　　-Distributorserver_name[\instance_name]

　　分發(fā)服務(wù)器名稱。為該服務(wù)器上的默認(rèn)實(shí)例指定server_nameSQL Server。為該服務(wù)器上的server_name\instance_nameinstance_nameSQL Server默認(rèn)實(shí)例指定 server_name。

　　-DistributorDeadlockPriority[-1|0|1]

　　死鎖發(fā)生時(shí)快照代理連接到分發(fā)服務(wù)器的優(yōu)先級(jí)。指定此參數(shù)是為了解決快照生成期間在快照代理和用戶應(yīng)用程序之間發(fā)生的死鎖問(wèn)題。

　　DistributorDeadlockPriority 值

　　說(shuō)明

　　-1

　　在分發(fā)服務(wù)器上發(fā)生死鎖時(shí)，應(yīng)用程序而非快照代理優(yōu)先。

　　0(默認(rèn)值)

　　未分配優(yōu)先級(jí)。

　　1

　　在分發(fā)服務(wù)器上發(fā)生死鎖時(shí)，快照代理優(yōu)先。

　　-DistributorLogindistributor_login

　　使用SQL Server身份驗(yàn)證連接到分發(fā)服務(wù)器時(shí)所用的登錄名。

　　-DistributorPassworddistributor_password

　　使用SQL Server身份驗(yàn)證連接到分發(fā)服務(wù)器時(shí)使用的密碼。。

　　-DistributorSecurityMode[0|1]

　　指定分發(fā)服務(wù)器的安全模式。值0指示SQL Server身份驗(yàn)證模式(默認(rèn)設(shè)置)，值1指示 Windows 身份驗(yàn)證模式。

　　-DynamicFilterHostNamedynamic_filter_host_name

　　在創(chuàng)建動(dòng)態(tài)快照時(shí)，用來(lái)為篩選中的HOST_NAME (Transact-SQL)設(shè)置值。例如，如果為項(xiàng)目指定了子集篩選器子句rep_id = HOST_NAME()，并且在調(diào)用合并代理之前將DynamicFilterHostName屬性設(shè)置為“FBJones”，則只會(huì)復(fù)制rep_id列中具有“FBJones”的行。

　　-DynamicFilterLogindynamic_filter_login

　　在創(chuàng)建動(dòng)態(tài)快照時(shí)，用來(lái)為篩選中的SUSER_SNAME (Transact-SQL)設(shè)置值。例如，如果為項(xiàng)目指定了子集篩選器子句user_id = SUSER_SNAME()，并且在調(diào)用SQLSnapshot對(duì)象的Run方法之前將DynamicFilterLogin屬性設(shè)置為“rsmith”，則只將user_id列中具有“rsmith”的行包括在快照中。

　　-DynamicSnapshotLocationdynamic_snapshot_location

　　應(yīng)生成動(dòng)態(tài)快照的位置。

　　-EncryptionLevel[0|1|2]

　　建立連接時(shí)快照代理使用的安全套接字層 (SSL) 加密的等級(jí)。

　　EncryptionLevel 值

　　說(shuō)明

　　0

　　指定不使用 SSL。

　　1

　　指定使用 SSL，但是代理不驗(yàn)證 SSL 服務(wù)器證書(shū)是否已由可信的頒發(fā)者進(jìn)行簽名。

　　2

　　指定使用 SSL，并驗(yàn)證證書(shū)。

　　-FieldDelimiterfield_delimiter

　　在SQL Server大容量復(fù)制數(shù)據(jù)文件中用于標(biāo)記字段末尾的字符或字符序列。默認(rèn)值為 \n\n。

　　-HistoryVerboseLevel[1|2|3]

　　指定在快照操作過(guò)程中記錄的歷史記錄大小。選擇1可將歷史日志記錄對(duì)性能的影響減至最小。

　　HistoryVerboseLevel 值

　　說(shuō)明

　　0

　　進(jìn)度消息將寫(xiě)入控制臺(tái)或輸出文件。不在分發(fā)數(shù)據(jù)庫(kù)中記錄歷史記錄。

　　1

　　總是更新具有相同狀態(tài)(啟動(dòng)、進(jìn)行中、成功等)的上一歷史記錄消息。如果不存在狀態(tài)相同的上一記錄，將插入新記錄。

　　2(默認(rèn)值)

　　除非記錄為空閑消息或長(zhǎng)時(shí)間運(yùn)行的作業(yè)消息等信息(此時(shí)將更新上一記錄)，否則插入新的歷史記錄。

　　3

　　始終插入新記錄，除非它與空閑消息有關(guān)。

　　-HRBcpBlocksnumber_of_blocks

　　在編寫(xiě)器線程和讀取器線程之間排隊(duì)的bcp數(shù)據(jù)塊的數(shù)量。默認(rèn)值為 50。HRBcpBlocks僅用于 Oracle 發(fā)布。

　　備注

　　此參數(shù)用于通過(guò) Oracle 發(fā)布服務(wù)器優(yōu)化bcp的性能。

　　-HRBcpBlockSizeblock_size

　　每個(gè)bcp數(shù)據(jù)塊的大小(以 KB 為單位)。默認(rèn)值為 64 KB。HRBcpBlocks僅用于 Oracle 發(fā)布。

　　備注

　　此參數(shù)用于通過(guò) Oracle 發(fā)布服務(wù)器優(yōu)化bcp的性能。

　　-HRBcpDynamicBlocks

　　每個(gè)bcp數(shù)據(jù)塊的大小是否可以動(dòng)態(tài)增長(zhǎng)。HRBcpBlocks僅用于 Oracle 發(fā)布。

　　備注

　　此參數(shù)用于通過(guò) Oracle 發(fā)布服務(wù)器優(yōu)化bcp的性能。

　　-KeepAliveMessageIntervalkeep_alive_interval

　　快照代理在向MSsnapshot_history表中記錄“waiting for backend message”之前等待的時(shí)間(以秒為單位)。默認(rèn)值為 300 秒。

　　-LoginTimeOutlogin_time_out_seconds

　　登錄超時(shí)前等待的秒數(shù)。 默認(rèn)值為 15 秒。

　　-MaxBcpThreadsnumber_of_threads

　　指定可以并行執(zhí)行的大容量復(fù)制操作的數(shù)量。同時(shí)存在的線程和 ODBC 連接的最大數(shù)量為MaxBcpThreads或顯示在分發(fā)數(shù)據(jù)庫(kù)中同步事務(wù)中的大容量復(fù)制請(qǐng)求數(shù)中較小的那一個(gè)。MaxBcpThreads的值必須大于0，并且不存在任何硬編碼的上限。默認(rèn)值為1。

　　-MaxNetworkOptimization[0|1]

　　是否將無(wú)關(guān)刪除操作發(fā)送到訂閱服務(wù)器。無(wú)關(guān)刪除操作是針對(duì)不屬于訂閱服務(wù)器分區(qū)的行發(fā)送到訂閱服務(wù)器的 DELETE 命令。無(wú)關(guān)刪除操作不會(huì)影響數(shù)據(jù)的完整性或收斂，但它們會(huì)導(dǎo)致不必要的網(wǎng)絡(luò)通信。MaxNetworkOptimization的默認(rèn)值是0。將MaxNetworkOptimization設(shè)置為1可將不相關(guān)的刪除操作發(fā)生的機(jī)會(huì)減至最小，從而減少網(wǎng)絡(luò)通信，并最大程度地優(yōu)化網(wǎng)絡(luò)。如果存在多個(gè)級(jí)別的聯(lián)接篩選器和復(fù)雜子集篩選器，則將此參數(shù)設(shè)置為1還會(huì)增加元數(shù)據(jù)的存儲(chǔ)并導(dǎo)致發(fā)布服務(wù)器性能下降。您應(yīng)仔細(xì)評(píng)估您的復(fù)制拓?fù)?，僅當(dāng)無(wú)關(guān)刪除操作導(dǎo)致的網(wǎng)絡(luò)通信高到無(wú)法接受時(shí)才應(yīng)將MaxNetworkOptimization設(shè)置為1。

　　備注

　　僅當(dāng)合并發(fā)布的同步優(yōu)化選項(xiàng)(sp_addmergepublication (Transact-SQL)的@keep_partition_changes參數(shù))設(shè)置為true時(shí)，將此參數(shù)設(shè)置為1才是有用的。

　　-Outputoutput_path_and_file_name

　　代理輸出文件的路徑。如果未提供文件名，則向控制臺(tái)發(fā)送該輸出。如果指定的文件名已存在，會(huì)將輸出追加到該文件。

　　-OutputVerboseLevel[0|1|2]

　　指定輸出是否應(yīng)提供詳細(xì)內(nèi)容。

　　OutputVerboseLevel 值

　　說(shuō)明

　　0

　　僅輸出錯(cuò)誤消息。

　　1(默認(rèn)值)

　　輸出所有進(jìn)度報(bào)告消息(默認(rèn)值)。

　　2

　　輸出所有錯(cuò)誤消息和進(jìn)度報(bào)告消息，這對(duì)于調(diào)試很有用。

　　-PacketSizepacket_size

　　快照代理連接到SQL Server時(shí)使用的數(shù)據(jù)包大小(以字節(jié)為單位)。默認(rèn)值為 8192 字節(jié)。

　　備注

　　除非您確信能夠提高性能，否則不要更改數(shù)據(jù)包的大小。對(duì)于大多數(shù)應(yīng)用程序而言，默認(rèn)數(shù)據(jù)包大小為最佳數(shù)值。

　　-ProfileNameprofile_name

　　指定用于代理參數(shù)的代理配置文件。如果ProfileName為 NULL，則將禁用代理配置文件。如果未指定ProfileName，則使用該代理類(lèi)型的默認(rèn)配置文件。

　　-PublisherDBpublisher_database

　　發(fā)布數(shù)據(jù)庫(kù)的名稱。Oracle 發(fā)布服務(wù)器不支持該參數(shù)。

　　-PublisherDeadlockPriority[-1|0|1]

　　死鎖發(fā)生時(shí)快照代理連接到發(fā)布服務(wù)器的優(yōu)先級(jí)。指定此參數(shù)是為了解決快照生成期間在快照代理和用戶應(yīng)用程序之間發(fā)生的死鎖問(wèn)題。

　　PublisherDeadlockPriority 值

　　說(shuō)明

　　-1

　　在發(fā)布服務(wù)器上發(fā)生死鎖時(shí)，應(yīng)用程序而非快照代理優(yōu)先。

　　0(默認(rèn)值)

　　未分配優(yōu)先級(jí)。

　　1

　　在發(fā)布服務(wù)器上發(fā)生死鎖時(shí)，快照代理優(yōu)先。

　　-PublisherFailoverPartnerserver_name[\instance_name]

　　指定參加與發(fā)布數(shù)據(jù)庫(kù)進(jìn)行的數(shù)據(jù)庫(kù)鏡像會(huì)話的SQL Server故障轉(zhuǎn)移伙伴實(shí)例。

　　-PublisherLoginpublisher_login

　　使用SQL Server身份驗(yàn)證連接到發(fā)布服務(wù)器時(shí)所用的登錄名。

　　-PublisherPasswordpublisher_password

　　使用SQL Server身份驗(yàn)證連接到發(fā)布服務(wù)器時(shí)使用的密碼。。

　　-PublisherSecurityMode[0|1]

　　指定發(fā)布服務(wù)器的安全模式。值0指示SQL Server身份驗(yàn)證(默認(rèn)值)，值1指示 Windows 身份驗(yàn)證模式。

　　-QueryTimeOutquery_time_out_seconds

　　查詢超時(shí)前等待的秒數(shù)。默認(rèn)值為 1800 秒。

　　-ReplicationType[1|2]

　　指定復(fù)制的類(lèi)型。值1指示事務(wù)復(fù)制，值2指示合并復(fù)制。

　　-RowDelimiterrow_delimiter

　　在SQL Server大容量復(fù)制數(shù)據(jù)文件中用于標(biāo)記行尾的字符或字符序列。默認(rèn)值為 \n<,@g>\n。

　　-StartQueueTimeoutstart_queue_timeout_seconds

　　當(dāng)運(yùn)行的并發(fā)動(dòng)態(tài)快照進(jìn)程數(shù)達(dá)到由sp_addmergepublication (Transact-SQL)的@max_concurrent_dynamic_snapshots屬性設(shè)置的限制值時(shí)，快照代理等待的最大秒數(shù)。如果在經(jīng)過(guò)最大秒數(shù)之后快照代理仍在等待，快照代理將退出。值 0 表示代理將無(wú)限期地等待，盡管可以將其取消。

　　-UsePerArticleContentsViewuse_per_article_contents_view

　　已不推薦使用此參數(shù)，支持它是為了能夠向后兼容。

總結(jié)

　　由于在生成快照需要擁有對(duì)象的架構(gòu)鎖，所以在生成快照的過(guò)程中表對(duì)象是只讀的。如果對(duì)大表生成快照千萬(wàn)不要選擇在業(yè)務(wù)繁忙的時(shí)候否則有可能造成系統(tǒng)癱瘓，2016生成快照的時(shí)間比2008要快很多。通過(guò)對(duì)比可以發(fā)現(xiàn)2016的復(fù)制生成快照比2008性能提升了很多。但是從2014到2016BCP文件從32個(gè)變成16個(gè)不知道是出于什么原因。

　　原文鏈接：http://www.cnblogs.com/chenmh/p/7895991.html