Kopírování a transformace dat do a z SQL Serveru pomocí služby Azure Data Factory nebo Azure Synapse Analytics

PLATÍ PRO: Azure Data Factory Azure Synapse Analytics

Tento článek popisuje, jak pomocí aktivity kopírování v kanálech Azure Data Factory a Azure Synapse kopírovat data z databáze SQL Serveru a do databáze SQL Serveru a pomocí Tok dat transformovat data v databázi SQL Serveru. Další informace najdete v úvodním článku pro Azure Data Factory nebo Azure Synapse Analytics.

Podporované funkce

Tento konektor SQL Serveru je podporovaný pro následující funkce:

(1) Prostředí Azure Integration Runtime (2) Místní prostředí Integration Runtime

Seznam úložišť dat podporovaných jako zdroje nebo jímky aktivitou kopírování najdete v tabulce Podporované úložiště dat.

Konkrétně tento konektor SQL Serveru podporuje:

• SQL Server verze 2005 a novější
• Kopírování dat pomocí ověřování SQL nebo Windows
• Jako zdroj načítá data pomocí dotazu SQL nebo uložené procedury. Můžete se také rozhodnout pro paralelní kopírování ze zdroje SQL Serveru. Podrobnosti najdete v části Paralelní kopírování z databáze SQL.
• Jako jímka automaticky vytváří cílovou tabulku, pokud neexistuje na základě zdrojového schématu; připojení dat k tabulce nebo vyvolání uložené procedury s vlastní logikou během kopírování

SQL Server Express LocalDB není podporován.

Požadavky

Pokud se vaše úložiště dat nachází uvnitř místní sítě, virtuální sítě Azure nebo amazonového privátního cloudu, musíte nakonfigurovat místní prostředí Integration Runtime pro připojení k němu.

Pokud je vaše úložiště dat spravovanou cloudovou datovou službou, můžete použít Azure Integration Runtime. Pokud je přístup omezený na IP adresy schválené v pravidlech brány firewall, můžete do seznamu povolených přidat IP adresy prostředí Azure Integration Runtime.

K přístupu k místní síti bez nutnosti instalace a konfigurace místního prostředí Integration Runtime můžete také použít funkci Runtime integrace spravované virtuální sítě ve službě Azure Data Factory.

Další informace o mechanismech zabezpečení sítě a možnostech podporovaných službou Data Factory najdete v tématu Strategie přístupu k datům.

Začínáme

K provedení aktivita Copy s kanálem můžete použít jeden z následujících nástrojů nebo sad SDK:

• Nástroj pro kopírování dat
• Azure Portal
• Sada .NET SDK
• Sada Python SDK
• Azure PowerShell
• Rozhraní REST API
• Šablona Azure Resource Manageru

Vytvoření propojené služby SQL Serveru pomocí uživatelského rozhraní

Pomocí následujícího postupu vytvořte propojenou službu SQL Serveru v uživatelském rozhraní webu Azure Portal.

1. Přejděte na kartu Správa v pracovním prostoru Azure Data Factory nebo Synapse a vyberte Propojené služby a pak klikněte na Nový:

   • Azure Data Factory
   • Azure Synapse

   

2. Vyhledejte SQL a vyberte konektor SQL Serveru.

   

3. Nakonfigurujte podrobnosti o službě, otestujte připojení a vytvořte novou propojenou službu.

   

Podrobnosti konfigurace konektoru

Následující části obsahují podrobnosti o vlastnostech, které slouží k definování entit kanálu Data Factory a Synapse specifických pro konektor databáze SQL Serveru.

Vlastnosti propojené služby

Doporučená verze SQL Serveru podporuje protokol TLS 1.3. Pokud používáte starší verzi, projděte si tuto část a upgradujte propojenou službu SQL Serveru. Podrobnosti o vlastnosti najdete v odpovídajících oddílech.

• Doporučená verze
• Starší verze

Doporučená verze

Pokud použijete doporučenou verzi, podporují se pro propojenou službu SQL Serveru tyto obecné vlastnosti:

Další vlastnosti připojení najdete v následující tabulce:

Ověřování SQL

Pokud chcete použít ověřování SQL, kromě obecných vlastností popsaných v předchozí části zadejte následující vlastnosti:

Příklad: Použití ověřování SQL

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

Příklad: Použití ověřování SQL s heslem ve službě 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"
        }
    }
}

Příklad: Použití funkce 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"
        }
    }
}

Ověřování systému Windows

Pokud chcete použít ověřování systému Windows, kromě obecných vlastností popsaných v předchozí části zadejte následující vlastnosti:

Příklad: Použití ověřování systému Windows

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

