Copie e transforme dados para e a partir do SQL Server usando o Azure Data Factory ou o Azure Synapse Analytics

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como utilizar a atividade de cópia nos pipelines Azure Data Factory e Azure Synapse para copiar dados de e para a base de dados SQL Server e usar o Fluxo de Dados para transformar dados na base de dados SQL Server. Para saber mais, leia o artigo introdutório para Azure Data Factory ou Azure Synapse Analytics.

Capacidades suportadas

Este conector SQL Server é suportado para as seguintes capacidades:

(1) Runtime de integração Azure (2) Runtime de integração auto-hospedado

Para obter uma lista de armazenamentos de dados suportados como fontes ou destinos pela atividade de cópia, consulte a tabela Armazenamentos de dados suportados.

Especificamente, este conector SQL Server suporta:

• SQL Server versão 2005 e superiores.
• Copiar dados usando SQL ou Windows authentication.
• Como origem, recuperando dados usando uma consulta SQL ou um procedimento armazenado. Também pode optar por copiar em paralelo do SQL Server como fonte, consulte a secção Cópia paralela da base de dados SQL para mais detalhes.
• Como um coletor, criando automaticamente a tabela de destino se não existir com base no esquema de origem; anexar dados a uma tabela ou invocar um procedimento armazenado com lógica personalizada durante a cópia.

SQL Server Express LocalDB não é suportado.

Pré-requisitos

Se o seu armazenamento de dados estiver localizado numa rede local, numa rede virtual Azure ou na Amazon Virtual Private Cloud, precisa de configurar um runtime de integração auto-hospedado para se ligar a ele.

Se o seu armazenamento de dados for um serviço de dados na cloud gerido, pode usar o Azure Integration Runtime. Se o acesso for restrito a IPs aprovados nas regras do firewall, pode adicionar IPs Azure Integration Runtime à lista de autorizações.

Também pode usar a funcionalidade managed virtual network integration runtime no Azure Data Factory para aceder à rede local sem instalar e configurar um runtime de integração auto-hospedado.

Para obter mais informações sobre os mecanismos de segurança de rede e as opções suportadas pelo Data Factory, consulte Estratégias de acesso a dados.

Introdução

Para executar a atividade de cópia com um pipeline, você pode usar uma das seguintes ferramentas ou SDKs:

• Ferramenta Copiar dados
• Portal do Azure
• .NET SDK
• Python SDK
• Azure PowerShell
• API REST
• Modelo do Azure Resource Manager

Crie um serviço ligado ao SQL Server usando UI

Use os seguintes passos para criar um serviço ligado ao SQL Server na interface do portal Azure.

1. Navegue até ao separador Gerir no seu espaço de trabalho Azure Data Factory ou Synapse e selecione Serviços Ligados, depois clique em Novo:

   • Azure Data Factory
   • Azure Synapse

   

2. Procure por SQL e selecione o conector SQL Server.

   

3. Configure os detalhes do serviço, teste a conexão e crie o novo serviço vinculado.

   

Detalhes de configuração do conector

As secções seguintes fornecem detalhes sobre propriedades que são usadas para definir as entidades do pipeline Data Factory e Synapse específicas para o conector de base de dados SQL Server.

Propriedades do serviço vinculado

A versão SQL Server Recommended suporta TLS 1.3. Consulte esta secção para atualizar o seu serviço associado do SQL Server se usar a versão Legacy. Para obter os detalhes da propriedade, consulte as seções correspondentes.

• Versão recomendada
• Versão legacy

Versão recomendada

Estas propriedades genéricas têm suporte para um serviço vinculado do SQL Server quando você aplica a versão recomendada :

Para obter propriedades de conexão adicionais, consulte a tabela abaixo:

Autenticação do SQL

Para usar a autenticação SQL, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Exemplo: Usar autenticação 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"
        }
    }
}

Exemplo: Use autenticação SQL com palavra-passe em 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"
        }
    }
}

Exemplo: Usar sempre criptografado

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

Autenticação do Windows (Windows authentication)

Para usar a Windows authentication, além das propriedades genéricas descritas na secção anterior, especifique as seguintes propriedades:

Exemplo: Utilize autenticação do 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"
        }
    }
}

Exemplo: Usar autenticação do Windows com palavra-passe no 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"
        }
    }
}

Autenticação de identidade gerenciada atribuída pelo usuário

