設定複製活動中的 SQL Server

本文概述如何在資料管線中使用複製活動，從 SQL Server 資料庫複製資料及將資料複製到該處。

支援的設定

對於複製活動下每個索引標籤的組態，請分別參閱下列各節。

• 一般
• 來源
• 目的地
• 對應
• 設定

一般

請參閱＜[一般] 設定＞指導，來設定 [一般] 設定索引標籤。

來源

在複製活動的 [來源] 索引標籤下，SQL Server 支援下列屬性。

以下是必要的屬性：

• 資料存放區類型：選取 [外部]。

• 連線：從連線清單中選取 SQL Server 連線。 如果連線不存在，請選取 [新增] 來建立新的 SQL Server 連線。

• 連線類型：選取 [SQL Server]。

• 使用查詢：指定讀取資料的方式。 可以選擇 [資料表]、[查詢] 或 [預存程序]。 下列清單描述每個設定的組態：

  • 資料表：從指定的資料表讀取資料。 從下拉式清單中選取來源資料表，或選取 [編輯] 以手動輸入。

  • 查詢：指定自訂 SQL 查詢來讀取資料。 例如 select * from MyTable。 或選取鉛筆圖示以在程式碼編輯器中編輯。

    

  • 預存程序：使用從來源資料表讀取資料的預存程序名稱。 最後一個 SQL 陳述式必須是預存程序中的 SELECT 陳述式。

    • 預存程序名稱：選取預存程序，或在選取 [編輯] 以從源資料表讀取資料時手動指定預存程序名稱。

    • 預存程序參數：指定預存程序參數的值。 允許的值為名稱或值組。 參數的名稱和大小寫必須符合預存程序參數的名稱和大小寫。 可以選取 [匯入參數] 以取得預存程序參數。

      

在 [進階] 下，可以指定下列欄位：

• 查詢逾時 (分鐘)：指定查詢命令執行的逾時，預設值為 120 分鐘。 如果為此屬性設定參數，允許的值為時間範圍，例如 "02:00:00" (120 分鐘)。

• 隔離等級：指定 SQL 來源的異動鎖定行為。 允許的值為：[無]、[讀取認可]、[讀取未認可]、[可重複讀取]、[可序列化] 或 [快照]。 如果未指定，則會使用資料庫的預設隔離等級。 如需詳細資料，請參閱＜IsolationLevel 列舉＞。

  

• 分割選項：指定用來從 SQL Server 載入資料的資料分割選項。 允許的值為：[無] (預設值)、[資料表的實體分割] 及 [動態範圍]。 啟用分割選項後 (即不是 [無])，從 SQL Server 同時載入資料的平行處理程度，將由 [複製活動設定] 索引標籤中的 [複製平行處理程度] 控制。

  • 無：選擇此設定不要使用分割。

  • 資料表的實體分割：使用實體分割時，分割資料行和機制會根據實體資料表定義自動決定。

  • 動態範圍：使用已啟用平行的查詢時，需要定界分割參數 (?DfDynamicRangePartitionCondition)。 範例查詢：SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition。

    • 分割資料行名稱：以整數類型或日期/日期時間類型 (int、smallint、bigint、date、smalldatetime、datetime、datetime2 或 datetimeoffset) 指定來源資料行的名稱，供平行複製的定界分割使用。 如果未指定，則會自動偵測資料表的索引或主索引鍵作為分割資料行。

      如果您使用查詢來取出來源資料，請在 WHERE 子句中加上 ?DfDynamicRangePartitionCondition。 如需範例，請參閱從 SQL 資料庫平行複製一節。

    • 分割上限：分割區範圍分割的分割資料行最大值。 這個值用於決定分割區的跨距，而不是用於篩選資料表中的資料列。 資料表或查詢結果中的所有資料列都會進行分割和複製。 如果未指定，複製活動會自動偵測該值。 如需範例，請參閱從 SQL 資料庫平行複製一節。

    • 分割下限：分割區範圍分割的分割資料行最小值。 這個值用於決定分割區的跨距，而不是用於篩選資料表中的資料列。 資料表或查詢結果中的所有資料列都會進行分割和複製。 如果未指定，複製活動會自動偵測該值。 如需範例，請參閱從 SQL 資料庫平行複製一節。

• 其他資料行：新增其他資料行來儲存來源檔案的相對路徑或靜態值。 後者支援運算式。

請注意下列幾點：

• 如果已為來源指定查詢，複製活動就會針對 SQL Server 來源執行此查詢來取得資料。 如果預存程序接受參數，也可以藉由指定預存程序名稱和預存程序參數來指定預存程序。
• 在來源中使用預存程序來擷取資料時，請注意，如果您的預存程序設計為在傳入不同的參數值時傳回不同的結構描述，在從 UI 匯入結構描述，或使用自動資料表建立將資料複製到 SQL 資料庫時，您可能遇到失敗，或看到非預期的結果。

Destination

在複製活動的 [目的地] 索引標籤下，SQL Server 支援下列屬性。

以下是必要的屬性：

• 資料存放區類型：選取 [外部]。

• 連線：從連線清單中選取 SQL Server 連線。 如果連線不存在，請選取 [新增] 來建立新的 SQL Server 連線。

• 連線類型：選取 [SQL Server]。

• 資料表選項：可以選擇 [使用現有的] 來使用指定的資料表。 或者，選擇 [自動建立資料表]，在來源結構描述中不存在資料表時自動建立目的地資料表，但請注意，當預存程序用作寫入行為時，不支援此選項。

  如果選擇 [使用現有的]：

  • 資料表：從下拉式清單選取目的地資料庫中的資料表。 或勾選 [編輯] 以手動輸入資料表名稱。

  如果選取：[自動建立資料表]：

  • 資料表：指定自動建立目的地資料表的名稱。