Příklad: Použití ověřování Windows s heslem ve službě 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"
        }
    }
}

Ověřování spravované identity přiřazené uživatelem

Pracovní prostor datové továrny nebo Synapse je možné přidružit ke spravovaným identitám přiřazeným uživatelem, které představují službu při ověřování v jiných prostředcích v Azure. Tuto spravovanou identitu můžete použít pro OVĚŘOVÁNÍ SQL Serveru na virtuálních počítačích Azure. Určený pracovní prostor továrny nebo Synapse může přistupovat k datům z databáze nebo do databáze a kopírovat je pomocí této identity.

Pokud chcete použít ověřování spravované identity přiřazené uživatelem, kromě obecných vlastností popsaných v předchozí části zadejte následující vlastnosti:

Musíte také postupovat podle následujících kroků:

1. Udělte oprávnění spravované identitě přiřazené uživatelem.

2. Povolte ověřování Microsoft Entra na SQL Serveru na virtuálních počítačích Azure.

3. Vytvořte uživatele databáze s omezením pro spravovanou identitu přiřazenou uživatelem. Připojte se k databázi nebo ke které chcete kopírovat data pomocí nástrojů, jako je SQL Server Management Studio, s identitou Microsoft Entra, která má alespoň oprávnění ALTER ANY USER. Spusťte následující příkaz T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

4. Vytvořte jednu nebo více spravovaných identit přiřazených uživatelem a udělte spravované identitě přiřazené uživatelem potřebná oprávnění jako obvykle pro uživatele SQL a další. Spusťte následující kód. Další možnosti najdete v tomto dokumentu.

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

5. Přiřaďte k datové továrně jednu nebo více spravovaných identit přiřazených uživatelem a vytvořte přihlašovací údaje pro každou spravovanou identitu přiřazenou uživatelem.

6. Nakonfigurujte propojenou službu SQL Serveru.

Příklad

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

Starší verze

Pokud použijete starší verzi, podporují se pro propojenou službu SQL Serveru tyto obecné vlastnosti:

Tento konektor SQL Serveru podporuje následující typy ověřování. Podrobnosti najdete v odpovídajících částech.

• Ověřování SQL pro starší verzi
• Ověřování systému Windows pro starší verzi

Ověřování SQL pro starší verzi

Pokud chcete použít ověřování SQL, kromě obecných vlastností popsaných v předchozí části zadejte následující vlastnosti:

Ověřování systému Windows pro starší verzi

Pokud chcete použít ověřování systému Windows, kromě obecných vlastností popsaných v předchozí části zadejte následující vlastnosti:

Vlastnosti datové sady

Úplný seznam oddílů a vlastností dostupných pro definování datových sad najdete v článku o datových sadách . Tato část obsahuje seznam vlastností podporovaných datovou sadou SQL Serveru.

Pokud chcete kopírovat data z databáze SQL Serveru a do databáze SQL Serveru, jsou podporovány následující vlastnosti:

Příklad

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

Vlastnosti aktivity kopírování

Úplný seznam oddílů a vlastností dostupných k definování aktivit najdete v článku Pipelines . Tato část obsahuje seznam vlastností podporovaných zdrojem a jímkou SQL Serveru.

SQL Server jako zdroj

Pokud chcete kopírovat data z SQL Serveru, nastavte typ zdroje v aktivitě kopírování na SqlSource. Ve zdrojové části aktivity kopírování jsou podporovány následující vlastnosti:

Je třeba počítat s následujícím:

• Pokud je pro SqlSource zadán sqlReaderQuery, aktivita kopírování spustí tento dotaz na zdroj SQL Serveru pro získání dat. Uloženou proceduru můžete také zadat zadáním sqlReaderStoredProcedureName a storedProcedureParameters , pokud uložená procedura přebírá parametry.
• Při použití uložené procedury ve zdroji k načtení dat si všimněte, že uložená procedura je navržena jako vrácení jiného schématu, pokud je předána jiná hodnota parametru, může dojít k selhání nebo může dojít k neočekávanému výsledku při importu schématu z uživatelského rozhraní nebo při kopírování dat do databáze SQL s automatickým vytvořením tabulky.

Příklad: Použití dotazu 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>"
            }
        }
    }
]

Příklad: Použití uložené procedury

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

Definice uložené procedury

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 jako jímka

Pokud chcete kopírovat data do SQL Serveru, nastavte typ jímky v aktivitě kopírování na SqlSink. V části jímky aktivity kopírování jsou podporovány následující vlastnosti:

Příklad 1: Připojení dat

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

Příklad 2: Vyvolání uložené procedury během kopírování