Uma fábrica de dados ou um espaço de trabalho Synapse pode ser associado a identidades geridas atribuídas pelo utilizador que representam o serviço ao autenticar-se com outros recursos no Azure. Pode usar esta identidade gerida para autenticação em SQL Server em Azure VMs. A fábrica designada ou o espaço de trabalho Synapse pode acessar e copiar dados de ou para seu banco de dados usando essa identidade.

Para usar a autenticação de identidade gerenciada atribuída pelo usuário, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Você também precisa seguir os passos abaixo:

1. Conceda permissões à sua identidade gerenciada atribuída pelo usuário.

2. Ative a autenticação Microsoft Entra do seu SQL Server nas VMs Azure.

3. Crie usuários de banco de dados contidos para a identidade gerenciada atribuída pelo usuário. Conecta-te à base de dados da qual ou para a qual quiseres copiar os dados usando ferramentas como o SQL Server Management Studio, com uma identidade Microsoft Entra que tenha pelo menos a permissão ALTERAR QUALQUER UTILIZADOR. Execute o seguinte T-SQL:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

4. Crie uma ou várias identidades gerenciadas atribuídas pelo usuário e conceda à identidade gerenciada atribuída pelo usuário as permissões necessárias como você normalmente faz para usuários SQL e outros. Execute o código a seguir. Para mais opções, consulte este documento.

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

5. Atribua uma ou várias identidades gerenciadas atribuídas pelo usuário ao seu data factory e crie credenciais para cada identidade gerenciada atribuída pelo usuário.

6. Configure um serviço ligado ao SQL Server.

Exemplo

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

Versão antiga

Estas propriedades genéricas são suportadas por um serviço ligado do SQL Server quando se aplica a versão Legacy:

Este conector do SQL Server suporta os seguintes tipos de autenticação. Consulte as seções correspondentes para obter detalhes.

• Autenticação SQL para a versão herdada
• Autenticação do Windows para a versão antiga

Autenticação SQL para a versão herdada

Para usar a autenticação SQL, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades:

Windows authentication para a versão antiga

Para usar a Windows authentication, além das propriedades genéricas descritas na secção anterior, especifique as seguintes propriedades:

Propriedades do conjunto de dados

Para obter uma lista completa de seções e propriedades disponíveis para definir conjuntos de dados, consulte o artigo sobre conjuntos de dados. Esta secção fornece uma lista de propriedades suportadas pelo conjunto de dados do SQL Server.

Para copiar dados de e para uma base de dados SQL Server, são suportadas as seguintes propriedades:

Exemplo

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

Propriedades da atividade de cópia

Para obter uma lista completa de seções e propriedades disponíveis para uso para definir atividades, consulte o artigo Pipelines . Esta secção fornece uma lista de propriedades suportadas pelo código-fonte e pelo dissipador do SQL Server.

SQL Server como fonte

Para copiar dados de SQL Server, defina o tipo de origem na atividade de cópia para SqlSource. As seguintes propriedades são suportadas na seção de origem da atividade de cópia:

Tenha em atenção os seguintes pontos:

• Se sqlReaderQuery for especificado para SqlSource, a atividade de cópia executa esta consulta contra a fonte SQL Server para obter os dados. Você também pode especificar um procedimento armazenado especificando sqlReaderStoredProcedureName e storedProcedureParameters se o procedimento armazenado tiver parâmetros.
• Ao usar o procedimento armazenado na origem para recuperar dados, observe se o procedimento armazenado for projetado como retornando esquema diferente quando um valor de parâmetro diferente for passado, você poderá encontrar falha ou ver um resultado inesperado ao importar esquema da interface do usuário ou ao copiar dados para o banco de dados SQL com a criação automática de tabelas.

Exemplo: Usar consulta 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>"
            }
        }
    }
]

Exemplo: Usar um procedimento armazenado

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

A definição de procedimento armazenado

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 como sumidouro

Para copiar dados para SQL Server, defina o tipo de sink na atividade de cópia para SqlSink. As seguintes propriedades são suportadas na seção de destino da atividade de cópia:

Exemplo 1: Acrescentar dados

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

Exemplo 2: Invocar um procedimento armazenado durante a cópia

Saiba mais detalhes em Invocar um procedimento armazenado a partir de um destino 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" }
                }
            }
        }
    }
]

Exemplo 3: Inserir ou atualizar dados

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

Cópia paralela do banco de dados SQL

