Copiar e transformar dados no Azure SQL Managed Instance usando Azure Data Factory ou Synapse Analytics

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como usar o Copy Activity para copiar dados do e para o Azure SQL Managed Instance, e usar o Fluxo de Dados para transformar dados no Azure SQL Managed Instance. Para saber mais, leia os artigos introdutórios para Azure Data Factory e Synapse Analytics.

Capacidades suportadas

Este conector Azure SQL Managed Instance é suportado para as seguintes capacidades:

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

Para atividade Copy, este conector Base de Dados SQL do Azure suporta estas funções:

• Cópia de dados utilizando autenticação SQL e autenticação de token da aplicação Microsoft Entra com um principal de serviço ou identidades geridas para recursos do Azure.
• Como origem, recuperando dados usando uma consulta SQL ou um procedimento armazenado. Você também pode optar por copiar paralelamente da fonte SQL MI, consulte a seção Cópia paralela do SQL MI para obter 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.

Pré-requisitos

Para aceder ao endpoint SQL Managed Instance público, pode usar um runtime de integração de Azure gerido. Confirme que ativa o ponto final público e que também permite o tráfego de pontos finais públicos no grupo de segurança de rede, para que o serviço se possa ligar à base de dados. Para obter mais informações, consulte estas orientações.

Para aceder ao SQL Managed Instance endpoint privado, configure um runtime de integração auto-hospedado que possa aceder à base de dados. Se aprovisionar o runtime de integração autoalojado na mesma rede virtual da instância gerida, confirme que a máquina virtual do runtime de integração está numa sub-rede diferente da instância gerida. Se aprovisionar o runtime de integração autoalojado numa rede virtual diferente da instância gerida na cloud, pode utilizar um emparelhamento de rede virtual ou uma ligação entre redes virtuais. Para mais informações, consulte Conecte a sua aplicação ao SQL Managed Instance.

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 para uma instância SQL do Azure Managed usando UI

Use os passos seguintes para criar um serviço ligado a uma instância SQL Managed na interface do portal do 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 Azure SQL Server Managed Instance.

   

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 utilizadas para definir entidades do Azure Data Factory específicas para o conector SQL Managed Instance.

Propriedades do serviço vinculado

A versão do conector Azure SQL Managed Instance Recommended suporta TLS 1.3. Consulte esta secção para atualizar a versão do seu conector de Azure SQL Managed Instance a partir do 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 são suportadas para um serviço Azure SQL Managed Instance ligado quando se aplica a versão Recomendado:

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 1: usar a autenticação SQL

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "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 2: usar autenticação SQL com palavra-passe em Azure Key Vault

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "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 3: usar a autenticação SQL com Always Encrypted

{
    "name": "AzureSqlMILinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "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 principal de serviço

Para usar a autenticação do principal de serviço, além das propriedades genéricas descritas na seção anterior, especifique as seguintes propriedades

Você também precisa seguir os passos abaixo:

1. Siga os passos para Providenciar um administrador de Microsoft Entra para o seu Managed Instance.

2. Criar uma aplicação Microsoft Entra a partir do portal Azure. Anote o nome do aplicativo e os seguintes valores que definem o serviço vinculado:

   • ID da aplicação
   • Chave de aplicação
   • ID de Locatário

3. Crie logins para a entidade de serviço. No SQL Server Management Studio (SSMS), liga-te à tua instância gerida usando uma conta SQL Server que seja um sysadmin. No banco de dados mestre , execute o seguinte T-SQL:

   CREATE LOGIN [your application name] FROM EXTERNAL PROVIDER
   

4. Crie utilizadores de banco de dados contidos para o principal de serviço. Conecte-se ao banco de dados do qual você deseja copiar dados, execute o seguinte T-SQL:

   CREATE USER [your application name] FROM EXTERNAL PROVIDER
   

5. Conceda à entidade de serviço as permissões necessárias como 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 e.g. db_owner] ADD MEMBER [your application name]
   

6. Configurar uma instância gerida do SQL vinculada ao serviço.

