使用 Azure Data Factory 或 Azure Synapse Analytics 在 SQL Server 複製/貼上和轉換資料

適用於:Azure Data Factory Azure Synapse Analytics

本文概述如何使用 Azure Data Factory 和 Azure Synapse Analytics 管線中的複製活動,在 SQL Server 資料庫複製/貼上資料,並使用資料流程來轉換 SQL Server 資料庫中的資料。 若要了解詳細資訊,請閱讀 Azure Data FactoryAzure Synapse Analytics 的介紹文章。

支援的功能

此 SQL Server 連接器支援下列活動:

您可以將資料從 SQL Server 資料庫複製到任何支援的接收資料存放區。 或者,您也可以將資料從任何支援的來源資料存放區複製到 SQL Server 資料庫。 如需複製活動所支援作為來源或接收器的資料存放區清單,請參閱支援的資料存放區表格。

具體而言,這個 SQL Server 連接器支援:

  • SQL Server 2005 版和更新版本。
  • 使用 SQL 或 Windows 驗證來複製資料。
  • 作為來源時,使用 SQL 查詢或預存程序來擷取資料。 您也可以選擇從 SQL Server 來源平行複製,如需詳細資訊,請參閱從 SQL 資料庫平行複製一節。
  • 作為接收器,如果目的地資料表不存在,則會根據來源架構自動建立;在複製期間會將資料附加至資料表,或叫用具有自訂邏輯的預存程序。

不支援 SQL Server Express LocalDB

必要條件

如果您的資料存放區位於內部部署網路、Azure 虛擬網路或 Amazon 虛擬私人雲端中,則必須設定自我裝載整合執行階段以與其連線。

如果您的資料存放區是受控雲端資料服務,則可使用 Azure Integration Runtime。 如果只能存取防火牆規則中核准的 IP,您可以將 Azure Integration Runtime IP 新增至允許清單。

您也可以使用 Azure Data Factory 中的受控虛擬網路整合執行階段功能來存取內部部署網路,而不需要安裝和設定自我裝載整合執行階段。

如需 Data Factory 支援的網路安全性機制和選項的詳細資訊,請參閱資料存取策略

開始使用

若要透過管線執行複製活動,您可以使用下列其中一個工具或 SDK:

使用 UI 建立 SQL Server 連結服務

使用下列步驟,在 Azure 入口網站 UI 中建立 SQL Server 連結服務。

  1. 瀏覽至 Azure Data Factory 或 Synapse 工作區中的 [管理] 索引標籤,並選取 [連結的服務],然後按一下 [新增]:

  2. 搜尋 SQL,然後選取 SQL Server 連接器。

    Screenshot of the SQL Server connector.

  3. 設定服務詳細資料、測試連線,然後建立新的連結服務。

    Screenshot of configuration for SQL Server linked service.

連接器設定詳細資料

下列各節提供屬性的相關詳細資料,這些屬性是用來定義 SQL Server 資料庫連接器專屬的 Data Factory 和 Synapse 管線實體。

連結服務屬性

以下是針對 SQL Server 已連結服務支援的屬性:

屬性 描述 必要
type 類型屬性必須設定為 SqlServer
connectionString 指定使用 SQL 驗證或 Windows 驗證來連線至 SQL Server 資料庫時所需的 connectionString 資訊。 請參考下列範例。
您也可以將密碼放在 Azure Key Vault。 如果這是 SQL 驗證,則會從連接字串中提取 password 組態。 如需詳細資訊,請參閱表格下方的 JSON 範例和在 Azure Key Vault 中儲存認證
userName 如果您使用 Windows 驗證,請指定使用者名稱。 範例為 domainname\username
密碼 針對使用者名稱指定的使用者帳戶,指定該帳戶的密碼。 將此欄位標記為 SecureString 以將其安全地儲存。 或者,可以參考 Azure Key Vault 中儲存的認證 No
alwaysEncryptedSettings 指定 alwaysencryptedsettings 資訊,讓 Always Encrypted 能夠使用受控身分識別或服務主體來保護儲存在 SQL Server 的敏感性資料。 如需詳細資訊,請參閱表格下方的 JSON 範例和使用 Always Encrypted 一節。 如果未指定,則會停用預設的一律加密設定。
connectVia 用來連線到資料存放區的整合執行階段。 深入了解必要條件一節。 若未指定,則會使用預設 Azure Integration Runtime。