Další informace najdete v části Volání uložené procedury z jímky 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" }
                }
            }
        }
    }
]

Příklad 3: Upsert data

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

Paralelní kopírování z databáze SQL

Konektor SQL Serveru v aktivitě kopírování poskytuje integrované dělení dat pro paralelní kopírování dat. Možnosti dělení dat najdete na kartě Zdroj aktivity kopírování.

Když povolíte dělené kopírování, aktivita kopírování spouští paralelní dotazy na zdroj SQL Serveru, aby načetla data podle oddílů. Paralelní stupeň se řídí parallelCopies nastavením aktivity kopírování. Pokud například nastavíte parallelCopies hodnotu čtyři, služba souběžně vygeneruje a spouští čtyři dotazy na základě zadané možnosti a nastavení oddílu a každý dotaz načte část dat z SQL Serveru.

Doporučujeme povolit paralelní kopírování s dělením dat, zejména pokud načítáte velké množství dat z SQL Serveru. Následující konfigurace jsou navržené pro různé scénáře. Při kopírování dat do souborového úložiště dat se doporučuje zapisovat do složky jako více souborů (zadat pouze název složky), v takovém případě je výkon lepší než zápis do jednoho souboru.

Osvědčené postupy pro načtení dat s možností oddílu:

1. Zvolte výrazný sloupec jako sloupec oddílu (například primární klíč nebo jedinečný klíč), abyste se vyhnuli nerovnoměrné distribuci dat.
2. Pokud tabulka obsahuje předdefinovaný oddíl, použijte možnost oddílu Fyzické oddíly tabulky, abyste dosáhli lepšího výkonu.
3. Pokud ke kopírování dat používáte Prostředí Azure Integration Runtime, můžete nastavit větší počet jednotek Integrace Dat (DIU) (>4) a využít tak více výpočetních prostředků. Zkontrolujte tam příslušné scénáře.
4. "Stupeň paralelismu kopírování" řídí čísla oddílů, nastavení příliš velkého čísla někdy snižuje výkon, doporučujeme nastavit toto číslo jako (DIU nebo počet uzlů místního prostředí IR) * (2 až 4).

Příklad: Úplné načtení z velké tabulky s fyzickými oddíly

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

Příklad: Dotaz s oddílem dynamického rozsahu

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

Ukázkový dotaz pro kontrolu fyzického oddílu

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

Pokud tabulka obsahuje fyzický oddíl, zobrazí se "HasPartition" jako "ano", například následující.

Osvědčený postup při načítání dat do SQL Serveru

Při kopírování dat do SQL Serveru můžete vyžadovat jiné chování zápisu:

• Připojení: Zdrojová data mají pouze nové záznamy.
• Upsert: Zdrojová data obsahují vložení i aktualizace.
• Přepsat: Pokaždé chci znovu načíst celou tabulku dimenzí.
• Psaní s vlastní logikou: Potřebuji další zpracování před posledním vložením do cílové tabulky.

Informace o konfiguraci a osvědčených postupech najdete v příslušných částech.

Připojení dat

Připojení dat je výchozím chováním tohoto konektoru jímky SQL Serveru. Služba provede hromadné vložení pro efektivní zápis do tabulky. Zdroj a jímku můžete odpovídajícím způsobem nakonfigurovat v aktivitě kopírování.

Upsert dat

aktivita Copy teď podporuje nativní načítání dat do dočasné tabulky databáze a následné aktualizace dat v tabulce jímky, pokud existuje klíč, a jinak vložit nová data. Další informace o nastavení upsertu v aktivitách kopírování najdete v tématu SQL Server jako jímka.

Přepsat celou tabulku

Vlastnost preCopyScript můžete nakonfigurovat v jímce aktivity kopírování. V tomto případě pro každou aktivitu kopírování, která se spouští, služba nejprve spustí skript. Potom spustí kopii pro vložení dat. Pokud chcete například přepsat celou tabulku nejnovějšími daty, zadejte skript, který nejprve odstraní všechny záznamy před hromadným načtením nových dat ze zdroje.

Zápis dat pomocí vlastní logiky

Postup zápisu dat s vlastní logikou je podobný krokům popsaným v části Upsert data . Pokud potřebujete použít dodatečné zpracování před konečným vložením zdrojových dat do cílové tabulky, můžete načíst pracovní tabulku a pak vyvolat aktivitu uložené procedury nebo vyvolat uloženou proceduru v jímce aktivity kopírování, která použije data.

Vyvolání uložené procedury z jímky SQL

