Referência de script de objetos utilitáriosSQL Server

Acesse o material de referência para o script de objetos utilitários SQL Server , incluindo componentes, parâmetros e solução de problemas.

Visão geral

O script instala utilitários versionados, procedimentos armazenados e funções para configurar seu banco de dados SQL Server para ingestão no LakeFlow Connect. As tarefas de configuração incluem:

• Gestão de permissões
• Alterar configuração de envio (CT)
• captura de dados de alterações (CDC) (CDC) setup
• Detecção de plataforma
• Suporte a DDL para criação de objetos para alteração de esquema

Informações da versão

• Versão atual: 1.7
• Versão principal: 1
• Versão Minor: 7
• Função de versão: lakeflowUtilityVersion()

Novidades na versão 1.7

Destaques

• Corrige um bug de perda de dados na evolução do esquema. Em versões anteriores, a adição de uma coluna a uma tabela que também possui uma instância de captura pré-existente (não LakeFlow) podia ingerir a nova coluna como NULL. Isso foi corrigido, juntamente com várias falhas relacionadas à evolução do esquema (consulte Correções de bugs).
• Objetos de suporte a DDL agora são opcionais para o acompanhamento de alterações. A tabela de auditoria DDL e o trigger não são mais necessários para a evolução do esquema automática e vêm desativados por default. Defina @CreateDdlSupportingObjects = 1 em lakeflowSetupChangeTracking apenas se você ainda os quiser.
• Evolução do esquema mais confiável. As alterações de restrições são classificadas para que as não quebrantes, como foreign keys, não mais trigger desnecessários full refreshes.

Correções de bugs

• Foram corrigidas várias falhas de evolução do esquema ADD COLUMN, incluindo tabelas que usam mascaramento de dados dinâmico, agrupamentos sensíveis a maiúsculas e minúsculas ou binários, e um loop de reinicialização ou ingestão interrompida quando uma instância de captura pré-existente está presente.
• Erros de conflito de collation corrigidos em bancos de dados com uma collation de servidor não default.
• Foram corrigidos triggers de DDL que podiam falhar quando executados a partir de uma sessão com opções de SET non-default.
• Corrigido lakeflowFixPermissions para conceder as permissões necessárias com escopo de servidor do banco de dados master.
• A desinstalação corrigida (modo CLEANUP) deixando triggers DDL órfãos que poderiam bloquear ALTER TABLE.

Outras alterações

• Adicionado @AllowDisablePreExistingCaptureInstances em lakeflowSetupChangeDataCapture. Defina-o como 1 para permitir que o Lakeflow Connect assuma o controle e gerencie a instância de captura de CDC pré-existente (não Lakeflow) de uma tabela, em vez de deixá-la no lugar.

componentes principais

Funções

lakeflowDetectPlatform()

Detecta o tipo de plataforma do SQL Server.

Retorna: 'AZURE_SQL_DATABASE', 'AZURE_SQL_MANAGED_INSTANCE', 'AMAZON_RDS', 'ON_PREMISES', ou 'UNKNOWN'

lakeflowUtilityVersion()

Detecta a versão dos objetos russos.

Devoluções: '1.7'

Procedimentos armazenados

lakeflowFixPermissions

Concede as permissões necessárias aos usuários para operações de ingestão.

Parâmetros:

Parâmetro	Descrição
@User (NVARCHAR(128))	Obrigatório. Nome de usuário ao qual conceder permissões
@Tables (NVARCHAR(MAX))	Opcional. Controla o escopo de permissões em nível de tabela.

Parâmetro	Descrição
@User (NVARCHAR(128))	Obrigatório. Nome de usuário ao qual conceder permissões
@Tables (NVARCHAR(MAX))	Opcional. Controla o escopo de permissões em nível de tabela.

@Tables opções de parâmetros:

Opção	Descrição
NULL	Conceder apenas permissões de nível de sistema (default)
'ALL'	Conceda permissões em todas as tabelas de usuários no banco de dados.
'SCHEMAS:Schema1,Schema2'	Conceder permissões em todas as tabelas nos esquemas especificados.
'Schema.Table1,Schema.Table2'	Conceder permissões em tabelas específicas
Suporte a curingas	Exemplo: 'Sales.*,HR.Employees'

Opção	Descrição
NULL	Conceder apenas permissões de nível de sistema (default)
'ALL'	Conceda permissões em todas as tabelas de usuários no banco de dados.
'SCHEMAS:Schema1,Schema2'	Conceder permissões em todas as tabelas nos esquemas especificados.
'Schema.Table1,Schema.Table2'	Conceder permissões em tabelas específicas
Suporte a curingas	Exemplo: 'Sales.*,HR.Employees'

