SQL Server 2016快照代理程式詳解

本文給大家詳細分析了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]

#分發伺服器名稱。

#-DistributorDeadlockPriority [-1|0|1]死鎖發生時快照代理程式連接到分發伺服器的優先權。使用者應用程式之間發生的死鎖問題。 DistributorDeadlockPriority 值說明

#-1

在分發伺服器上發生死鎖時，應用程式而非快照代理優先。

-DistributorLogin distributor_login使用 SQL Server 身分驗證連線到分發伺服器時所使用的登入名稱。 distributor_password

使用 SQL Server 身分驗證連線到分送伺服器時所使用的密碼。指定分發伺服器的安全模式。 ##在建立動態快照時，用來為篩選中的 HOST_NAME (Transact-SQL) 設定值。屬性設定為“FBJones”，則只會複製 rep_id 列中具有“FBJones”的行。

-DynamicFilterLogin 

dynamic_filter_login

在建立動態快照時，用來為篩選中的 SUSER_SNAME (Transact-SQL) 設定值。例如，如果為專案指定了子集篩選器子句 user_id = SUSER_SNAME() ，並且在呼叫 SQLSnapshot 物件的 Run 方法之前將 DynamicFilterLogin 在快照中。

-DynamicSnapshotLocation 

dynamic_snapshot_location

應產生動態快照的位置。

-EncryptionLevel [ 0 | 1 | 2 ]

建立連線時快照代理程式所使用的安全通訊端層 (SSL) 加密的等級。

#

#EncryptionLevel 值

說明

#0

指定不使用 SSL。

1

指定使用 SSL，但是代理程式不驗證 SSL 伺服器憑證是否已由可信任的頒發者進行簽署。

2

指定使用 SSL，並驗證憑證。

-FieldDelimiter field_delimiter
在 SQL Server 大容量複製資料檔案中用於標記欄位末端的字元或字元序列。 預設值為 \n\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值 (Transact-SQL) 的 @max_concurrent_dynamic_snapshots值時，屬性設定的屬性限制快照代理程式等待的最大秒數。 如果在經過最大秒數之後快照代理仍在等待，快照代理將退出。 值 0 表示代理將無限期地等待，儘管可以將其取消。

- UsePerArticleContentsView 

use_per_article_contents_view

已不建議使用此參數，支援它是為了能夠向後相容。

SQL server 資料庫的資料完整性

SQL server約束

總結SQL Server中常用函式使用方法

以上是SQL Server 2016快照代理程式詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！