O conector SQL Server na atividade de cópia fornece particionamento de dados incorporado para copiar dados em paralelo. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

Quando ativas a cópia particionada, a atividade de cópia executa consultas paralelas na tua fonte do SQL Server para carregar os dados por partições. O grau paralelo é controlado pela configuração parallelCopies na atividade de cópia. Por exemplo, se definir parallelCopies como quatro, o serviço gera e executa simultaneamente quatro consultas com base na opção de partição e definições especificadas, e cada consulta recupera uma parte dos dados do seu SQL Server.

Recomenda-se ativar a cópia paralela com particionamento de dados, especialmente quando carrega grandes quantidades de dados do seu SQL Server. A seguir estão sugeridas configurações para diferentes cenários. Ao copiar dados para o armazenamento de dados baseado em arquivo, é recomendável gravar em uma pasta como vários arquivos (especifique apenas o nome da pasta), caso em que o desempenho é melhor do que gravar em um único arquivo.

Práticas recomendadas para carregar dados com a opção de partição:

1. Escolha uma coluna distinta como coluna de partição (como chave primária ou chave exclusiva) para evitar distorção de dados.
2. Se a tabela tiver partição incorporada, use a opção de partição "Partições físicas da tabela" para obter um melhor desempenho.
3. Se usar Azure Integration Runtime para copiar dados, pode definir "Unidades de Integração de Dados (DIU)" (>4) maiores para utilizar mais recursos computacionais. Verifique os cenários aplicáveis lá.
4. "Grau de paralelismo de cópia controla os números de partição, estabelecer este número demasiado grande pode por vezes prejudicar o desempenho, recomenda-se definir este número como (DIU ou número de nós IR auto-hospedados) * (2 a 4)."

Exemplo: carga completa a partir de uma tabela grande com partições físicas

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

Exemplo: consulta com partição de intervalo dinâmico

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

Exemplo de consulta para verificar a partição física

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

Se a tabela tiver partição física, você verá "HasPartition" como "sim" como a seguir.

Melhores práticas para carregar dados no SQL Server

Quando copias dados para o SQL Server, podes precisar de comportamentos de escrita diferentes:

• Acrescentar: Meus dados de origem têm apenas novos registros.
• Upsert: Meus dados de origem têm inserções e atualizações.
• Substituir: Quero recarregar toda a tabela de dimensões de cada vez.
• Escrever com lógica personalizada: preciso de processamento extra antes da inserção final na tabela de destino.

Consulte as respetivas seções para saber como configurar e práticas recomendadas.

Acrescentar dados

Anexar dados é o comportamento padrão deste conector de sink do SQL Server. O serviço faz uma inserção em massa para gravar na sua tabela de forma eficiente. Você pode configurar a origem e o coletor de acordo com a atividade de cópia.

Inserir ou atualizar dados

A atividade de cópia agora suporta carregar dados nativamente numa tabela temporária de base de dados e depois atualizar os dados na tabela de destino se a chave existir e, caso contrário, inserir novos dados. Para saber mais sobre as definições do upsert nas atividades de cópia, veja SQL Server como um sumidouro.

Substituir a tabela inteira

Você pode configurar a propriedade preCopyScript em um coletor de atividade de cópia. Nesse caso, para cada atividade de cópia executada, o serviço executa o script primeiro. Em seguida, ele executa a cópia para inserir os dados. Por exemplo, para substituir a tabela inteira pelos dados mais recentes, especifique um script para primeiro excluir todos os registros antes de carregar em massa os novos dados da fonte.

Gravar dados com lógica personalizada

As etapas para gravar dados com lógica personalizada são semelhantes às descritas na seção Upsert dados. Quando precisar aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino, você poderá carregar em uma tabela de preparo e, em seguida, invocar a atividade de procedimento armazenado ou invocar um procedimento armazenado no coletor de atividade de cópia para aplicar dados.

Invocar um procedimento armazenado a partir de um coletor SQL

Quando copia dados para a base de dados SQL Server, também pode configurar e invocar um procedimento armazenado especificado pelo utilizador com parâmetros adicionais em cada lote da tabela de origem. O recurso de procedimento armazenado tira partido dos parâmetros com valor de tabela. Observe que o serviço encapsula automaticamente o procedimento armazenado em sua própria transação, portanto, qualquer transação criada dentro do procedimento armazenado se tornará uma transação aninhada e poderá ter implicações para o tratamento de exceções.

