Kopieren und Transformieren von Daten in und aus SQL Server mithilfe von Azure Data Factory oder Azure Synapse Analytics

Gilt für: Azure Data Factory Azure Synapse Analytics

In diesem Artikel wird beschrieben, wie Sie die Kopieraktivität in Azure Data Factory und Azure Synapse Pipelines verwenden, um Daten aus und in SQL Server Datenbank zu kopieren und Datenfluss zum Transformieren von Daten in SQL Server Datenbank zu verwenden. Weitere Informationen finden Sie im Einführungsartikel zu Azure Data Factory oder Azure Synapse Analytics.

Unterstützte Funktionen

Dieser SQL Server Connector wird für die folgenden Funktionen unterstützt:

(1) Azure Integrationslaufzeit (2) Selbst gehostete Integrationslaufzeit

Eine Liste der Datenspeicher, die als Quellen oder Senken für die Kopieraktivität unterstützt werden, finden Sie in der Tabelle Unterstützte Datenspeicher.

Insbesondere unterstützt dieser SQL Server Connector Folgendes:

• SQL Server Version 2005 und höher.
• Kopieren von Daten mithilfe von SQL oder Windows-Authentifizierung.
• Als Quelle das Abrufen von Daten mithilfe einer SQL-Abfrage oder gespeicherten Prozedur Sie können auch eine parallele Kopie aus SQL Server Quelle auswählen. Weitere Informationen finden Sie im Abschnitt Parallel copy from SQL database.
• Als Senke das automatische Erstellen einer Zieltabelle (sofern nicht vorhanden) basierend auf dem Quellschema. Außerdem das Anfügen von Daten an eine Tabelle oder das Aufrufen einer gespeicherten Prozedur mit benutzerdefinierter Logik während des Kopiervorgangs.

SQL Server Express LocalDB wird nicht unterstützt.

Voraussetzungen

Wenn sich Ihr Datenspeicher in einem lokalen Netzwerk, einem Azure virtuellen Netzwerk oder amazon Virtual Private Cloud befindet, müssen Sie eine self-gehostete Integrationslaufzeit konfigurieren, um eine Verbindung damit herzustellen.

Wenn Ihr Datenspeicher ein verwalteter Clouddatendienst ist, können Sie die Azure Integration Runtime verwenden. Wenn der Zugriff auf IPs beschränkt ist, die in den Firewallregeln genehmigt wurden, können Sie der Zulassungsliste Azure Integration Runtime-IPs hinzufügen.

Sie können auch das Feature managed virtual network integration runtime in Azure Data Factory verwenden, um auf das lokale Netzwerk zuzugreifen, ohne eine selbst gehostete Integrationslaufzeit zu installieren und zu konfigurieren.

Weitere Informationen zu den von Data Factory unterstützten Netzwerksicherheitsmechanismen und -optionen finden Sie unter Datenzugriffsstrategien.

Erste Schritte

Zum Ausführen der Kopieraktivität mit einer Pipeline können Sie eines der folgenden Tools oder SDKs verwenden:

• Datenkopier-Werkzeug
• Azure Portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST-API
• Azure Resource Manager Vorlage

Erstellen eines SQL Server verknüpften Diensts mithilfe der Benutzeroberfläche

Führen Sie die folgenden Schritte aus, um einen SQL Server verknüpften Dienst in der benutzeroberfläche des Azure Portals zu erstellen.

1. Navigieren Sie in Ihrem Azure Data Factory- oder Synapse-Arbeitsbereich zur Registerkarte "Verwalten", und wählen Sie "Verknüpfte Dienste" aus, und klicken Sie dann auf "Neu":

   • Azure Data Factory
   • Azure Synapse

   

2. Suchen Sie nach SQL, und wählen Sie den SQL Server Connector aus.

   

3. Konfigurieren Sie die Dienstdetails, testen Sie die Verbindung, und erstellen Sie den neuen verknüpften Dienst.

   

Details zur Connectorkonfiguration

Die folgenden Abschnitte enthalten Details zu Eigenschaften, die zum Definieren von Data Factory- und Synapse-Pipelineentitäten verwendet werden, die für den SQL Server-Datenbankconnector spezifisch sind.

Eigenschaften des verknüpften Diensts