O que faz:

• Concede SELECT na visão de sistema necessária (sys.objects, sys.tables, sys.columns, etc.)
• Concede EXECUTE em procedimentos armazenados do sistema (sp_tables, sp_columns_100, etc.)
• Opcionalmente, concede SELECT em tabelas de usuários com base no parâmetro @Tables .
• Lida com diferenças específicas da plataforma (Banco de Dados SQL Azure , instância gerenciada, RDS, local).

lakeflowSetupChangeTracking

Ativa o acompanhamento de alterações nos níveis de banco de dados e tabela, com suporte a DDL opcional (opcional).

Parâmetros:

Parâmetro	Descrição
@Tables (NVARCHAR(MAX))	Opcional. Tabelas para habilitar a TC em
@User (NVARCHAR(128))	Opcional. O usuário deverá conceder permissões a
@Retention (NVARCHAR(50))	Opcional. Período de retenção CT (default: '2 DAYS')
@Mode (NVARCHAR(10))	Opcional. 'INSTALL' (default) ou 'CLEANUP'
@CreateDdlSupportingObjects (BIT)	Opcional. Default 0. Defina como 1 para criar a tabela de auditoria DDL e o trigger para o acompanhamento de alterações de esquema (DDL). A validação da configuração espera que esse valor corresponda à configuração do pipeline. Se os valores não coincidirem, a validação da configuração relatará uma falha.

Parâmetro	Descrição
@Tables (NVARCHAR(MAX))	Opcional. Tabelas para habilitar a TC em
@User (NVARCHAR(128))	Opcional. O usuário deverá conceder permissões a
@Retention (NVARCHAR(50))	Opcional. Período de retenção CT (default: '2 DAYS')
@Mode (NVARCHAR(10))	Opcional. 'INSTALL' (default) ou 'CLEANUP'
@CreateDdlSupportingObjects (BIT)	Opcional. Default 0. Defina como 1 para criar a tabela de auditoria DDL e o trigger para o acompanhamento de alterações de esquema (DDL). A validação da configuração espera que esse valor corresponda à configuração do pipeline. Se os valores não coincidirem, a validação da configuração relatará uma falha.

@Tables opções de parâmetros:

Opção	Descrição
NULL	Configurar apenas CT em nível de banco de dados, sem habilitação de tabela (também cria objetos de suporte DDL quando @CreateDdlSupportingObjects = 1)
'ALL'	Habilite o CT em todas as tabelas de usuários com chave primária.
'SCHEMAS:Schema1,Schema2'	Ativar CT em tabelas nos esquemas especificados
'Schema.Table1,Schema.Table2'	Habilitar CT em tabelas específicas
Suporte a curingas	Exemplo: 'Sales.*,HR.Employees'

Opção	Descrição
NULL	Configurar apenas CT em nível de banco de dados, sem habilitação de tabela (também cria objetos de suporte DDL quando @CreateDdlSupportingObjects = 1)
'ALL'	Habilite o CT em todas as tabelas de usuários com chave primária.
'SCHEMAS:Schema1,Schema2'	Ativar CT em tabelas nos esquemas especificados
'Schema.Table1,Schema.Table2'	Habilitar CT em tabelas específicas
Suporte a curingas	Exemplo: 'Sales.*,HR.Employees'

O que faz:

• Permite alterar o acompanhamento no nível do banco de dados, caso ainda não esteja ativado.
• Quando @CreateDdlSupportingObjects = 1, cria uma tabela de auditoria DDL com controle de versão (lakeflowDdlAudit_1_7) e um trigger para capturar alterações de esquema
• Habilita o CT em tabelas específicas (ignora tabelas sem chave primária).
• Concede permissões VIEW CHANGE TRACKING ao usuário especificado.
• CLEANUP modo: Remove objetos de suporte DDL

Comportamentos importantes:

• Ignora automaticamente tabelas sem chave primária (CDC é recomendado para esses casos).
• Descoberta inteligente com o parâmetro 'ALL'
• Idempotente: Seguro para execução múltiplas vezes

lakeflowSetupChangeDataCapture

Habilita a captura de erros de captura (CDC) nos níveis de banco de dados e tabela, com suporte a DDL e gerenciamento de instâncias de captura.

Parâmetros:

Parâmetro	Descrição
@Tables (NVARCHAR(MAX))	Opcional. Tabelas para habilitar o CDC em
@User (NVARCHAR(128))	Opcional. O usuário deverá conceder permissões a
@Mode (NVARCHAR(10))	Opcional. 'INSTALL' (default) ou 'CLEANUP'
@AllowDisablePreExistingCaptureInstances (BIT)	Opcional. 0 (default) deixa instâncias de captura pré-existentes e que não são do Lakeflow inalteradas durante o tratamento de alteração de esquema. Defina como 1 para permitir que o Lakeflow Connect assuma o controle e desabilite instâncias de captura pré-existentes.

Parâmetro	Descrição
@Tables (NVARCHAR(MAX))	Opcional. Tabelas para habilitar o CDC em
@User (NVARCHAR(128))	Opcional. O usuário deverá conceder permissões a
@Mode (NVARCHAR(10))	Opcional. 'INSTALL' (default) ou 'CLEANUP'
@AllowDisablePreExistingCaptureInstances (BIT)	Opcional. 0 (default) deixa instâncias de captura pré-existentes e que não são do Lakeflow inalteradas durante o tratamento de alteração de esquema. Defina como 1 para permitir que o Lakeflow Connect assuma o controle e desabilite instâncias de captura pré-existentes.

@Tables opções de parâmetros:

Opção	Descrição
NULL	Configure o suporte a CDC e DDL somente no nível do banco de dados.
'ALL'	Ative o CDC em todas as tabelas de usuários.
'SCHEMAS:Schema1,Schema2'	Habilitar CDC em tabelas nos esquemas especificados
'Schema.Table1,Schema.Table2'	Habilitar o CDC em tabelas específicas

Opção	Descrição
NULL	Configure o suporte a CDC e DDL somente no nível do banco de dados.
'ALL'	Ative o CDC em todas as tabelas de usuários.
'SCHEMAS:Schema1,Schema2'	Habilitar CDC em tabelas nos esquemas especificados
'Schema.Table1,Schema.Table2'	Habilitar o CDC em tabelas específicas

O que faz:

• Habilita o CDC no nível do banco de dados, caso ainda não esteja habilitado.

• Cria uma tabela de acompanhamento da instância de captura (lakeflowCaptureInstanceInfo_1_7)

• Cria procedimentos auxiliares para o gerenciamento de instâncias de captura:

  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7

• Cria um gatilho ALTER TABLE para o tratamento automático de alterações de esquema.

• Habilita o CDC em tabelas específicas.

• Concede as permissões necessárias do CDC ao usuário especificado.

• CLEANUP modo: Remove todos os objetos de suporte DDL do CDC

Comportamentos importantes:

• Funciona com tabelas com ou sem chave primária.
• Gerencia automaticamente a rotação da instância de captura em caso de alterações de esquema.
• Idempotente: Seguro para execução múltiplas vezes

Suporte da plataforma

• SQL Server local (EngineEdition 1-4)
• Banco de Dados SQL do Azure (EngineEdition 5)
• Instância de gerenciamento Azure SQL (EngineEdition 8)
• Amazon RDS para SQL Server (detectado pelo padrão do nome do servidor)

Pré-requisitos

• O usuário que executa o script deve ser membro da função db_owner .
• Para configuração de TC: A opção Alterar acompanhamento deve estar disponível na plataforma.
• Para configuração CDC : a captura de dados de alterações (CDC) deve estar disponível na plataforma

Instruções de instalação

baixe e execute o script

1. Faça o download da versão mais recente do script:

Download utility_script.sql

2. Execute o script:

   1. Abra o script de downloads no SQL Server Management Studio (SSMS), Azure Data Studio ou no seu cliente SQL preferido.
   2. Conecte-se à sua instância do SQL Server.
   3. Confirme se você está conectado ao banco de dados de destino onde deseja instalar os objetos utilitários.
   4. execução do roteiro.

3. Verificar instalação:

   SQL

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;

Alternativa: execução usando a linha de comando

Se preferir usar sqlcmd:

Bash

sqlcmd -S YourServerName -d YourDatabase -E -i utility_script.sql

nota

Substitua YourServerName e YourDatabase pelos nomes reais do seu servidor e banco de dados. Use -U username -P password em vez de -E se não estiver usando autenticação do Windows.

Exemplo: Corrigir permissões (somente do sistema)

SQL

-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser';

Exemplo: Corrigir permissões (com acesso à tabela)

SQL

-- Grant system permissions plus access to all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'ALL';

-- Grant permissions for specific schemas
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'SCHEMAS:Sales,HR,Production';