注意

資料流程不支援 Windows 驗證。

提示

如果您遇到錯誤,其錯誤碼為 "UserErrorFailedToConnectToSqlServer",以及「資料庫的工作階段限制為 XXX 並已達到,」訊息,請將 Pooling=false 新增至您的連接字串並再試一次。

範例 1:使用 SQL 驗證

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

範例 2:使用 SQL 驗證搭配 Azure Key Vault 中的密碼

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

範例 3:使用 Windows 驗證

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=True;",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

範例 4:使用 Always Encrypted

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;"
        },
        "alwaysEncryptedSettings": {
            "alwaysEncryptedAkvAuthType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

資料集屬性

如需可用來定義資料集的區段和屬性完整清單,請參閱資料集一文。 本節提供 SQL Server 資料集所支援的屬性清單。

在 SQL Server 資料庫複製/貼上資料時,支援下列屬性:

屬性 描述 必要
type 資料集的類型屬性必須設定為 SqlServerTable
結構描述 結構描述的名稱。 否 (來源);是 (接收)
資料表 資料表/檢視的名稱。 否 (來源);是 (接收)
tableName 具有結構描述的資料表/檢視名稱。 支援此屬性是基於回溯相容性。 對於新的工作負載,請使用 schematable 否 (來源);是 (接收)

範例

{
    "name": "SQLServerDataset",
    "properties":
    {
        "type": "SqlServerTable",
        "linkedServiceName": {
            "referenceName": "<SQL Server linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

複製活動屬性

如需可用來定義活動的區段和屬性完整清單,請參閱管線一文。 本節提供 SQL Server 來源和接收器所支援的屬性清單。

SQL Server 作為來源

提示

若要使用資料分割有效率地從 SQL Server 載入資料,請在從 SQL Server 資料庫平行複製 一節深入了解。

若要從 SQL Server 複製資料,請將複製活動中的來源類型設定為 SqlSource。 複製活動的 [來源] 區段支援下列屬性:

屬性 描述 必要
type 複製活動來源的 type屬性必須設定為 SqlSource
sqlReaderQuery 使用自訂 SQL 查詢來讀取資料。 例如 select * from MyTable
sqlReaderStoredProcedureName 此屬性是從來源資料表讀取資料的預存程序名稱。 最後一個 SQL 陳述式必須是預存程序中的 SELECT 陳述式。
storedProcedureParameters 這些是預存程序的參數。
允許的值為名稱或值組。 參數的名稱和大小寫必須符合預存程序參數的名稱和大小寫。
isolationLevel 指定 SQL 來源的異動鎖定行為。 允許的值為:ReadCommittedReadUncommittedRepeatableReadSerializableSnapshot。 如果未指定,則會使用資料庫的預設隔離等級。 如需詳細資訊,請參閱這篇文件
partitionOptions 指定用來從 SQL Server 載入資料的資料分割選項。
允許的值為:None (預設值)、PhysicalPartitionsOfTableDynamicRange
啟用分割選項後 (亦即不是 None),從 SQL Server 同時載入資料的平行程度,由複製活動的 parallelCopies 設定所控制。
partitionSettings 指定資料分割的設定群組。
當分割選項不是 None 時套用。
partitionSettings 底下:
partitionColumnName 整數類型或日期/日期時間類型 (intsmallintbigintdatesmalldatetimedatetimedatetime2datetimeoffset) 指定來源資料行的名稱,供平行複製的範圍分割使用。 如果未指定,則會自動偵測資料表的索引或主索引鍵作為分割資料行。
當分割選項是 DynamicRange 時套用。 如果您使用查詢來取出來源資料,請在 WHERE 子句中加上 ?AdfDynamicRangePartitionCondition 。 如需範例,請參閱從 SQL 資料庫平行複製一節。
partitionUpperBound 分割區範圍分割的分割區資料行最大值。 這個值用於決定分割區的跨距,而不是用於篩選資料表中的資料列。 資料表或查詢結果中的所有資料列都會進行分割和複製。 如果未指定,複製活動會自動偵測該值。
當分割選項是 DynamicRange 時套用。 如需範例,請參閱從 SQL 資料庫平行複製一節。
partitionLowerBound 分割區範圍分割的分割區資料行最小值。 這個值用於決定分割區的跨距,而不是用於篩選資料表中的資料列。 資料表或查詢結果中的所有資料列都會進行分割和複製。 如果未指定,複製活動會自動偵測該值。
當分割選項是 DynamicRange 時套用。 如需範例,請參閱從 SQL 資料庫平行複製一節。
No

請注意下列幾點:

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

範例:使用 SQL 查詢

"activities":[
    {
        "name": "CopyFromSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

範例:使用預存程序

"activities":[
    {
        "name": "CopyFromSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

預存程序定義

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
    select *
    from dbo.UnitTestSrcTable
    where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

SQL Server 作為接收器

提示

深入了解支援的寫入行為、設定,以及將資料載入 SQL Server 的最佳做法所介紹的最佳做法。

若要將資料複製到 SQL Server,請將複製活動中的接收器類型設定為 SqlSink。 複製活動的 [接收] 區段支援下列屬性:

屬性 描述 必要
type 複製活動接收的 type 屬性必須設定為 SqlSink Yes
preCopyScript 此屬性會針對複製活動指定一個 SQL 查詢,在將資料寫入到 SQL Server 之前執行。 每一複製回合只會叫用此查詢一次。 您可以使用此屬性來清除預先載入的資料。
tableOption 指定是否要根據來源結構描述,自動建立接收資料表 (如果不存在)。 當接收指定預存程序時,不支援自動建立資料表。 允許的值包為:none (預設) 或 autoCreate
sqlWriterStoredProcedureName 定義如何將來源資料套用到目標資料表的預存程序名稱。
此預存程序將會依批次叫用。 針對只執行一次且與來源資料無關的作業 (例如刪除或截斷),請使用 preCopyScript 屬性。
請參閱叫用 SQL 接收器中的預存程序的範例。
No
storedProcedureTableTypeParameterName 預存程序中指定資料表類型的參數名稱。 No
sqlWriterTableType 在預存程序中使用的資料表類型名稱。 複製活動可讓正在移動的資料可用於此資料表類型的暫存資料表。 然後,預存程序程式碼可以合併正在複製的資料與現有的資料。
storedProcedureParameters 預存程序的參數。
允許的值為:名稱和值組。 參數的名稱和大小寫必須符合預存程序參數的名稱和大小寫。
writeBatchSize 對於每個批次要插入 SQL 資料表中的資料列數。
允許的值為整數的資料列數目。 根據預設,服務會依據資料列大小動態決定適當的批次大小。
No
writeBatchTimeout 此屬性會指定在逾時前等待批次插入作業完成的時間。
允許的值為時間範圍。 例如 “00:30:00” 為 30 分鐘。 如果未指定任何值,逾時預設為 “02:00:00”。
 maxConcurrentConnections 活動執行期間,建立與資料存放區的並行連線上限。 僅在想要限制並行連線時,才需要指定值。  否
WriteBehavior 指定複製活動的寫入行為,將資料載入 SQL Server 資料庫。
允許的值為 InsertUpsert。 根據預設,服務會使用 Insert 載入資料。
upsertSettings 指定寫入行為的設定群組。
當 WriteBehavior 選項為 Upert 時套用。
upsertSettings 底下:
useTempDB 指定是否要使用全域暫存資料表或實體資料表作為 upsert 的過渡資料表。
根據預設,服務會使用全域暫存資料表作為過度資料表。 值為 true
No
interimSchemaName 如果使用實體資料表,請指定建立過渡資料表的過渡結構描述。 注意:使用者必須具有建立和刪除資料表的權限。 根據預設,過渡資料表會與接收資料表共用相同的結構描述。
當 useTempDB 選項為 False 套用。
No
金鑰 指定唯一資料列識別的資料行名稱。 您可以使用單一索引鍵或一系列索引鍵。 如果未指定,則會使用主索引鍵。 No

範例 1:附加資料

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

範例 2:在複製期間叫用預存程序

若要了解更多詳細資料,請參閱叫用 SQL 接收中的預存程序

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

範例 3:Upsert 資料

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

從 SQL 資料庫平行複製

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

Screenshot of partition options

當您啟用分割複本時,複製活動會平行查詢 SQL Server 來源,以依分割區來載入資料。 平行程度由複製活動的 parallelCopies 設定所控制。 例如,如果您將 parallelCopies 設定為 4,服務會根據您指定的資料分割選項和設定,同時產生並執行四個查詢,而每個查詢會從 SQL Server 資料庫取取出一部分資料。

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

狀況 建議的設定
使用實體分割區從大型資料表完整載入。 分割選項:資料表的實體分割區。

在執行期間,服務會自動偵測實體分割區,並依分割區複製資料。

若要檢查您的資料表是否有實體分割區,您可以參考此查詢
從大型資料表完整載入,不含實體分割區,同時在資料分割時包含整數或日期時間資料行。 分割選項:動態範圍分割。
分割資料行 (選用):指定用來分割資料的資料行。 如果未指定,則會使用主索引鍵資料行。
分割區上限分割區下限 (選用):指定是否要決定分割區跨距。 這不適用於篩選資料表中的資料列,資料表中的所有資料列都會分割並複製。 如果未指定,複製活動會自動偵測值,而且可能需要很長的時間,視 MIN 和 MAX 值而定。 建議提供上限和下限。

例如,如果您的分割區資料行「識別碼」具有範圍 1 到 100 之間的值,而您將下限設定為 20、上限設定為 80,且平行複製為 4,則服務會分別依 4 個分割區擷取資料 - 範圍中的識別碼分別為 <=20、[21, 50]、[51, 80] 和 >=81。
使用自訂查詢載入大量資料,不使用實體分割區,同時包含整數或日期/日期時間資料行用於資料分割。 分割選項:動態範圍分割。
查詢SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
分割資料行:指定用來分割資料的資料行。
分割區上限分割區下限 (選用):指定是否要決定分割區跨距。 這不適用於篩選資料表中的資料列,查詢結果中的所有資料列都會分割並複製。 如果未指定,複製活動會自動偵測該值。

在執行期間,服務會將 ?AdfRangePartitionColumnName 替換成每個分割區的實際資料行名稱和值範圍,並傳送至 SQL Server。
例如,如果您的分割區資料行「識別碼」具有範圍 1 到 100 之間的值,而您將下限設定為 20、上限設定為 80,且平行複製為 4,則服務會分別依 4 個分割區擷取資料 - 範圍中的識別碼分別為 <=20、[21, 50]、[51, 80] 和 >=81。

以下是不同案例的更多範例查詢:
1. 查詢整個資料表:
SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition
2. 來自具有資料行選取範圍和其他 where 子句篩選的資料表查詢:
SELECT <column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
3. 使用子查詢的查詢:
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. 在子查詢中使用分割區的查詢:
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T

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

  1. 選擇獨特的資料行作為分割資料行 (例如主索引鍵或唯一索引鍵) 以避免資料扭曲。
  2. 如果資料表有內建分割區,請使用分割選項「資料表的實體分割區」,以獲得更佳的效能。
  3. 如果您使用 Azure Integration Runtime 來複製資料,您可以設定較大的「資料整合單位 (DIU)」(>4) 來利用更多運算資源。 檢查該處適用的案例。
  4. 複製平行處理原則的程度」會控制分割區數目,將此數目設定過大有時會損害效能,建議將此數目設定為 (DIU 或自我裝載 IR 節點數目) * (2 到 4)。

範例:使用實體分割區從大型資料表完整載入

"source": {
    "type": "SqlSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

範例:使用動態範圍分割進行查詢

"source": {
    "type": "SqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

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

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 query result

將資料載入 SQL Server 的最佳做法

將資料複製到 SQL Server 時,您可能需要不同的寫入行為:

  • 附加:我的來源資料只有新的記錄。
  • 更新插入:我的來源資料同時有插入和更新。
  • 覆寫:我想要每次都重新載入整個維度資料表。
  • 使用自訂邏輯寫入:在最終插入目的地資料表之前,我還需要額外的處理。

如需設定方式和最佳做法,請參閱個別章節。

附加資料

附加資料是這個 SQL Server 接收連接器的預設行為。 服務會執行大量插入,以有效率地寫入資料表。 您可以在複製活動中據以設定來源和接收。

更新插入資料

複製活動現在支援將資料原生載入資料庫暫存資料表,然後在索引鍵存在時更新接收資料表中的資料,若無則插入新的資料。 若要深入了解複製活動中的更新插入設定,請參閱 SQL Server 作為接收器

覆寫整個資料表

您可以在複製活動接收器中設定 preCopyScript 屬性。 在此情況下,針對執行的每個複製活動,服務會先執行指令碼。 然後服務會執行複本以插入資料。 例如,若要以最新的資料覆寫整個資料表,可以指定先刪除所有記錄,再從來源大量載入新資料的指令碼。

使用自訂邏輯寫入資料

使用自訂邏輯寫入資料的步驟類似於更新插入資料一節中所述的步驟。 當您需要在最終插入來源資料至目的地資料表之前套用額外的處理時,您可以載入暫存表格,然後叫用預存程序活動,或在複製活動接收器中叫用預存程序來套用資料。

從 SQL 接收叫用預存程序

當您將資料複製到 SQL Server 資料庫時,也可以在來源資料表的每個批次上設定及叫用具有額外參數的使用者指定預存程序。 預存程序功能使用資料表值參數。 請注意,服務會自動將預存程序包裝在自己的交易中,因此在預存程式內建立的任何交易都會變成巢狀交易,而且可能會對例外狀況處理造成影響。

當內建的複製機制無法滿足需求時,您可以使用預存程序。 例如,當您想要在最終插入來源資料至目的地資料表之前,套用額外的處理。 額外處理的一些範例包括:合併資料行、查閱其他的值,以及插入多個資料表中。

下列範例示範如何使用預存程序,對 SQL Server 資料庫中的資料表執行更新插入。 假設輸入資料和接收 Marketing 資料表各有三個資料行:ProfileIDStateCategory。 根據 ProfileID 資料行執行更新插入,然後僅套用至名為 "ProductA" 的特定類別。

  1. 在資料庫中,使用與 sqlWriterTableType 相同的名稱來定義資料表類型。 資料表類型的結構描述會與輸入資料所傳回的結構描述相同。

    CREATE TYPE [dbo].[MarketingType] AS TABLE(
        [ProfileID] [varchar](256) NOT NULL,
        [State] [varchar](256) NOT NULL,
        [Category] [varchar](256) NOT NULL
    )
    
  2. 在資料庫中,使用與 SqlWriterStoredProcedureName 相同的名稱來定義預存程序。 它會處理來自指定來源的輸入資料,並合併至輸出資料表。 預存程序中資料表類型的參數名稱會與資料集中定義的 tableName 相同。

    CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
    AS
    BEGIN
    MERGE [dbo].[Marketing] AS target
    USING @Marketing AS source
    ON (target.ProfileID = source.ProfileID and target.Category = @category)
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT MATCHED THEN
        INSERT (ProfileID, State, Category)
        VALUES (source.ProfileID, source.State, source.Category);
    END
    
  3. 依下列方式,在複製活動中定義 SQL 接收器區段:

    "sink": {
        "type": "SqlSink",
        "sqlWriterStoredProcedureName": "spOverwriteMarketing",
        "storedProcedureTableTypeParameterName": "Marketing",
        "sqlWriterTableType": "MarketingType",
        "storedProcedureParameters": {
            "category": {
                "value": "ProductA"
            }
        }
    }
    

對應資料流程屬性

在對應資料流程中轉換資料時,您可以從 SQL Server 資料庫分析讀取和寫入資料表。 如需詳細資訊,請參閱對應資料流程中的來源轉換接收轉換

注意

若要存取內部部署 SQL Server,您必須使用 Azure Data Factory 或是採用私人端點的 Synapse 工作區受控虛擬網路。 請參閱此教學課程中的詳細步驟。

來源轉換

下表列出 SQL Server 來源所支援的屬性。 您可以在 [來源選項] 索引標籤中編輯這些屬性。

名稱 描述 必要 允許的值 資料流程指令碼屬性
資料表 如果您選取 [資料表] 作為輸入,資料流程會從資料集中指定的資料表擷取所有資料。 No - -
查詢 如果您選取 [查詢] 作為輸入,請指定要從來源擷取資料的 SQL 查詢,這會覆寫您在資料集中指定的任何資料表。 使用查詢也是減少測試或查閱資料行的絕佳方式。

不支援 Order By 子句,但您可以設定完整的 SELECT FROM 陳述式。 您也可使用使用者定義的資料表函數。 select * from udfGetData() 是 SQL 中的 UDF,您可以在資料流程中用於傳回資料表。
查詢範例:Select * from MyTable where customerId > 1000 and customerId < 2000
String 查詢
批次大小 指定批次大小,以將大量資料分成多次讀取。 No 整數 batchSize
隔離等級 選擇下列其中一個隔離等級:
- 讀取認可
- 讀取未認可 (預設值)
- 可重複讀取
- 可序列化
- 無 (忽略隔離等級)
No READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZABLE
NONE
isolationLevel

提示

對應資料流程 [查詢] 模式不支援 SQL 的通用資料表運算式 (CTE),因為使用此模式的必要條件是查詢可用於 SQL 查詢 FROM 子句,但 CTE 不適用。 若要使用 CTE,您必須使用下列查詢來建立預存程序:

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

然後在對應資料流程的來源轉換中使用 [預存程序] 模式,並依照範例 with CTE as (select 'test' as a) select * from CTE 設定 @query。 接著您便可以正常使用 CTE。

SQL Server 來源指令碼範例

當您使用 SQL Server 作為來源類型時,相關聯的資料流程指令碼為:

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLSource

接收轉換

下表列出 SQL Server 接收器所支援的屬性。 您可以在 [接收器選項] 索引標籤中編輯這些屬性。

名稱 描述 必要 允許的值 資料流程指令碼屬性
Update 方法 指定您的資料庫目的地所允許的作業。 預設僅允許插入。
若要更新、更新插入或刪除資料列,必須使用變更資料列轉換來標記這些動作的資料列。
Yes truefalse deletable
insertable
updateable
upsertable
索引鍵資料行 對於更新、更新插入和刪除,必須設定索引鍵資料行以決定要變更哪一個資料列。
您挑選作為索引鍵的資料行名稱,將會在後續更新、更新插入、刪除時使用。 因此,您必須挑選存在於接收器對應中的資料行。
No Array 金鑰
跳過寫入索引鍵資料行 如果您不想將值寫入索引鍵資料行,請選取 [跳過寫入索引鍵資料行]。 No truefalse skipKeyWrites
資料表動作 決定在寫入之前,是否要重新建立或移除目的地資料表中的所有資料列。
- :資料表不會執行任何動作。
- 重新建立:資料表會遭到捨棄並重新建立。 如果要動態建立新的資料表,則為必要。
- 截斷:系統將會移除目標資料表中的所有資料列。
No truefalse recreate
truncate
批次大小 指定要在每個批次中寫入的資料列數目。 較大的批次大小會改善壓縮和記憶體優化,但會導致在快取資料時發生記憶體例外狀況的風險。 No 整數 batchSize
前置和後置 SQL 指令碼 指定將在寫入至您的接收器資料庫之前 (前置處理) 和之後 (後置處理) 將執行的多行 SQL 指令碼。 String preSQLs
postSQLs

提示

  1. 建議將含有多個命令的單一批次指令碼分成多個批次。
  2. 只有傳回簡單更新計數的資料定義語言 (DDL) 和資料操作語言 (DML) 陳述式可以當作批次的一部份來執行。 若要深入了解,請參閱執行批次作業

SQL Server 接收器指令碼範例

當您使用 SQL Server 作為接收器類型時,相關聯的資料流程指令碼為:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLSink

SQL Server 的資料類型對應

當您在 SQL Server 複製/貼上資料時,將會使用下列從 SQL Server 資料類型對應到 Azure Data Factory 過渡期資料類型的對應。 實作 Data Factory 的 Synapse 管線會使用相同的對應。 若要了解複製活動如何將來源結構描述和資料類型對應至接收,請參閱結構描述和資料類型對應

SQL Server 資料類型 Data Factory 過渡期資料類型
BIGINT Int64
BINARY Byte[]
bit Boolean
char String, Char[]
date Datetime
Datetime Datetime
datetime2 Datetime
Datetimeoffset DateTimeOffset
Decimal Decimal
FILESTREAM attribute (varbinary(max)) Byte[]
Float Double
image Byte[]
int Int32
money Decimal
NCHAR String, Char[]
ntext String, Char[]
NUMERIC Decimal
NVARCHAR String, Char[]
real Single
rowversion Byte[]
smalldatetime Datetime
SMALLINT Int16
SMALLMONEY Decimal
sql_variant Object
text String, Char[]
time TimeSpan
timestamp Byte[]
TINYINT Int16
UNIQUEIDENTIFIER Guid
varbinary Byte[]
varchar String, Char[]
Xml String

注意

針對對應至 Decimal 過渡期類型的資料類型,複製活動目前支援最多 28 個有效位數。 如果您的資料需要大於 28 個有效位數,請考慮轉換成 SQL 查詢中的字串。

使用 Azure Data Factory 從 SQL Server 複製資料時,位元資料類型會對應至布林值過渡資料類型。 如果您有需要保留為位元資料類型的資料,請使用具有 T-SQL CAST 或 CONVERT 的查詢。

查閱活動屬性

若要了解關於屬性的詳細資料,請參閱查閱活動

GetMetadata 活動屬性

若要了解關於屬性的詳細資料,請參閱 GetMetadata 活動

使用 Always Encrypted

當您使用 Always Encrypted 在 SQL Server 複製/貼上資料時,請遵循下列步驟:

  1. 資料行主要金鑰 (CMK) 儲存在 Azure Key Vault 中。 深入了解如何使用 Azure Key Vault 設定 Always Encrypted

  2. 請務必將金鑰保存庫的存取權授與儲存資料行主要金鑰 (CMK) 的位置。 針對必要權限,請參閱這篇文章

  3. 建立連結服務以連線到您的 SQL 資料庫,並使用受控身分識別或服務主體啟用 'Always Encrypted' 函式。

注意

SQL Server Always Encrypted 支援下列案例:

  1. 來源或接收資料存放區都使用受控識別或服務主體作為金鑰提供者驗證類型。
  2. 來源和接收資料存放區都會使用受控身分識別作為金鑰提供者驗證類型。
  3. 來源和接收資料存放區都使用與金鑰提供者驗證類型相同的服務主體。

注意

目前,只有對應資料流中的來源轉換才支援SQL Server Always Encrypted

連線問題疑難排解

  1. 將 SQL Server 執行個體設定成接受遠端連線。 啟動 [SQL Server Management Studio]、用滑鼠右鍵按一下 [伺服器],然後選取 [屬性]。 從清單中選取 [連線],然後選取 [允許此伺服器的遠端連接] 核取方塊。

    Enable remote connections

    如需詳細步驟,請參閱設定 remote access 伺服器組態選項

  2. 啟動 SQL Server 組態管理員。 展開您想要之執行個體的 [SQL Server 網路組態],然後選取 [MSSQLSERVER 的通訊協定]。 這些通訊協定會出現在右窗格中。 用滑鼠右鍵按一下 [TCP/IP],然後選取 [啟用] 來啟用 TCP/IP。

    Enable TCP/IP

    如需啟用 TCP/IP 通訊協定的詳細資料及替代方式,請參閱啟用或停用伺服器網路通訊協定

  3. 在相同的視窗中,按兩下 [TCP/IP] 來啟動 [TCP/IP 屬性] 視窗。

  4. 切換至 [IP 位址] 索引標籤。向下捲動以查看 [IPAll] 區段。 記下 [TCP 埠]。 預設值是 1433

  5. 在電腦上建立 Windows 防火牆規則 ,來允許透過此連接埠的連入流量。

  6. 確認連線:若要使用完整名稱來連線到 SQL Server,請使用來自不同機器的 SQL Server Management Studio。 例如 "<machine>.<domain>.corp.<company>.com,1433"

下一步

如需複製活動支援作為來源與接收器的資料存放區清單,請參閱支援的資料存放區