Preparar o SQL Server para ingestão usando o script de objetos do utilitário

Conclua as tarefas de instalação do banco de dados do SQL Server para ingerir no Azure Databricks usando o Lakeflow Connect.

Requirements

• O usuário que executa o script deve ser um membro da db_owner função.
• Para configuração de CT: o controle de alterações deve estar disponível na plataforma.
• Para a instalação do CDC: a captura de dados de alteração deve estar disponível na plataforma.

Etapa 1: instalar objetos utilitários

Esta etapa instala os procedimentos armazenados do utilitário e as funções necessárias para a instalação do SQL Server. Para obter detalhes sobre o que é instalado, consulte a referência de script dos objetos de utilitários do SQL Server.

1. Baixe o script: utility_script.sql

2. Abra o script no SSMS (SQL Server Management Studio), no Azure Data Studio ou no cliente SQL preferido.

3. Conecte-se à instância do SQL Server como um usuário com a db_owner função.

4. Verifique se você está conectado ao banco de dados de destino.

5. Executar o script.

6. Verifique a instalação:

   SELECT dbo.lakeflowUtilityVersion_1_1() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;
   

Etapa 2: Habilitar o controle de alterações (para tabelas com chaves primárias)

O controle de alterações é um mecanismo leve que rastreia as alterações nas linhas da tabela. Esta etapa habilita a CT no nível do banco de dados em tabelas especificadas e configura objetos de suporte DDL para lidar com alterações de esquema. Para obter detalhes, consulte lakeflowSetupChangeTracking na referência de script de objetos de utilitário do SQL Server.

-- Enable change tracking on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'Sales.Orders,Production.Products,HR.Employees',
    @User = 'your_ingestion_user',
    @Retention = '2 DAYS';

Opções alternativas:

• Para todas as tabelas com chaves primárias: @Tables = 'ALL'
• Para esquemas específicos: @Tables = 'SCHEMAS:Sales,HR,Production'
• Somente para configuração no nível do banco de dados (sem habilitação de tabela): @Tables = NULL

Etapa 3: Habilitar a captura de dados de alteração (para tabelas sem chaves primárias)

O CDC captura a atividade de inserção, atualização e exclusão e é particularmente útil para tabelas sem chaves primárias. Esta etapa habilita o CDC no nível do banco de dados, configura o gerenciamento de instância de captura e cria gatilhos para tratamento automático de alterações de esquema. Para obter detalhes, consulte lakeflowSetupChangeDataCapture na referência de script de objetos de utilitário do SQL Server.

