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

　　概述

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

一、快照代理文件

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

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

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

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

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

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

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

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

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

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

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

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

　　接下來測試對比2億的記錄表生成快照

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

　　2008R2

　　2016SP1

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

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

　　2.快照生成詳細過程對比

　　2008r2

　　2016SP1

　　從生成的BCP文件記錄對比來看：

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

　　2016：前15個文件每個文件記錄700萬左右，最后一個文件78萬。

　　說明：

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

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

　　共同缺點：表記錄超過“適合的復(fù)制表記錄數(shù)”后剩下數(shù)據(jù)會全部存儲到最后個bcp文件中。

　　3.分發(fā)對比

　　接下來看一下分發(fā)的詳細過程

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

四、快照生成過程

　　復(fù)制快照代理是一個可執(zhí)行文件，用于準備快照文件(其中包含已發(fā)布表和數(shù)據(jù)庫對象的架構(gòu)及數(shù)據(jù))，然后將這些文件存儲在快照文件夾中，并在分發(fā)數(shù)據(jù)庫中記錄同步作業(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默認實例指定 server_name。為該服務(wù)器上的server_name\instance_nameinstance_nameSQL Server默認實例指定 server_name。

　　-Publication發(fā)布

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

　　-70Subscribers

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

　　-BcpBatchSizebcpbatch\size

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

　　-DefinitionFiledef_path_and_file_name

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

　　-Distributorserver_name[\instance_name]

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

　　-DistributorDeadlockPriority[-1|0|1]

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

　　DistributorDeadlockPriority 值

　　說明

　　-1

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

　　0(默認值)

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

　　1

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

　　-DistributorLogindistributor_login

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

　　-DistributorPassworddistributor_password

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

　　-DistributorSecurityMode[0|1]

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

　　-DynamicFilterHostNamedynamic_filter_host_name

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

　　-DynamicFilterLogindynamic_filter_login

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

　　-DynamicSnapshotLocationdynamic_snapshot_location

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

　　-EncryptionLevel[0|1|2]

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

　　EncryptionLevel 值

　　說明

　　0

　　指定不使用 SSL。

　　1

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

　　2

　　指定使用 SSL，并驗證證書。

　　-FieldDelimiterfield_delimiter

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

　　-HistoryVerboseLevel[1|2|3]

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

　　HistoryVerboseLevel 值

　　說明

　　0

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

　　1

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

　　2(默認值)

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

　　3

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

　　-HRBcpBlocksnumber_of_blocks

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

　　備注

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

　　-HRBcpBlockSizeblock_size

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

　　備注

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

　　-HRBcpDynamicBlocks

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

　　備注

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

　　-KeepAliveMessageIntervalkeep_alive_interval

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

　　-LoginTimeOutlogin_time_out_seconds

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

　　-MaxBcpThreadsnumber_of_threads

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

　　-MaxNetworkOptimization[0|1]

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

　　備注

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

　　-Outputoutput_path_and_file_name

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

　　-OutputVerboseLevel[0|1|2]

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

　　OutputVerboseLevel 值

　　說明

　　0

　　僅輸出錯誤消息。

　　1(默認值)

　　輸出所有進度報告消息(默認值)。

　　2

　　輸出所有錯誤消息和進度報告消息，這對于調(diào)試很有用。

　　-PacketSizepacket_size

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

　　備注

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

　　-ProfileNameprofile_name

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

　　-PublisherDBpublisher_database

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

　　-PublisherDeadlockPriority[-1|0|1]

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

　　PublisherDeadlockPriority 值

　　說明

　　-1

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

　　0(默認值)

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

　　1

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

　　-PublisherFailoverPartnerserver_name[\instance_name]

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

　　-PublisherLoginpublisher_login

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

　　-PublisherPasswordpublisher_password

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

　　-PublisherSecurityMode[0|1]

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

　　-QueryTimeOutquery_time_out_seconds

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

　　-ReplicationType[1|2]

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

　　-RowDelimiterrow_delimiter

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

　　-StartQueueTimeoutstart_queue_timeout_seconds

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

　　-UsePerArticleContentsViewuse_per_article_contents_view

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

總結(jié)

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

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