Você pode usar um procedimento armazenado quando os mecanismos de cópia internos não atendem à finalidade. Um exemplo é quando você deseja aplicar processamento extra antes da inserção final dos dados de origem na tabela de destino. Alguns exemplos de processamento extra são quando você deseja mesclar colunas, procurar valores adicionais e inserir em mais de uma tabela.

O exemplo seguinte mostra como usar um procedimento armazenado para fazer um upsert numa tabela na base de dados do SQL Server. Suponha que os dados de entrada e a tabela de destino Marketing tenham três colunas: ProfileID, State e Category. Faça o upsert com base na coluna ProfileID e aplique-o apenas para uma categoria específica chamada "ProductA".

1. Em seu banco de dados, defina o tipo de tabela com o mesmo nome de sqlWriterTableType. O esquema do tipo de tabela é o mesmo que o esquema retornado pelos dados de entrada.

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

2. Em seu banco de dados, defina o procedimento armazenado com o mesmo nome de sqlWriterStoredProcedureName. Ele processa dados de entrada da sua fonte especificada e integra na tabela de saída. O nome do parâmetro do tipo de tabela no procedimento armazenado é o mesmo que tableName definido no conjunto de dados.

   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. Defina a seção do coletor SQL na atividade de cópia da seguinte maneira:

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

Mapeando propriedades de fluxo de dados

Ao transformar dados no mapeamento do fluxo de dados, pode ler e escrever em tabelas a partir do SQL Server Database. Para obter mais informações, consulte a transformação de origem e a transformação de coletor no mapeamento de fluxos de dados.

Transformação da fonte

A tabela abaixo lista as propriedades suportadas pelo código-fonte do SQL Server. Você pode editar essas propriedades na guia Opções de origem .

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

Exemplo de script de código fonte do SQL Server

Quando usa SQL Server como tipo de origem, o script de fluxo de dados associado é:

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

Transformação do lavatório

A tabela abaixo lista as propriedades suportadas pelo SQL Server sink. Você pode editar essas propriedades na guia Opções do coletor .

Exemplo de script de sink no SQL Server

Quando usa o SQL Server como tipo sink, o script de fluxo de dados associado é:

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

Mapeamento de tipos de dados para SQL Server

Quando copia dados do e para o SQL Server, são usados os seguintes mapeamentos dos tipos de dados do SQL Server para os tipos de dados intermédios do Azure Data Factory. Os pipelines de Synapse, que implementam o Data Factory, usam os mesmos mapeamentos. Para saber como a atividade de cópia mapeia o esquema de origem e o tipo de dados para o coletor, consulte Mapeamentos de esquema e tipo de dados.

Propriedades da atividade de consulta

Para saber detalhes sobre as propriedades, verifique Atividade de pesquisa.

Propriedades de atividade GetMetadata

Para saber detalhes sobre as propriedades, verifique a atividade GetMetadata

Utilização de Always Encrypted

Quando copiar dados de/para SQL Server com Always Encrypted, siga os passos abaixo:

1. Armazene a Column Master Key (CMK) em um Azure Key Vault. Saiba mais sobre como configurar o Always Encrypted usando Azure Key Vault

2. Certifique-se de conceder acesso ao cofre de chaves onde a Chave Mestra de Coluna (CMK) está armazenada. Consulte este artigo para obter as permissões necessárias.

3. Crie um serviço vinculado para se conectar ao seu banco de dados SQL e habilite a função 'Always Encrypted' usando a identidade gerida ou o principal de serviço.

Captura nativa de dados de alteração

O Azure Data Factory pode suportar capacidades nativas de captura de dados de alterações para SQL Server, SQL do Azure DB e SQL do Azure MI. Os dados alterados, incluindo inserção de linha, atualização e exclusão em repositórios SQL, podem ser detetados e extraídos automaticamente pelo fluxo de dados de mapeamento do ADF. Com a experiência sem código no mapeamento de fluxo de dados, os usuários podem facilmente obter o cenário de replicação de dados de repositórios SQL anexando um banco de dados como armazenamento de destino. Além disso, os utilizadores podem também compor qualquer lógica de transformação de dados para conseguir um cenário de ETL incremental utilizando repositórios SQL.