Exemplo: usar a autenticação da entidade de serviço

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "hostNameInCertificate": "<host name>",
            "authenticationType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": "<application key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticação de identidade gerenciada atribuída pelo sistema

Uma fábrica de dados ou um espaço de trabalho Synapse pode ser associado a uma identidade gerida atribuída ao sistema para recursos Azure que representa o serviço para autenticação a outros serviços Azure. Pode usar esta identidade gerida para a autenticação de SQL Managed Instance. O serviço designado 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 sistema, especifique as propriedades genéricas descritas na seção anterior e siga estas etapas.

1. Siga os passos para Providenciar um administrador de Microsoft Entra para o seu Managed Instance.

2. Crie logins para a identidade gerenciada atribuída ao sistema. No SQL Server Management Studio (SSMS), liga-te à tua instância gerida usando uma conta SQL Server que seja um sysadmin. No banco de dados mestre , execute o seguinte T-SQL:

   CREATE LOGIN [your_factory_or_workspace_ name] FROM EXTERNAL PROVIDER
   

3. Crie usuários de banco de dados contidos para a identidade gerenciada atribuída ao sistema. Conecte-se ao banco de dados do qual você deseja copiar dados, execute o seguinte T-SQL:

   CREATE USER [your_factory_or_workspace_name] FROM EXTERNAL PROVIDER
   

4. Conceda as permissões necessárias à identidade gerenciada atribuída ao sistema, 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 e.g. db_owner] ADD MEMBER [your_factory_or_workspace_name]
   

5. Configure um serviço ligado a SQL Managed Instance.

Exemplo: usa autenticação de identidade gerenciada atribuída pelo sistema

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "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 associada a uma identidade gerida atribuída pelo utilizador que representa o serviço para a autenticação de outros serviços Azure. Pode usar esta identidade gerida para a autenticação do SQL Managed Instance. O serviço designado 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. Siga os passos para Providenciar um administrador de Microsoft Entra para o seu Managed Instance.

2. Crie logins para a identidade gerenciada atribuída pelo usuário. No SQL Server Management Studio (SSMS), liga-te à tua instância gerida usando uma conta SQL Server que seja um sysadmin. No banco de dados mestre , execute o seguinte T-SQL:

   CREATE LOGIN [your_factory_or_workspace_ name] FROM EXTERNAL PROVIDER
   

