SQL Server 2016快照代理的示例分析

這篇文章主要介紹SQL Server 2016快照代理的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

概述

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

一、快照代理文件

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2008R2

2016SP1

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

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

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

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億左右。

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

3.分發(fā)對比

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

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

四、快照生成過程

復(fù)制快照代理是一個可執(zhí)行文件，用于準(zhǔn)備快照文件（其中包含已發(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ù)。

-Publisher server_name[\instance_name]

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

-Publication 發(fā)布

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

-70Subscribers

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

-BcpBatchSize bcp batch\ size

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

-DefinitionFile def_path_and_file_name

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

-Distributor server_name[\instance_name]

分發(fā)服務(wù)器名稱。 為該服務(wù)器上的 默認(rèn)實例指定 server_name SQL Server 。 為該服務(wù)器上的 server_name\instance_name instance_name SQL Server 默認(rèn)實例指定 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 （默認(rèn)值）

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

1

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

-DistributorLogin distributor_login

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

-DistributorPassword distributor_password

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

-DistributorSecurityMode [ 0| 1]

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

-DynamicFilterHostName dynamic_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”的行。

-DynamicFilterLogin dynamic_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”的行包括在快照中。

-DynamicSnapshotLocation dynamic_snapshot_location

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

-EncryptionLevel [ 0 | 1 | 2 ]
建立連接時快照代理使用的安全套接字層 (SSL) 加密的等級。

EncryptionLevel 值

說明

0

指定不使用 SSL。

1

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

2

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

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

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

HistoryVerboseLevel 值

說明

0

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

1

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

2 （默認(rèn)值）

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

3

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

-HRBcpBlocks number_of_blocks

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

備注

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

-HRBcpBlockSizeblock_size

每個 bcp 數(shù)據(jù)塊的大小（以 KB 為單位）。 默認(rèn)值為 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 的性能。

-KeepAliveMessageInterval keep_alive_interval

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

-LoginTimeOut login_time_out_seconds

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

-MaxBcpThreads number_of_threads

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

- MaxNetworkOptimization [ 0| 1]

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

備注

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

-Output output_path_and_file_name

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

-OutputVerboseLevel [ 0| 1| 2]

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

OutputVerboseLevel 值

說明

0

僅輸出錯誤消息。

1 （默認(rèn)值）

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

2

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

-PacketSize packet_size

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

備注

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

-ProfileName profile_name

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

-PublisherDB publisher_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 （默認(rèn)值）

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

1

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

-PublisherFailoverPartner server_name[\instance_name]

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

-PublisherLogin publisher_login

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

-PublisherPassword publisher_password

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

-PublisherSecurityMode [ 0| 1]

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

-QueryTimeOut query_time_out_seconds

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

-ReplicationType [ 1| 2]

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

-RowDelimiter row_delimiter

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

-StartQueueTimeout start_queue_timeout_seconds

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

- UsePerArticleContentsView use_per_article_contents_view

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

以上是“SQL Server 2016快照代理的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！