Při kopírování dat do databáze SQL Serveru můžete také nakonfigurovat a vyvolat uživatelem zadanou uloženou proceduru s dalšími parametry v každé dávce zdrojové tabulky. Funkce uložené procedury využívá parametry hodnot tabulky. Všimněte si, že služba automaticky zabalí uloženou proceduru do své vlastní transakce, takže jakákoli transakce vytvořená uvnitř uložené procedury se stane vnořenou transakcí a může mít vliv na zpracování výjimek.

Uloženou proceduru můžete použít, když předdefinované mechanismy kopírování neslouží k účelu. Příkladem je, když chcete použít dodatečné zpracování před posledním vložením zdrojových dat do cílové tabulky. Některé další příklady zpracování jsou, když chcete sloučit sloupce, vyhledat další hodnoty a vložit do více než jedné tabulky.

Následující ukázka ukazuje, jak pomocí uložené procedury provést upsert do tabulky v databázi SQL Serveru. Předpokládejme, že vstupní data a tabulka Marketing jímky mají tři sloupce: ProfileID, State a Category. Proveďte upsert založený na sloupci ProfileID a použijte ho pouze pro konkrétní kategorii s názvem ProductA.

1. V databázi definujte typ tabulky se stejným názvem jako sqlWriterTableType. Schéma typu tabulky je stejné jako schéma vrácené vstupními daty.

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

2. V databázi definujte uloženou proceduru se stejným názvem jako sqlWriterStoredProcedureName. Zpracovává vstupní data ze zadaného zdroje a slučuje se do výstupní tabulky. Název parametru typu tabulky v uložené proceduře je stejný jako tableName definovaný v datové sadě.

   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. V aktivitě kopírování definujte oddíl jímky SQL následujícím způsobem:

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

Mapování vlastností toku dat

Při transformaci dat při mapování toku dat můžete číst a zapisovat do tabulek z databáze SQL Serveru. Další informace najdete v tématu transformace zdroje a transformace jímky v mapování toků dat.

Transformace zdroje

V následující tabulce jsou uvedeny vlastnosti podporované zdrojem SQL Serveru. Tyto vlastnosti můžete upravit na kartě Možnosti zdroje.

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

Příklad zdrojového skriptu SQL Serveru

Pokud jako typ zdroje použijete SQL Server, přidružený skript toku dat:

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

Transformace jímky

Následující tabulka uvádí vlastnosti podporované jímkou SQL Serveru. Tyto vlastnosti můžete upravit na kartě Možnosti jímky.

Ukázkový skript jímky SQL Serveru

Při použití SQL Serveru jako typu jímky je přidružený skript toku dat:

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

Mapování datových typů pro SQL Server

Když kopírujete data z a do SQL Serveru, použijí se následující mapování z datových typů SQL Serveru na dočasné datové typy služby Azure Data Factory. Kanály Synapse, které implementují službu Data Factory, používají stejné mapování. Informace o tom, jak aktivita kopírování mapuje zdrojové schéma a datový typ na jímku, najdete v tématu Mapování schématu a datového typu.

Vlastnosti aktivity vyhledávání

Podrobnosti o vlastnostech najdete v aktivitě Vyhledávání.

Vlastnosti aktivity GetMetadata

Podrobnosti o vlastnostech najdete v aktivitě GetMetadata.

Použití funkce Always Encrypted

Při kopírování dat z nebo do SQL Serveru s funkcí Always Encrypted postupujte následovně:

1. Uložte hlavní klíč sloupce (CMK) ve službě Azure Key Vault. Další informace o konfiguraci funkce Always Encrypted pomocí služby Azure Key Vault

2. Nezapomeňte udělit přístup k trezoru klíčů, kde je uložený hlavní klíč sloupce (CMK ). Požadovaná oprávnění najdete v tomto článku .

3. Vytvořte propojenou službu pro připojení k databázi SQL a povolte funkci Always Encrypted pomocí spravované identity nebo instančního objektu.

Nativní zachytávání dat změn

Azure Data Factory může podporovat nativní možnosti zachytávání dat změn pro SQL Server, Azure SQL DB a Azure SQL MI. Změněná data, včetně vložení, aktualizace a odstranění řádků v úložištích SQL, je možné automaticky rozpoznat a extrahovat mapováním toku dat ADF. Bez zkušeností s kódem při mapování toku dat můžou uživatelé snadno dosáhnout scénáře replikace dat z úložišť SQL připojením databáze jako cílového úložiště. Kromě toho mohou uživatelé také vytvořit libovolnou logiku transformace dat mezi tím, aby dosáhli scénáře přírůstkového ETL z úložišť SQL.

