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]
死鎖發生時快照代理連接到分發伺服器的優先順序。 指定此參數是為瞭解決快照生成期間在快照代理和用戶應用程式之間發生的死鎖問題。

-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) 加密的等級。

-FieldDelimiter field_delimiter
在 SQL Server 大容量複製數據文件中用於標記欄位末尾的字元或字元序列。 預設值為 \n<x$3>\n。

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

-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]
指定輸出是否應提供詳細內容。

-PacketSize packet_size
快照代理連接到 SQL Server時使用的數據包大小（以位元組為單位）。 預設值為 8192 位元組。

備註

除非您確信能夠提高性能，否則不要更改數據包的大小。 對於大多數應用程式而言，預設數據包大小為最佳數值。

-ProfileName profile_name
指定用於代理參數的代理配置文件。 如果 ProfileName 為 NULL，則將禁用代理配置文件。 如果未指定 ProfileName ，則使用該代理類型的預設配置文件。 

-PublisherDB publisher_database
發佈資料庫的名稱。 Oracle 發佈伺服器不支持該參數。

-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個不知道是出於什麼原因。