在 [進階] 下，可以指定下列欄位：

• 寫入行為：定義來源是來自檔案型資料存放區的檔案時的寫入行為。 可以選擇 [插入]、[Upsert] 或 [預存程序]。

  • 插入：選擇此選項會使用插入寫入行為將資料載入 SQL Server。

  • Upsert：選擇此選項會使用 Upsert 寫入行為將資料載入 SQL Server。

    • 使用 TempDB：指定是否使用全域暫存資料表或實體資料表作為 Upsert 的過渡資料表。 根據預設，服務會使用全域暫存資料表作為過渡資料表，並且會選取此屬性。

      

    • 選取使用者 DB 結構描述：如果未選取 [使用 TempDB]，則在使用實體資料表時指定用來建立過渡資料表的過渡結構描述。

      注意

      您必須具有建立和刪除資料表的權限。 根據預設，過渡資料表會與目的地資料表共用相同的結構描述。

      

    • 索引鍵資料行：指定唯一資料列識別的資料行名稱。 您可以使用單一索引鍵或一系列索引鍵。 如果未指定，則會使用主索引鍵。

  • 預存程序：定義如何將來源資料套用到目標資料表的預存程序名稱。 此預存程序將會依批次叫用。 針對只執行一次且與來源資料無關的作業 (例如刪除或截斷)，請使用 [複製前指令碼] 屬性。

    • 預存程序名稱：選取預存程序，或在檢查 [編輯] 以從來源資料表讀取資料時手動指定預存程序名稱。

    • 預存程序參數：

      • 資料表類型：指定要在預存程序中使用的資料表類型名稱。 複製活動可讓正在移動的資料可用於此資料表類型的暫存資料表。 然後，預存程序程式碼可以合併正在複製的資料與現有的資料。
      • 資料表類型參數名稱：指定預存程序中指定資料表類型的參數名稱。
      • 參數：指定預存程序參數的值。 允許的值為名稱或值組。 參數的名稱和大小寫必須符合預存程序參數的名稱和大小寫。 可以選取 [匯入參數] 以取得預存程序參數。

      

• 大量插入資料表鎖定：選擇 [是] 或 [否] (預設值)。 在資料表上進行大量插入作業期間，使用此設定可改善資料表上的複製效能，且沒有來自多個用戶端的索引。 選取 [插入] 或 [Upsert] 作為寫入行為時，可以指定此屬性。 若要了解有關詳細資訊，請參閱＜BULK INSERT (Transact-SQL)＞。

• 複製前指令碼：指定一個複製活動的指令碼，會在每次執行中將資料寫入到目的地資料表前執行此指令碼。 您可以使用此屬性來清除預先載入的資料。

• 寫入批次逾時：在逾時前等待批次插入作業完成的時間。允許的值為時間範圍。 如果未指定任何值，逾時預設為 “02：00：00”。

• 寫入批次大小：指定每個批次要插入 SQL 資料表的資料列數目。 允許的值為整數 (資料列數目)。 根據預設，服務會依據資料列大小動態決定適當的批次大小。

• 並行連線數上限：在活動執行期間，與資料存放區建立的並行連線數上限。 僅在想要限制並行連線時，才需要指定值。

對應

對於 [對應] 索引標籤組態，如果未套用 SQL Server，並將自動建立資料表作為目的地，請參閱＜對應＞。

如果套用 SQL Server，並將自動建立資料表作為目的地，但 [對應] 中的組態除外，則可以編輯目的地資料行的類型。 選取 [匯入結構描述] 之後，可以在目的地中指定資料行類型。

例如，來源中 [識別碼] 資料行的類型為 int，而對應至目的地資料行時，可以將其變更為 float 類型。

設定

對於 [設定] 索引標籤組態，請參閱＜在 [設定] 索引標籤下進行其他設定＞。

從 SQL 資料庫平行複製

複製活動中 SQL Server 連接器提供內建的資料分割，以平行方式複製資料。 您可以在複製活動的 [來源] 索引標籤上找到資料分割選項。

當您啟用分割複本時，複製活動會平行查詢 SQL Server 來源，以依分割區來載入資料。 平行程度由 [複製活動設定] 索引標籤中的 [複製平行處理程度] 設定所控制。例如，如果將 [複製平行處理程度] 設定為四，服務會根據指定的分割選項和設定同時產生和執行四個查詢，而每個查詢都會從 SQL Server 擷取部分資料。

建議您啟用平行複製與資料分割，特別是從 SQL Server 載入大量資料時。 以下針對各種情節的建議設定。 將資料複製到以檔案為基礎的資料存放區時，建議分成多個檔案來寫入資料夾 (僅指定資料夾名稱)，這樣效能會比寫入單一檔案更好。

使用分割區選項載入資料的最佳做法：

• 選擇獨特的資料行作為分割資料行 (例如主索引鍵或唯一索引鍵) 以避免資料扭曲。
• 如果資料表有內建分割區，請使用分割選項 [資料表的實體分割]，以獲得更佳的效能。

用來檢查實體分割區的範例查詢

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

如果資料表具有實體分割區，您會看到“HasPartition” 顯示為 “yes”，如下所示。

資料表摘要

如需有關 SQL Server 複製活動摘要和詳細資訊，請參閱下表。

來源資訊

目的地資訊

相關內容

• SQL Server 概觀