Ujistěte se, že název kanálu a aktivity zůstane beze změny, aby bylo možné kontrolní bod zaznamenat službou ADF, abyste získali změněná data z posledního spuštění automaticky. Pokud změníte název kanálu nebo název aktivity, kontrolní bod se resetuje, což vede k tomu, že v dalším běhu začnete od začátku nebo se změny provedou od nynějška. Pokud chcete změnit název kanálu nebo název aktivity, ale přesto zachovat kontrolní bod, abyste získali změněná data z posledního spuštění automaticky, použijte k tomu vlastní klíč kontrolního bodu v aktivitě toku dat.

Při ladění kanálu funguje tato funkce stejně. Mějte na paměti, že kontrolní bod se resetuje při aktualizaci prohlížeče během spuštění ladění. Jakmile budete spokojeni s výsledkem kanálu spuštění ladění, můžete pokračovat v publikování a aktivaci kanálu. V okamžiku, kdy poprvé aktivujete publikovaný kanál, se automaticky restartuje od začátku nebo od této chvíle dojde ke změnám.

V části monitorování máte vždy možnost znovu spustit kanál. Když to uděláte, změněná data se vždy zaznamenávají z předchozího kontrolního bodu vybraného kanálu.

Příklad 1:

Když přímo zřetězíte zdrojovou transformaci odkazovanou na datovou sadu s povolenou datovou sadou SQL CDC s transformací jímky odkazovanou na databázi v mapování toku dat, změny provedené ve zdroji SQL se automaticky použijí na cílovou databázi, abyste mohli snadno získat scénář replikace dat mezi databázemi. Metodu aktualizace v transformaci jímky můžete použít k výběru, jestli chcete povolit vložení, povolit aktualizaci nebo povolit odstranění v cílové databázi. Ukázkový skript mapování toku dat je následující.

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

Příklad 2:

Pokud chcete povolit scénář ETL místo replikace dat mezi databází prostřednictvím SLUŽBY CDC SQL, můžete použít výrazy v mapování toku dat včetně isInsert(1), isUpdate(1) a isDelete(1) k rozlišení řádků s různými typy operací. Následuje jeden z ukázkových skriptů pro mapování toku dat na odvození jednoho sloupce s hodnotou: 1 pro označení vložených řádků, 2 pro označení aktualizovaných řádků a 3 pro označení odstraněných řádků pro podřízené transformace pro zpracování rozdílových dat.

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

Známé omezení:

• ADF načte pouze čisté změny z SQL CDC prostřednictvím cdc.fn_cdc_get_net_changes_.

Odstraňování potíží s připojením

1. Nakonfigurujte instanci SQL Serveru tak, aby přijímala vzdálená připojení. Spusťte aplikaci SQL Server Management Studio, klikněte pravým tlačítkem myši na server a vyberte Vlastnosti. V seznamu vyberte Připojení a zaškrtněte políčko Povolit vzdálená připojení k tomuto serveru.

   

   Podrobný postup najdete v tématu Konfigurace možnosti konfigurace serveru vzdáleného přístupu.

2. Spusťte NÁSTROJ SQL Server Configuration Manager. Rozbalte konfiguraci sítě SQL Serveru pro požadovanou instanci a vyberte Protokoly pro MSSQLSERVER. Protokoly se zobrazí v pravém podokně. Povolte tcp/IP tak, že kliknete pravým tlačítkem na TCP/IP a vyberete Povolit.

   

   Další informace a alternativní způsoby povolení protokolu TCP/IP naleznete v tématu Povolení nebo zakázání síťového protokolu serveru.

3. Ve stejném okně poklikejte na tcp/IP a spusťte okno Vlastnosti protokolu TCP/IP.

4. Přepněte na kartu IP adresy . Posuňte se dolů a zobrazte část IPAll . Zapište port TCP. Výchozí hodnota je 1433.

5. Vytvořte pravidlo pro bránu Windows Firewall na počítači, které povolí příchozí provoz přes tento port.

6. Ověření připojení: Pokud se chcete připojit k SQL Serveru pomocí plně kvalifikovaného názvu, použijte SQL Server Management Studio z jiného počítače. Příklad: "<machine>.<domain>.corp.<company>.com,1433".

Upgrade verze SQL Serveru

Pokud chcete upgradovat verzi SQL Serveru, vyberte na stránce Upravit propojenou službu v části Verze a nakonfigurujte propojenou službu podle vlastností propojené služby pro doporučenou verzi.

Rozdíly mezi doporučenou a starší verzí

Následující tabulka ukazuje rozdíly mezi SQL Serverem pomocí doporučené a starší verze.

Související obsah

Seznam úložišť dat podporovaných jako zdroje a jímky aktivitou kopírování najdete v tématu Podporované úložiště dat.