Die version SQL Server Recommended unterstützt TLS 1.3. Lesen Sie diesen Abschnitt, um Ihren SQL Server-verknüpften Dienst zu aktualisieren, wenn Sie die Legacy-Version verwenden. Einzelheiten zur Eigenschaft finden Sie in den entsprechenden Abschnitten.

• Empfohlene Version
• Legacy-Version

Empfohlene Version

Diese generischen Eigenschaften werden für einen mit SQL Server verknüpften Dienst unterstützt, wenn Sie die empfohlene Version anwenden:

Weitere Verbindungseigenschaften finden Sie in der folgenden Tabelle:

SQL-Authentifizierung

Um die SQL-Authentifizierung zu verwenden, geben Sie zusätzlich zu den im vorherigen Abschnitt beschriebenen allgemeinen Eigenschaften die folgenden Eigenschaften an:

Beispiel 1: Verwenden der SQL-Authentifizierung

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: Verwenden der SQL-Authentifizierung mit einem Kennwort in Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Beispiel: Verwenden von Always Encrypted

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "alwaysEncryptedSettings": {
            "alwaysEncryptedAkvAuthType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Windows-Authentifizierung

Um Windows-Authentifizierung zu verwenden, geben Sie zusätzlich zu den generischen Eigenschaften, die im vorherigen Abschnitt beschrieben werden, die folgenden Eigenschaften an:

Example: Verwenden Sie Windows-Authentifizierung

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: Verwenden sie Windows-Authentifizierung mit einem Kennwort in Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "annotations": [],
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Authentifizierung mit einer benutzerseitig zugewiesenen verwalteten Identität

Ein Data Factory- oder Synapse-Arbeitsbereich kann einer benutzer zugewiesenen verwalteten Identitäten zugeordnet werden, die den Dienst bei der Authentifizierung für andere Ressourcen in Azure darstellt. Sie können diese verwaltete Identität für SQL Server für Azure VMsauthentifizierung verwenden. Die angegebenen Factory oder Synapse Arbeitsbereiche können mittels dieser Identität auf Daten zugreifen und Daten aus der oder in die Datenbank kopieren.

Um die vom Benutzer zugewiesene verwaltete Identitätsauthentifizierung zu verwenden, geben Sie zusätzlich zu den im vorherigen Abschnitt beschriebenen allgemeinen Eigenschaften die folgenden Eigenschaften an:

Sie müssen auch die folgenden Schritte ausführen:

1. Erteilen von Berechtigungen für Ihre benutzerseitig zugewiesene verwaltete Identität

2. Aktivieren Sie die Microsoft Entra-Authentifizierung für Ihren SQL Server auf Azure-VMs.

3. Erstellen Sie Benutzer der eigenständigen Datenbank für die benutzerseitig zugewiesene verwaltete Identität. Stellen Sie eine Verbindung mit der Datenbank her, aus der oder in die Sie Daten kopieren möchten, indem Sie Tools wie SQL Server Management Studio verwenden, mit einer Microsoft Entra-Identität, die mindestens über die ALTER ANY USER-Berechtigung verfügt. Führen Sie folgenden T-SQL-Code aus:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

4. Erstellen Sie mindestens eine benutzerseitig zugewiesene verwaltete Identität, und gewähren Sie dieser Identität die erforderlichen Berechtigungen, wie Sie es normalerweise für SQL- und andere Benutzer tun. Führen Sie den folgenden Code aus. Weitere Optionen finden Sie in diesem Dokument.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

5. Weisen Sie Ihrer Data Factory eine oder mehrere benutzerseitig zugewiesene verwaltete Identitäten zu, und erstellen Sie Anmeldeinformationen für jede benutzerseitig zugewiesene verwaltete Identität.

6. Konfigurieren sie einen SQL Server verknüpften Dienst.

Beispiel

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Legacy-Version

Diese generischen Eigenschaften werden für einen mit SQL Server verknüpften Dienst unterstützt, wenn Sie die Legacy-Version anwenden:

Dieser SQL Server-Connector unterstützt die folgenden Authentifizierungstypen. Weitere Informationen finden Sie in den entsprechenden Abschnitten.

• SQL-Authentifizierung für die Legacy-Version
• Windows-Authentifizierung für die Legacyversion

SQL-Authentifizierung für die Legacy-Version

Um die SQL-Authentifizierung zu verwenden, geben Sie zusätzlich zu den im vorherigen Abschnitt beschriebenen allgemeinen Eigenschaften die folgenden Eigenschaften an:

Windows-Authentifizierung für die Legacyversion

Um Windows-Authentifizierung zu verwenden, geben Sie zusätzlich zu den generischen Eigenschaften, die im vorherigen Abschnitt beschrieben werden, die folgenden Eigenschaften an:

Dataset-Eigenschaften

Eine vollständige Liste mit den Abschnitten und Eigenschaften, die zum Definieren von Datasets zur Verfügung stehen, finden Sie im Artikel zu Datasets. Dieser Abschnitt enthält eine Liste der Eigenschaften, die vom SQL Server-Dataset unterstützt werden.

Zum Kopieren von Daten aus und in eine SQL Server-Datenbank werden die folgenden Eigenschaften unterstützt:

Beispiel

{
    "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>"
        }
    }
}