-- Grant permissions for specific tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'Sales.Orders,HR.Employees';

Exemplos: Alterar configuração de envio

Somente em nível de banco de dados

SQL

-- Setup CT infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = NULL,
    @User = 'myuser';

Ativar em todas as tabelas

SQL

-- Enable CT on all user tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

Configuração baseada em esquema

SQL

-- Enable CT on all tables in specific schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'myuser',
    @Retention = '3 DAYS';

Tabelas específicas

SQL

-- Enable CT on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'dbo.Table1,Sales.Orders,HR.Employees',
    @User = 'myuser';

Criar objetos de suporte a DDL

SQL

-- Enable CT and create the DDL audit table and trigger for schema-change capture
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser',
    @CreateDdlSupportingObjects = 1;

Exemplos: Configuração do CDC

Somente em nível de banco de dados

SQL

-- Setup CDC infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = NULL,
    @User = 'myuser';

Ativar em todas as tabelas

SQL

-- Enable CDC on all user tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Tabelas específicas

SQL

-- Enable CDC on specific tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'dbo.Table1,Sales.Orders',
    @User = 'myuser';

Assumir o controle de instâncias de captura pré-existentes

SQL

-- Allow Lakeflow to disable pre-existing (non-Lakeflow) capture instances
-- during schema change handling
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser',
    @AllowDisablePreExistingCaptureInstances = 1;

Exemplo: Abordagem híbrida

SQL

-- Step 1: Enable CT on tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