Certifique-se de manter o pipeline e o nome da atividade inalterados, para que o ponto de verificação possa ser registado pelo ADF e assim você obtenha automaticamente os dados alterados da última execução. Se alterar o nome do pipeline ou o nome da atividade, o ponto de verificação será redefinido, o que o obriga a começar do início ou a aplicar as alterações a partir de agora na próxima execução do pipeline. Caso deseje alterar o nome do pipeline ou o nome da atividade, mas queira manter o ponto de verificação para obter dados modificados automaticamente da última execução, utilize sua própria chave de ponto de verificação na atividade de fluxo de dados para alcançar isso.

Quando você depura o pipeline, esse recurso funciona da mesma forma. Lembre-se de que o ponto de verificação será redefinido quando você atualizar o navegador durante a execução de depuração. Depois de estar satisfeito com o resultado da execução de depuração do pipeline, pode publicar e acionar o pipeline. No momento em que você aciona pela primeira vez seu pipeline publicado, ele é reiniciado automaticamente desde o início ou recebe alterações a partir de agora.

Na seção de monitoramento, você sempre tem a chance de executar novamente um pipeline. Quando você está fazendo isso, os dados alterados são sempre capturados do ponto de verificação anterior da execução do pipeline selecionado.

Exemplo 1:

Quando você encadeia diretamente uma transformação de origem referenciada ao conjunto de dados habilitado para SQL CDC com uma transformação de coletor referenciada a um banco de dados em um fluxo de dados de mapeamento, as alterações ocorridas na origem SQL serão aplicadas automaticamente ao banco de dados de destino, para que você obtenha facilmente o cenário de replicação de dados entre bancos de dados. Você pode usar o método update na transformação do coletor para selecionar se deseja permitir inserir, permitir atualização ou permitir exclusão no banco de dados de destino. O script de exemplo no mapeamento de fluxo de dados é como abaixo.

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

Exemplo 2:

Se quiser habilitar o cenário de ETL em vez da replicação de dados entre bancos de dados via SQL CDC, você pode usar expressões no mapeamento de fluxo de dados, incluindo isInsert(1), isUpdate(1) e isDelete(1) para diferenciar as linhas com diferentes tipos de operação. A seguir está um dos scripts de exemplo para mapear o fluxo de dados na derivação de uma coluna com o valor: 1 para indicar linhas inseridas, 2 para indicar linhas atualizadas e 3 para indicar linhas excluídas para transformações downstream para processar os dados delta.

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

Limitação conhecida:

• Somente as alterações líquidas do SQL CDC serão carregadas pelo ADF via cdc.fn_cdc_get_net_changes_.

Resolver problemas de ligação

1. Configure a sua instância do SQL Server para aceitar ligações remotas. Inicie SQL Server Management Studio, clique com o botão direito server e selecione Properties. Selecione Conexões na lista e marque a caixa de seleção Permitir conexões remotas a este servidor .

   

   Para obter etapas detalhadas, consulte Configurar a opção de configuração do servidor de acesso remoto.

2. Comece Gestor de Configuração do SQL Server. Expanda SQL Server Network Configuration para a instância que pretende e selecione Protocols for MSSQLSERVER. Os protocolos aparecem no painel direito. Habilite o TCP/IP clicando com o botão direito do mouse em TCP/IP e selecionando Ativar.

   

   Para obter mais informações e formas alternativas de ativar o protocolo TCP/IP, consulte Ativar ou desativar um protocolo de rede de servidor.

3. Na mesma janela, clique duas vezes em TCP/IP para iniciar a janela Propriedades de TCP/IP.

4. Mude para o separador Endereços IP. Desloque-se para baixo para ver a secção IPAll. Anote a porta TCP. O padrão é 1433.

5. Crie uma regra para o Windows Firewall na máquina para permitir o tráfego recebido através desta porta.

6. Verify connection: Para ligar a SQL Server usando um nome totalmente qualificado, use SQL Server Management Studio de uma máquina diferente. Um exemplo é "<machine>.<domain>.corp.<company>.com,1433".

Atualizar a versão do SQL Server

Para atualizar a versão do SQL Server, na página Editar serviço ligado, selecione Recomendado em Versão e configure o serviço ligado consultando as Propriedades do serviço ligado para a versão recomendada.

Diferenças entre a versão recomendada e a versão herdada

A tabela abaixo mostra as diferenças entre o SQL Server usando a versão recomendada e a versão legada.

Conteúdos relacionados

Para obter uma lista de armazenamentos de dados suportados como fontes e destinos pela atividade de cópia, consulte Armazenamentos de dados suportados.