Eigenschaften der Kopieraktivität

Eine vollständige Liste mit den Abschnitten und Eigenschaften zum Definieren von Aktivitäten finden Sie im Artikel Pipelines. Dieser Abschnitt enthält eine Liste der Eigenschaften, die von der SQL Server-Quelle und -Senke unterstützt werden.

SQL Server als Quelle

Um Daten aus SQL Server zu kopieren, legen Sie den Quelltyp in der Kopieraktivität auf SqlSource fest. Die folgenden Eigenschaften werden im Abschnitt „source“ der Kopieraktivität unterstützt:

Beachten Sie folgende Punkte:

• Wenn sqlReaderQuery für SqlSource angegeben ist, führt die Kopieraktivität diese Abfrage für die SQL Server Quelle aus, um die Daten abzurufen. Sie können auch eine gespeicherte Prozedur angeben, indem Sie sqlReaderStoredProcedureName und storedProcedureParameters angeben, sofern die gespeicherten Prozeduren Parameter verwenden.
• Wenn Sie zum Abrufen von Daten eine gespeicherte Prozedur in der Quelle verwenden und die gespeicherte Prozedur beim Übergeben eines anderen Parameterwerts ein anderes Schema zurückgibt, kommt es möglicherweise beim Importieren eines Schemas über die Benutzeroberfläche oder beim Kopieren von Daten in eine SQL-Datenbank zu einem Fehler oder einem unerwarteten Ergebnis.

Beispiel: Verwenden einer SQL-Abfrage

"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>"
            }
        }
    }
]

Beispiel: Verwenden einer gespeicherten Prozedur

"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>"
            }
        }
    }
]

Definition der gespeicherten Prozedur

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 als Spüle

Wenn Sie Daten in SQL Server kopieren möchten, legen Sie den Sinktyp in der Kopieraktivität auf SqlSink fest. Die folgenden Eigenschaften werden im Abschnitt „sink“ der Kopieraktivität unterstützt:

Beispiel 1: Anfügen von Daten

"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
            }
        }
    }
]

Beispiel 2: Aufrufen einer gespeicherten Prozedur während des Kopiervorgangs

Weitere Informationen finden Sie unter Aufrufen einer gespeicherten Prozedur aus einer SQL-Senke.

"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" }
                }
            }
        }
    }
]

Beispiel 3: Upsert-Daten

"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>"
                    ]
                },
            }
        }
    }
]

Paralleles Kopieren aus SQL-Datenbank

Der SQL Server-Connector in der Kopieraktivität bietet eine integrierte Datenpartitionierung, um Daten parallel zu kopieren. Die Datenpartitionierungsoptionen befinden sich auf der Registerkarte Quelle der Kopieraktivität.

Wenn Sie partitionierte Kopie aktivieren, führt kopieraktivität parallele Abfragen für Ihre SQL Server Quelle aus, um Daten nach Partitionen zu laden. Der Parallelitätsgrad wird über die Einstellung parallelCopies der Kopieraktivität gesteuert. Wenn Sie z. B. parallelCopies auf vier festlegen, generiert der Dienst gleichzeitig vier Abfragen basierend auf Der angegebenen Partitionsoption und -einstellungen, und jede Abfrage ruft einen Teil der Daten aus Ihrem SQL Server ab.