-- Enable CDC on specific tables (particularly those without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'your_ingestion_user';

Opções alternativas:

• Para todas as tabelas: @Tables = 'ALL'
• Para esquemas específicos: @Tables = 'SCHEMAS:Sales,HR'
• Somente para configuração no nível do banco de dados: @Tables = NULL

Gerenciamento de instância de captura

O Lakeflow Connect usa uma convenção de nomenclatura baseada em prefixo para gerenciar instâncias de captura CDC sem afetar instâncias de captura pré-existentes criadas por outros sistemas ou processos.

Nomenclatura da instância de captura do Lakeflow

O Lakeflow Connect cria e gerencia instâncias de captura usando o seguinte padrão de nomenclatura:

• lakeflow_<schema>_<table>_1
• lakeflow_<schema>_<table>_2

O Lakeflow Connect gerencia apenas instâncias de captura que correspondem a esse padrão de nomenclatura. Instâncias de captura pré-existentes com nomes diferentes são preservadas e permanecem não afetadas pelas operações do Lakeflow.

Requisitos de slot de instância de captura

O SQL Server permite um máximo de 2 instâncias de captura por tabela. Para o Lakeflow Connect funcionar com o CDC:

• Pelo menos um dos dois slots de instância de captura deve estar disponível para que o Lakeflow possa criar sua lakeflow_ instância com prefixo.
• Se ambos os slots já estiverem ocupados por instâncias de captura não Lakeflow, o Lakeflow Connect não poderá criar e gerenciar sua própria instância de captura. Embora o Lakeflow possa ler de uma instância de captura pré-existente, ele não pode executar operações completas de atualização ou evolução de esquema.

Coexistência com outros consumidores CDC

O Lakeflow Connect pode coexistir com segurança com outros consumidores CDC na mesma tabela:

• As instâncias de captura pré-existentes são preservadas durante todas as operações do Lakeflow (por exemplo, atualização completa e evolução do esquema).
• Lakeflow só descarta e recria suas próprias lakeflow_ instâncias prefixadas quando necessário.
• Outros sistemas que consomem dados CDC de instâncias de captura que não são Lakeflow continuam funcionando sem interrupções.

Operações que recriam instâncias de captura do Lakeflow:

As operações a seguir fazem com que o Lakeflow libere e recrie suas instâncias de captura com prefixo lakeflow_ (mas não outras):

• Operações de atualização completas
• Adicionando colunas a tabelas (ADD COLUMN)

Cenário de exemplo:

Se uma tabela tiver uma instância de captura pré-existente chamada my_app_cdc:

1. O Lakeflow Connect cria lakeflow_schema_table_1.
2. Ambas as instâncias de captura coexistem com segurança.
3. Quando o Lakeflow executa uma atualização completa ou evolução de esquema, ele recria apenas lakeflow_schema_table_1.
4. A my_app_cdc instância permanece intocada e continua funcionando para o outro sistema.

Etapa 4: Conceder permissões adicionais (se necessário)

Esta etapa concede as permissões necessárias de sistema e de nível de tabela para o usuário responsável pela ingestão. Embora as etapas 2 e 3 concedam permissões específicas de CT e CDC, essa etapa garante que o usuário tenha todas as permissões necessárias SELECT . Para obter detalhes, consulte lakeflowFixPermissions na referência de script de objetos de utilitário do SQL Server.

-- Grant system-level and table-level permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'Sales.Orders,Production.Products,HR.Employees';

Opções alternativas:

• Para todas as tabelas: @Tables = 'ALL'
• Somente permissões do sistema: @Tables = NULL
• Esquemas específicos: @Tables = 'SCHEMAS:Sales,HR'

Etapa 5: Verificar a configuração

Execute as seguintes consultas para confirmar se o controle de alterações e o CDC estão configurados corretamente em seu banco de dados e tabelas:

-- Check Change Tracking status
SELECT
    d.name AS DatabaseName,
    ctd.is_auto_cleanup_on,
    ctd.retention_period,
    ctd.retention_period_units_desc
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON ctd.database_id = d.database_id
WHERE d.name = DB_NAME();

-- Check tables with Change Tracking enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.begin_version,
    ct.cleanup_version
FROM sys.change_tracking_tables ct
INNER JOIN sys.tables t ON ct.object_id = t.object_id;

-- Check CDC status
SELECT
    DB_NAME() AS DatabaseName,
    is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

-- Check tables with CDC enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.capture_instance,
    ct.start_lsn,
    ct.create_date
FROM cdc.change_tables ct
INNER JOIN sys.tables t ON ct.source_object_id = t.object_id;

Exemplo: abordagem híbrida

-- Step 1: Already completed (script installed)

-- Step 2 & 3: Enable both CT and CDC
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'lakeflow_user';

-- Step 4: Grant all necessary permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Cenários comuns

Cenário 1: somente controle de alterações (esquemas específicos)

EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,Production',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'SCHEMAS:Sales,Production';

Cenário 2: somente CDC (tabelas específicas)

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords',
    @User = 'lakeflow_user';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords';

Cenário 3: abordagem híbrida (CT para alguns esquemas, CDC para tabelas específicas)

-- Enable CT on transactional schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'lakeflow_user',
    @Retention = '3 DAYS';

-- Enable CDC on specific staging tables without primary keys
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'lakeflow_user';

-- Grant permissions on all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Recursos adicionais

• Referência de script de objetos do utilitário do SQL Server
• Configurar o Microsoft SQL Server para ingestão no Azure Databricks
• Requisitos de usuário do banco de dados do Microsoft SQL Server
• Acompanhar alterações de dados (SQL Server) na documentação do SQL Server