使用 Azure Data Factory 或 Azure Synapse Analytics，從 Amazon RDS for SQL Server 中複製資料

發行項
03/20/2024

本文概述如何使用 Azure Data Factory 和 Azure Synapse 管線中的複製活動，以從 Amazon RDS for SQL Server 資料庫中複製資料。若要深入了解，請閱讀 Azure Data Factory 或 Azure Synapse Analytics 的介紹文章。

支援的功能

此 Amazon RDS for SQL Server 連接器支援下列功能：

支援的功能	IR
複製活動 (來源/-)	① ②
查閱活動	① ②
GetMetadata 活動	① ②
預存程序活動	① ②

① Azure 整合執行階段 ② 自我裝載整合執行階段

如需複製活動所支援作為來源或接收器的資料存放區清單，請參閱支援的資料存放區表格。

具體而言，這個 Amazon RDS for SQL Server 連接器支援：

SQL Server 2005 版和更新版本。
使用 SQL 或 Windows 驗證來複製資料。
作為來源時，使用 SQL 查詢或預存程序來擷取資料。您也可以選擇從 Amazon RDS for SQL Server 來源平行複製，如需詳細資料，請參閱從 SQL 資料庫平行複製一節。

不支援 SQL Server Express LocalDB。

必要條件

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

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

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

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

開始使用

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

使用 UI 建立 Amazon RDS for SQL Server 連結服務

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

前往 Azure Data Factory 或 Synapse 工作區的 [管理] 索引標籤，選取 [連結服務]，然後按一下 [新增]：
- Azure Data Factory
- Azure Synapse
搜尋 [Amazon RDS for SQL Server]，然後選取 [Amazon RDS for SQL Server 連接器]。
設定服務詳細資料，測試連線，然後建立新的連結服務。

連接器設定詳細資料

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

連結服務屬性

以下是針對 Amazon RDS for SQL Server 連結服務所支援的屬性：

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

注意

資料流程不支援 Amazon RDS for SQL Server 一律加密。

提示

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

範例 1：使用 SQL 驗證

{
    "name": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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": "AmazonSqlLinkedService",
    "properties": {
        "type": "AmazonRdsForSqlServer",
        "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"
        }
    }
}

資料集屬性

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

若要從 Amazon RDS for SQL Server 資料庫複製資料，支援下列屬性：

屬性	描述	必要
type	資料集的類型屬性必須設定為 AmazonRdsForSqlServerTable。	Yes
schema	結構描述的名稱。	No
table	資料表/檢視的名稱。	No
tableName	具有結構描述的資料表/檢視名稱。支援此屬性是基於回溯相容性。對於新的工作負載，請使用 `schema` 和 `table`。	No

範例

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

複製活動屬性

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

Amazon RDS for SQL Server 作為來源

提示

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

若要從 Amazon RDS for SQL Server 複製資料，請將複製活動中的來源類型設定為 AmazonRdsForSqlServerSource。複製活動的 [來源] 區段支援下列屬性：

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

請注意下列幾點：

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

範例：使用 SQL 查詢

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

範例：使用預存程序

"activities":[
    {
        "name": "CopyFromAmazonRdsForSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Amazon RDS for SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AmazonRdsForSqlServerSource",
                "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 資料庫平行複製

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

Screenshot of partition options

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

建議您啟用平行複製與資料分割，特別是從 Amazon RDS for 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` 替換成每個分割區的實際資料行名稱和值範圍，並傳送至 Amazon RDS for 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`

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

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

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

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

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

"source": {
    "type": "AmazonRdsForSqlServerSource",
    "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

查閱活動屬性

若要了解屬性的詳細資料，請參閱查閱活動。

GetMetadata 活動屬性

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

使用 Always Encrypted

當您使用一律加密在 Amazon RDS for SQL Server 複製/貼上資料時，請遵循下列步驟：

將資料行主要金鑰 (CMK) 儲存在 Azure Key Vault 中。深入了解如何使用 Azure Key Vault 設定 Always Encrypted
請務必將金鑰保存庫的存取權授與儲存資料行主要金鑰 (CMK) 的位置。針對必要權限，請參閱這篇文章。
建立連結服務以連線到您的 SQL 資料庫，並使用受控身分識別或服務主體啟用 'Always Encrypted' 函式。

連線問題疑難排解

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

如需詳細步驟，請參閱設定 remote access 伺服器組態選項。
啟動 Amazon RDS for SQL Server 設定管理員. 展開所要執行個體的 [Amazon RDS for SQL Server 網路設定]，然後選取 [MSSQLSERVER 的通訊協定]。這些通訊協定會出現在右窗格中。用滑鼠右鍵按一下 [TCP/IP]，然後選取 [啟用] 來啟用 TCP/IP。

如需啟用 TCP/IP 通訊協定的詳細資料及替代方式，請參閱啟用或停用伺服器網路通訊協定。
在相同的視窗中，按兩下 [TCP/IP] 來啟動 [TCP/IP 屬性] 視窗。
切換到 [IP 位址] 索引標籤。向下捲動以查看 [IPAll] 區段。記下 [TCP 埠]。預設值是 1433。
在電腦上建立 Windows 防火牆規則 ，來允許透過此連接埠的連入流量。
確認連線：若要使用完整名稱來連線至 Amazon RDS for SQL Server，請使用來自不同機器的 Amazon RDS for SQL Server Management Studio。例如 "<machine>.<domain>.corp.<company>.com,1433"。

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