Sie werden empfohlen, parallele Kopie mit Datenpartitionierung zu aktivieren, insbesondere wenn Sie eine große Datenmenge aus Ihrem SQL Server laden. Im Anschluss finden Sie empfohlene Konfigurationen für verschiedene Szenarien. Beim Kopieren von Daten in einen dateibasierten Datenspeicher wird empfohlen, mehrere Dateien in einen Ordner zu schreiben (nur den Ordnernamen anzugeben). In diesem Fall ist die Leistung besser als beim Schreiben in eine einzelne Datei.

Bewährte Methoden zum Laden von Daten mit Partitionierungsoption:

1. Wählen Sie eine aussagekräftige Spalte als Partitionsspalte (wie Primärschlüssel oder eindeutiger Schlüssel), um Datenabweichungen zu vermeiden.
2. Wenn die Tabelle eine integrierte Partition aufweist, verwenden Sie die Partitionsoption „Physikalische Partitionen der Tabelle“, um eine bessere Leistung zu erzielen.
3. Wenn Sie Azure Integration Runtime zum Kopieren von Daten verwenden, können Sie größere "Data Integration Units (DIU)" (>4) festlegen, um weitere Rechenressourcen zu nutzen. Prüfen Sie dort die anwendbaren Szenarien.
4. „Grad der Kopierparallelität“ steuert die Partitionsnummern. Ein zu großer Wert schadet manchmal der Leistung. Deshalb wird empfohlen, diesen Wert wie folgt festzulegen: (DIU oder Anzahl der selbstgehosteten IR-Knoten) × (2 bis 4).

Beispiel: Vollständiges Laden aus einer großen Tabelle mit physischen Partitionen

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

Beispiel: Abfrage mit dynamischer Bereichspartition

"source": {
    "type": "SqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 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>"
    }
}

Beispielabfrage zur Überprüfung der physischen Partition

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]'

Wenn die Tabelle eine physische Partition besitzt, würde „HasPartition“ wie folgt als „Yes“ (Ja) angezeigt werden.

Bewährte Methode zum Laden von Daten in SQL Server

Wenn Sie Daten in SQL Server kopieren, ist möglicherweise ein anderes Schreibverhalten erforderlich:

• Anfügen: Meine Quelldaten enthalten nur neue Datensätze.
• Upsert: Meine Quelldaten umfassen sowohl Einfügungen als auch Aktualisierungen.
• Überschreiben: Ich möchte die gesamte Dimensionstabelle jedes Mal neu laden.
• Schreiben von Daten mit benutzerdefinierter Logik: Ich benötige eine zusätzliche Verarbeitung vor dem endgültigen Einfügen in die Zieltabelle.

Informationen zur Konfiguration und zu bewährten Methoden finden Sie in den entsprechenden Abschnitten.

Anfügen von Daten

Das Anhängen von Daten ist das Standardverhalten dieses SQL Server Sink-Connectors. Der Dienst führt eine Masseneinfügung durch, um effizient in Ihre Tabelle zu schreiben. Sie können die Quelle und Senke in der Kopieraktivität entsprechend konfigurieren.

Durchführen von Upsert für Daten

Die Kopieraktivität unterstützt ab sofort das native Laden von Daten in eine temporäre Datenbanktabelle und das anschließende Aktualisieren der Daten in der Senkentabelle, wenn der Schlüssel vorhanden ist. Andernfalls werden neue Daten eingefügt. Weitere Informationen zu upsert-Einstellungen in Kopieraktivitäten finden Sie unter SQL Server als Senke.

Überschreiben der gesamten Tabelle

Sie können die preCopyScript-Eigenschaft in einer Kopieraktivitätssenke konfigurieren. In diesem Fall führt Azure Data Factory für jede ausgeführte Copy-Aktivität zuerst das Skript aus. Dann wird der Kopiervorgang ausgeführt, um die Daten einzufügen. Beispiel: Um die gesamte Tabelle mit den neuesten Daten zu überschreiben, geben Sie ein Skript an, um zunächst alle Datensätze zu löschen, bevor die neuen Daten durch Massenladen aus der Quelle eingefügt werden.

Schreiben von Daten mit benutzerdefinierter Logik