3. Crie usuários de banco de dados contidos para a identidade gerenciada atribuída pelo usuário. Conecte-se ao banco de dados do qual você deseja copiar dados, execute o seguinte T-SQL:

   CREATE USER [your_factory_or_workspace_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 e.g. db_owner] ADD MEMBER [your_factory_or_workspace_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. Configurar uma instância gerida do SQL vinculada ao serviço.

Exemplo: usa autenticação de identidade gerenciada atribuída pelo usuário

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlMI",
        "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 para um serviço Azure SQL Managed Instance ligado quando se aplica a versão Legacy:

Para diferentes tipos de autenticação, consulte as seguintes seções sobre propriedades específicas e pré-requisitos, respectivamente:

• Autenticação SQL para a versão herdada
• Autenticação de service principal para a versão herdada
• Autenticação de identidade gerenciada atribuída pelo sistema para a versão herdada
• Autenticação de identidade gerenciada atribuída pelo usuário para a versão herdada

Autenticação SQL para a versão herdada

Para usar a autenticação SQL, especifique as propriedades genéricas descritas na seção anterior.

Autenticação do principal de serviço para a versão legada

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

Você também precisa seguir as etapas em Autenticação da entidade de serviço para conceder a permissão correspondente.

Autenticação de identidade gerenciada atribuída pelo sistema para a versão herdada

Para usar a autenticação de identidade gerida atribuída pelo sistema, siga a mesma etapa para a versão recomendada em Autenticação de identidade gerida atribuída pelo sistema.

Autenticação de identidade gerida pelo usuário para versões antigas

Para usar a autenticação de identidade gerenciada atribuída pelo usuário, siga a mesma etapa para a versão recomendada em Autenticação de identidade gerenciada atribuída pelo usuário.

Propriedades do conjunto de dados

Para obter uma lista completa de seções e propriedades disponíveis para uso 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 SQL Managed Instance.

Para copiar dados para e a partir da SQL Managed Instance, são suportadas as seguintes propriedades:

Exemplo

{
    "name": "AzureSqlMIDataset",
    "properties":
    {
        "type": "AzureSqlMITable",
        "linkedServiceName": {
            "referenceName": "<SQL Managed Instance 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 pela origem e destino do SQL Managed Instance.

SQL Managed Instance como fonte

Para copiar dados do SQL Managed Instance, são suportadas as seguintes propriedades na secção de origem da atividade de cópia:

Tenha em atenção os seguintes pontos:

• Se sqlReaderQuery for especificado para SqlMISource, a atividade de cópia executa esta consulta contra a fonte SQL Managed Instance 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 uma consulta SQL

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

Exemplo: Usar um procedimento armazenado

"activities":[
    {
        "name": "CopyFromAzureSqlMI",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Managed Instance input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlMISource",
                "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 Managed Instance como destino

Para copiar dados para o SQL Managed Instance, são suportadas as seguintes propriedades na secção de destino da atividade de cópia:

Exemplo 1: Acrescentar dados

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

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

Saiba mais detalhes em Invocar um procedimento armazenado de um receptor SQL MI.

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

Exemplo 3: Inserir ou atualizar dados

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

Cópia paralela a partir do SQL MI

O conector Azure SQL Managed Instance na atividade de cópia fornece particionamento de dados incorporado, permitindo a cópia de dados em paralelo. Você pode encontrar opções de particionamento de dados na guia Origem da atividade de cópia.

Quando você habilita a cópia particionada, a atividade de cópia executa consultas paralelas na fonte do SQL MI para carregar dados por partições. O grau paralelo é controlado pela configuração parallelCopies na atividade de cópia. Por exemplo, se você definir parallelCopies como quatro, o serviço gerará e executará simultaneamente quatro consultas com base na opção e nas configurações de partição especificadas, e cada consulta recuperará uma parte dos dados do seu SQL MI.

Sugere-se que habilite a cópia paralela com particionamento de dados, especialmente quando você carrega uma grande quantidade de dados do seu SQL MI. 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": "SqlMISource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

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

"source": {
    "type": "SqlMISource",
    "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 Managed Instance

Quando copia dados para o SQL Managed Instance, pode ser necessário um comportamento de escrita diferente:

• 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

Acrescentar dados é o comportamento padrão do conector sink do SQL Managed Instance. 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 Managed Instance 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 o SQL Managed Instance, 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.

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. No pipeline, defina a seção SQL MI sink na atividade de cópia da seguinte maneira:

   "sink": {
       "type": "SqlMISink",
       "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 Azure SQL Managed Instance. 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 Azure SQL Managed Instance. 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 origem do Azure SQL Managed Instance

Quando usa Azure SQL Managed Instance como tipo de fonte, o script de fluxo de dados associado é:

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

Transformação do lavatório

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

Exemplo de script de destino do Azure SQL Managed Instance

Quando usa Azure SQL Managed Instance 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) ~> SQLMISink

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

Mapeamento de tipos de dados para SQL Managed Instance

Quando os dados são copiados para e a partir da SQL Managed Instance usando a atividade de cópia, os seguintes mapeamentos são usados dos tipos de dados da SQL Managed Instance para os tipos de dados intermédios usados internamente no serviço. Para saber como a atividade de cópia é mapeada do esquema de origem e do tipo de dados para o coletor, consulte Mapeamentos de esquema e tipo de dados.

Utilização de Always Encrypted

Quando copiar dados de/para SQL Managed Instance com Sempre Encriptado, 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 ter acesso garantido 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_.

Atualize a versão do Azure SQL Managed Instance

Para atualizar a versão do Azure SQL Managed Instance, na página Edit linked service, selecione Recommended em Version e configure o serviço ligado consultando as propriedades do serviço ligado para a versão recomendada em Linked service properties for the recommended version.

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

A tabela abaixo mostra as diferenças entre o Azure SQL Managed Instance 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.