-- Step 2: Enable CDC on remaining tables (without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Exemplo: Limpeza

SQL

-- Remove CT DDL support objects
EXEC dbo.lakeflowSetupChangeTracking
    @Mode = 'CLEANUP';

-- Remove CDC DDL support objects
EXEC dbo.lakeflowSetupChangeDataCapture
    @Mode = 'CLEANUP';

Objetos de suporte DDL criados

Os seguintes objetos de suporte DDL são criados, dependendo se você usa acompanhamento de alterações ou CDC.

Para alterar a reprodução

Tipo de objeto	Nome	Descrição
Tabela	lakeflowDdlAudit_1_7	História de alteração de DDL das lojas
Trigger	lakeflowDdlAuditTrigger_1_7	Captura eventos ALTER TABLE

Tipo de objeto	Nome	Descrição
Tabela	lakeflowDdlAudit_1_7	História de alteração de DDL das lojas
Trigger	lakeflowDdlAuditTrigger_1_7	Captura eventos ALTER TABLE

Para o CDC

Tipo de objeto	Nome	Descrição
Tabela	lakeflowCaptureInstanceInfo_1_7	As faixas capturam instâncias
Procedimento	lakeflowDisableOldCaptureInstance_1_7	Remove a instância de captura antiga
Procedimento	lakeflowMergeCaptureInstances_1_7	mesclar dados entre instâncias
Procedimento	lakeflowRefreshCaptureInstance_1_7	Cria uma nova instância de captura.
Trigger	lakeflowAlterTableTrigger_1_7	Lida com alterações de esquema

Tipo de objeto	Nome	Descrição
Tabela	lakeflowCaptureInstanceInfo_1_7	As faixas capturam instâncias
Procedimento	lakeflowDisableOldCaptureInstance_1_7	Remove a instância de captura antiga
Procedimento	lakeflowMergeCaptureInstances_1_7	mesclar dados entre instâncias
Procedimento	lakeflowRefreshCaptureInstance_1_7	Cria uma nova instância de captura.
Trigger	lakeflowAlterTableTrigger_1_7	Lida com alterações de esquema

Alterar limitações de envio

• Requer chave primária: Tabelas sem chave primária não podem usar acompanhamento de alterações.
• O script ignora automaticamente tabelas sem chaves primárias e recomenda o uso do CDC em vez disso.

Comportamento específico da plataforma

• Banco de Dados SQL Azure : os procedimentos armazenados do sistema são acessíveis por default (nenhuma concessão EXECUTE é necessária).
• Exibição com escopo de servidor: Acesso limitado no Banco de Dados SQL Azure para exibição como sys.change_tracking_databases.

Caminho de atualização

Para atualizar, execute novamente o script de objetos de utilidades e, em seguida, execute novamente os procedimentos de configuração. O script recria as funções de utilidades e remove apenas os objetos legados prefixados com replicantde liberações anteriores. Ele mantém os objetos de suporte Lakeflow DDL existentes no lugar, para que o trigger DDL atual continue capturando alterações de esquema até que você execute novamente os procedimentos de configuração. Para obter instruções, consulte o passo 1: Instalar ou atualizar objetos de utilidades.

O que acontece durante uma atualização:

• As funções de utilidade e os procedimentos de configuração definidos pelo script são descartados e recriados.
• Objetos prefixados com replicantherdados de versões anteriores foram removidos.
• Os objetos de suporte ao DDL do Lakeflow existentes (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_* e procedimentos e triggers relacionados) permanecem no lugar. A execução novamente dos procedimentos de configuração faz a transição deles para a nova versão. Os objetos da instância de captura se movem sempre que você executa novamente lakeflowSetupChangeDataCapture. A tabela de auditoria DDL e o trigger se movem apenas quando você executa novamente lakeflowSetupChangeTracking com @CreateDdlSupportingObjects = 1, pois são opcionais.
• As opções de acompanhamento e CDC permanecem ativadas em suas mesas. A atualização não os desativa.
• As instâncias de captura do CDC não são afetadas pelo script de atualização.

Após executar o script de atualização, execute novamente os procedimentos de configuração para recriar objetos de suporte DDL para a nova versão:

• lakeflowSetupChangeTracking: com @CreateDdlSupportingObjects = 1, transporta a tabela de auditoria DDL e o trigger para lakeflowDdlAudit_1_7. Passe esta flag se a sua instalação usar a captura de alterações de esquema DDL (ela possui uma tabela lakeflowDdlAudit) ou se os objetos permanecerem na versão antiga.
• lakeflowSetupChangeDataCapture: Recria lakeflowCaptureInstanceInfo_1_7 e os procedimentos e gatilhos relacionados.

Ambos os procedimentos são idempotentes e podem ser reexecutados com segurança, utilizando os parâmetros de configuração originais.

nota

Para reverter para uma versão anterior do script de objetos de utilidades, entre em contato com o suporte do Databricks.

Esquema de versionamento: objectName_majorVersion_minorVersion. Os objetos atuais usam o sufixo _1_7 .

Melhores práticas

• Sempre execução como db_owner ou um usuário com privilégios equivalentes.
• Teste primeiro em bancos de dados que não sejam de produção.
• Utilize a abordagem híbrida para uma cobertura abrangente.
• execução lakeflowFixPermissions após a configuração para garantir o acesso adequado do usuário.
• Considere os períodos de retenção com base na frequência de ingestão.

Solução de problemas

"O usuário que executa este script não é membro da função 'db_owner'"

soluções : Execute como um usuário com função db_owner

"Alterar conforme necessário não está habilitado no catálogo"

Soluções : Habilite o CT no nível do banco de dados ou deixe o procedimento lidar com ele automaticamente.

"A captura de dados de alterações (CDC) não está habilitada no catálogo"

Soluções : Habilite CDC no nível do banco de dados ou deixe o procedimento lidar com isso automaticamente.

"Tabelas ignoradas devido à ausência da chave primária"

soluções : Use lakeflowSetupChangeDataCapture para estas tabelas em vez disso

Integração de validação

Os seguintes objetos utilitários são validados pela estrutura de validação Java :

Objeto	Descrição
SqlServerUtilityObjectsSetupValidator	Valida a instalação de objetos de utilidades
SqlServerChangeDataManagementSetupValidator	Valida a configuração CT/CDC
SqlServerDdlSupportObjectsSetupValidator	Valida objetos de suporte DDL
SqlServerPermissionsSetupValidator	Valida permissões

Objeto	Descrição
SqlServerUtilityObjectsSetupValidator	Valida a instalação de objetos de utilidades
SqlServerChangeDataManagementSetupValidator	Valida a configuração CT/CDC
SqlServerDdlSupportObjectsSetupValidator	Valida objetos de suporte DDL
SqlServerPermissionsSetupValidator	Valida permissões

Notas sobre migração

Se estiver atualizando de versões antigas de objetos de suporte DDL (era pré-objetos utilitários):

• O script limpa automaticamente os objetos legados.
• Não é necessária limpeza manual.
• A versão 1.1 consolida todas as funcionalidades em procedimentos unificados.

Recursos adicionais

• Prepare SQL Server para ingestão usando o script de objetos utilitários.
• Configure o Microsoft SQL Server para ingestão de dados no Databricks.
• Requisitos do usuário do banco de dados Microsoft SQL Server
• Rastrear alterações de dados (SQL Server) na documentação do SQL Server
• Sobre a alteração de acompanhamento (SQL Server) na documentação SQL Server
• O que é captura de dados de alterações (CDC) (CDC)? na documentação SQL Server