Die Schritte zum Schreiben von Daten mit benutzerdefinierter Logik ähneln den im Abschnitt Durchführen von Upsert für Daten beschriebenen Schritten. Wenn vor dem Einfügen von Quelldaten in die Zieltabelle weitere Verarbeitungsschritte erforderlich sind, können Sie die Daten in eine Stagingtabelle laden und dann Aktivitäten einer gespeicherten Prozedur oder eine gespeicherte Prozedur in der Kopieraktivität der Senke aufrufen, um die Daten anzuwenden.

Aufrufen der gespeicherten Prozedur von der SQL-Senke

Wenn Sie Daten in SQL Server Datenbank kopieren, können Sie auch eine vom Benutzer angegebene gespeicherte Prozedur mit zusätzlichen Parametern für jeden Batch der Quelltabelle konfigurieren und aufrufen. Das Feature der gespeicherten Prozedur nutzt Tabellenwertparameter. Beachten Sie, dass der Dienst die gespeicherte Prozedur automatisch in seine eigene Transaktion umschließt, sodass jede in der gespeicherten Prozedur erstellte Transaktion zu einer geschachtelten Transaktion wird und Auswirkungen auf die Ausnahmebehandlung haben kann.

Sie können eine gespeicherte Prozedur nutzen, wenn integrierte Kopiermechanismen nicht den Zweck erfüllen. Ein Beispiel hierfür ist ein Szenario, in dem Sie vor dem endgültigen Einfügen von Quelldaten in die Zieltabelle eine zusätzliche Verarbeitung anwenden möchten. Beispiele für eine zusätzliche Verarbeitung sind das Zusammenführen von Spalten, das Suchen nach zusätzlichen Werten und das Einfügen in mehr als eine Tabelle.

Das folgende Beispiel zeigt, wie Sie mithilfe einer gespeicherten Prozedur einen Upsert in eine Tabelle in der SQL Server-Datenbank ausführen. Wenn Eingabedaten vorhanden sind und die Senkentabelle Marketing heißt, sind drei Spalten vorhanden: ProfileID, State und Category. Führen Sie den Upsert-Vorgang basierend auf der Spalte ProfileID aus, und wenden Sie ihn nur auf die Kategorie „ProductA“ an.

1. Definieren Sie in Ihrer Datenbank den Tabellentyp mit dem gleichen Namen wie sqlWriterTableType. Das Schema des Tabellentyps muss mit dem Schema übereinstimmen, das von den Eingabedaten zurückgegeben wird.

   CREATE TYPE [dbo].[MarketingType] AS TABLE(
       [ProfileID] [varchar](256) NOT NULL,
       [State] [varchar](256) NOT NULL,
       [Category] [varchar](256) NOT NULL
   )
   

2. Definieren Sie die gespeicherte Prozedur in Ihrer Datenbank mit demselben Namen wie SqlWriterStoredProcedureName. Sie verarbeitet die Eingabedaten aus der angegebenen Quelle und führt sie mit der Ausgabetabelle zusammen. Der Parametername des Tabellentyps in der gespeicherten Prozedur entspricht dem im Dataset definierten 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. Definieren Sie den Abschnitt SqlSink in der Kopieraktivität wie folgt:

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

Eigenschaften von Mapping Data Flow

Beim Transformieren von Daten im Zuordnungsdatenfluss können Sie Tabellen aus SQL Server Datenbank lesen und schreiben. Weitere Informationen finden Sie unter Quellentransformation und Senkentransformation in Zuordnungsdatenflüssen.

Quellentransformation

In der folgenden Tabelle sind die von SQL Server Quelle unterstützten Eigenschaften aufgeführt. Sie können diese Eigenschaften auf der Registerkarte Quelloptionen bearbeiten.

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

Beispiel für SQL Server Quellskript

Wenn Sie SQL Server als Quelltyp verwenden, lautet das zugeordnete Datenflussskript:

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

Senkentransformation

In der folgenden Tabelle sind die Eigenschaften aufgeführt, die von SQL Server Sink unterstützt werden. Sie können diese Eigenschaften auf der Registerkarte Senkenoptionen bearbeiten.

SQL Server Sink-Skript Beispiel

Wenn Sie SQL Server als Sinktyp verwenden, lautet das zugeordnete Datenflussskript:

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

Datentypzuordnung für SQL Server

Wenn Sie Daten aus und in SQL Server kopieren, werden die folgenden Zuordnungen aus SQL Server Datentypen zu Azure Data Factory Zwischendatentypen verwendet. Synapse-Pipelines, die Data Factory implementieren, verwenden die gleichen Zuordnungen. Informationen dazu, wie die Kopieraktivität das Quellschema und den Datentyp zur Senke zuordnet, finden Sie unter Schema- und Datentypzuordnungen.

Eigenschaften der Lookup-Aktivität

Ausführliche Informationen zu den Eigenschaften finden Sie unter Lookup-Aktivität.

Eigenschaften der GetMetadata-Aktivität

Ausführliche Informationen zu den Eigenschaften finden Sie unter GetMetadata-Aktivität.

Verwenden von Always Encrypted

Wenn Sie Daten aus/in SQL Server mit Always Encrypted kopieren, führen Sie die folgenden Schritte aus:

1. Speichern Sie den Column Master Key (CMK) in einem Azure Key Vault. Erfahren Sie mehr über how to configure Always Encrypted by using Azure Key Vault

2. Stellen Sie sicher, dass der Zugriff auf den Schlüsseltresor gewährt wird, in dem der Spaltenhauptschlüssel (Column Master Key, CMK) gespeichert ist. Die erforderlichen Berechtigungen finden Sie in diesem Artikel.

3. Erstellen Sie einen verknüpften Dienst, um eine Verbindung mit der SQL-Datenbank herzustellen und aktivieren Sie die „Always Encrypted“-Funktion mithilfe einer verwalteten Identität oder eines Dienstprinzipals.

Natives Change Data Capture

Azure Data Factory können systemeigene Änderungsdatenerfassungsfunktionen für SQL Server, Azure SQL DB und Azure SQL MI unterstützen. Die geänderten Daten einschließlich Einfügen, Aktualisieren und Löschen von Zeilen in SQL-Speichern können durch den ADF-Zuordnungsdatenfluss automatisch erkannt und extrahiert werden. Mit der No-Code-Benutzeroberfläche im Zuordnungsdatenfluss können Benutzer ganz einfach ein Datenreplikationsszenario aus SQL-Speichern einrichten, indem sie eine Datenbank als Zielspeicher anhängen. Darüber hinaus können Benutzer auch eine beliebige Datentransformationslogik zwischenschalten, um ein inkrementelles ETL-Szenario aus SQL-Speichern zu erreichen.

Stellen Sie sicher, dass Sie die Pipeline und den Aktivitätsnamen unverändert lassen, damit der Prüfpunkt von ADF aufgezeichnet werden kann, damit Sie geänderte Daten aus der letzten Ausführung automatisch erhalten. Wenn Sie ihren Pipelinenamen oder Aktivitätsnamen ändern, wird der Prüfpunkt zurückgesetzt, was dazu führt, dass Sie bei der nächsten Ausführung von Anfang an beginnen oder Änderungen von jetzt erhalten. Wenn Sie den Pipelinenamen oder Aktivitätsnamen ändern möchten, aber weiterhin den Prüfpunkt beibehalten möchten, um geänderte Daten aus der letzten Ausführung automatisch abzurufen, verwenden Sie hierzu bitte Ihren eigenen Prüfpunktschlüssel in der Datenflussaktivität.

Wenn Sie die Pipeline debuggen, funktioniert dieses Feature genauso. Beachten Sie, dass der Prüfpunkt zurückgesetzt wird, wenn Sie Ihren Browser während der Debug-Ausfürung aktualisieren. Wenn Sie mit dem Pipelineergebnis der Debug-Ausführung zufrieden sind, können Sie die Pipeline veröffentlichen und auslösen. Wenn Sie ihre veröffentlichte Pipeline zum ersten Mal auslösen, wird sie automatisch von Anfang an neu gestartet oder erhält von nun an Änderungen.

Im Abschnitt „Überwachung“ haben Sie immer die Möglichkeit, eine Pipeline erneut ausführen. Dabei werden die geänderten Daten immer vom vorherigen Prüfpunkt des ausgewählten Pipelinelaufs erfasst.

Beispiel 1:

Wenn Sie eine Quellentransformation, die ein SQL CDC-fähiges Dataset referenziert, direkt mit einer Senkentransformation verketten, die eine Datenbank in einem Zuordnungsdatenfluss referenziert, werden die in der SQL-Quelle vorgenommenen Änderungen automatisch auf die Zieldatenbank angewendet, sodass Sie ganz einfach ein Datenreplikationsszenario zwischen Datenbanken erhalten. Sie können die Updatemethode in der Senkentransformation verwenden, um auszuwählen, ob Sie Einfüge-, Aktualisierungs- oder Löschvorgänge in der Zieldatenbank zulassen möchten. Unten sehen Sie das Beispielskript im Zuordnungsdatenfluss.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

Beispiel 2:

Wenn Sie ein ETL-Szenario anstelle der Datenreplikation zwischen Datenbanken über SQL CDC aktivieren möchten, können Sie im Zuordnungsdatenfluss Ausdrücke einschließlich isInsert(1), isUpdate(1) und isDelete(1) verwenden, um die Zeilen mit unterschiedlichen Vorgangstypen zu unterscheiden. Nachfolgend sehen Sie eines der Beispielskripts für den Zuordnungsdatenfluss zur Ableitung einer Spalte mit dem Wert 1 zur Kennzeichnung eingefügter Zeilen, 2 zur Kennzeichnung aktualisierter Zeilen und 3 zur Kennzeichnung gelöschter Zeilen für Downstreamtransformationen zur Verarbeitung der Deltadaten.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

Bekannte Einschränkung:

• Nur Nettoänderungen von SQL CDC werden von ADF über cdc.fn_cdc_get_net_changes_ geladen.

Beheben von Verbindungsproblemen

1. Konfigurieren Sie Ihre SQL Server Instanz so, dass Remoteverbindungen akzeptiert werden. Starten Sie SQL Server Management Studio, klicken Sie mit der rechten Maustaste auf server, und wählen Sie Eigenschaften aus. Wählen Sie in der Liste den Eintrag Verbindungen aus, und aktivieren Sie das Kontrollkästchen Remoteverbindungen mit diesem Server zulassen.

   

   Ausführliche Schritte finden Sie unter Konfigurieren der Serverkonfigurationsoption „Remotezugriff“.

2. Starten Sie SQL Server-Konfigurations-Manager. Erweitern Sie SQL Server Netzwerkkonfiguration für die gewünschte Instanz, und wählen Sie Protocols für MSSQLSERVER aus. Die Protokolle werden im rechten Bereich angezeigt. Aktivieren Sie TCP/IP, indem Sie mit der rechten Maustaste auf TCP/IP klicken und Aktivieren auswählen.

   

   Weitere Informationen und alternative Methoden zum Aktivieren von TCP/IP finden Sie unter Aktivieren oder Deaktivieren eines Servernetzwerkprotokolls.

3. Doppelklicken Sie im gleichen Fenster auf TCP/IP, um das Fenster TCP/IP-Eigenschaften zu öffnen.

4. Wechseln Sie zur Registerkarte IP-Adressen. Scrollen Sie nach unten zum Abschnitt IPAll. Notieren Sie sich den TCP-Port. Der Standardport ist 1433.

5. Erstellen Sie eine rule für die Windows Firewall auf dem Computer, um eingehenden Datenverkehr über diesen Port zuzulassen.

6. Verify connection: Verwenden Sie SQL Server Management Studio von einem anderen Computer, um eine Verbindung mit SQL Server herzustellen, indem Sie einen vollqualifizierten Namen verwenden. z. B. "<machine>.<domain>.corp.<company>.com,1433".

Aktualisieren der SQL Server-Version

Um die SQL Server-Version zu aktualisieren, führen Sie auf der Seite Edit linked service wählen Sie Recommended unter Version aus, und konfigurieren Sie den verknüpften Dienst, indem Sie auf Linked-Diensteigenschaften für die empfohlene Version verweisen.

Unterschiede zwischen der empfohlenen Version und der Legacyversion

Die folgende Tabelle zeigt die Unterschiede zwischen SQL Server verwendung der empfohlenen und der Legacyversion.

Verwandte Inhalte

Eine Liste der Datenspeicher, die als Quellen und Senken für die Kopieraktivität unterstützt werden, finden Sie unter Unterstützte Datenspeicher.