Riferimento agli script degli oggetti di utilità di SQL Server

Accedere al materiale di riferimento per lo script degli oggetti dell'utilità SQL Server, inclusi componenti, parametri e risoluzione dei problemi.

Informazioni generali

Per configurare il database SQL Server per l'acquisizione in Lakeflow Connect, lo script installa procedure e funzioni di utilità versionate. Le attività di installazione includono:

• Gestione delle autorizzazioni
• Configurazione del rilevamento delle modifiche
• Configurazione di Change Data Capture (CDC)
• Rilevamento della piattaforma
• DDL supporta la creazione di oggetti per il rilevamento delle modifiche dello schema

Informazioni sulla versione

• Versione attuale: 1.7
• Versione principale: 1
• Versione minore: 7
• Funzione Versione: lakeflowUtilityVersion()

Novità nella versione 1.7

Highlights

• Corregge un bug di perdita di dati nell'evoluzione dello schema. Nelle versioni precedenti, aggiungere una colonna a una tabella che contiene anche un'istanza di cattura preesistente (non Lakeflow) poteva ingerire la nuova colonna come NULL. Questo è stato risolto, insieme a diversi errori correlati nell'evoluzione dello schema (vedi Correzioni di bug).
• Gli oggetti di supporto DDL sono ora opzionali per il tracciamento delle modifiche. La tabella di audit DDL e il trigger non sono più necessari per l'evoluzione automatica dello schema e sono disattivati di default. Imposta @CreateDdlSupportingObjects = 1 solo lakeflowSetupChangeTracking se li vuoi ancora.
• Evoluzione degli schemi più affidabile. Le modifiche ai vincoli sono classificate in modo che quelle non interrotte, come le chiavi estranee, non inneschino più aggiornamenti completi inutili.

Correzioni di bug

• Ha corretto diversi ADD COLUMN fallimenti nell'evoluzione dello schema, incluse tabelle che utilizzano mascheramento dinamico dei dati, collazioni a fine e minuscola o binarie, e un ciclo di riinitializzazione o un'ingestione interrotta quando è presente un'istanza di cattura preesistente.
• Errori di collazione nei database corretti con una collation del server non predefinita.
• Sono stati corretti i trigger DDL che potevano fallire quando eseguiti da una sessione con opzioni non predefinite SET .
• Corretto lakeflowFixPermissions per concedere i permessi richiesti a scopo server dal database master.
• Corretto l'disinstallazione (CLEANUP modalità lasciando trigger DDL orfani che potevano bloccare ALTER TABLE.

Altre modifiche

• Aggiunto @AllowDisablePreExistingCaptureInstances a lakeflowSetupChangeDataCapture. Impostalo in modo 1 che Lakeflow Connect prenda il controllo e gestisca l'istanza di cattura CDC preesistente (non Lakeflow) di una tabella invece di lasciarla in posizione.

Componenti fondamentali

Functions

lakeflowDetectPlatform()

Rileva il tipo di piattaforma SQL Server.

Restituisce: 'AZURE_SQL_DATABASE', 'AZURE_SQL_MANAGED_INSTANCE', 'AMAZON_RDS', 'ON_PREMISES', o 'UNKNOWN'

lakeflowUtilityVersion()

Rileva la versione degli oggetti di utilità.

Restituisce: '1.7'

Procedure memorizzate

lakeflowFixPermissions

Concede le autorizzazioni necessarie agli utenti per le operazioni di inserimento.

Parametri:

@Tables opzioni parametro:

Che cosa fa:

• Concede SELECT le viste di sistema necessarie (sys.objects, sys.tables, sys.columnse così via)
• Concede EXECUTE nelle stored procedure di sistema (sp_tables, sp_columns_100 ecc.)
• Facoltativamente concede alle SELECT tabelle utente in base al @Tables parametro
• Gestisce le differenze specifiche della piattaforma (database SQL di Azure, Istanza gestita di Azure, Istanza RDS, On-premises)

lakeflowSetupChangeTracking

Consente il tracciamento delle modifiche a livello di database e tabella, con supporto opzionale DDL (opt-in).

Parametri:

@Tables opzioni parametro:

Che cosa fa:

• Abilita il rilevamento delle modifiche a livello di database, se non è già abilitato
• Quando @CreateDdlSupportingObjects = 1, crea una tabella di audit DDL versionata (lakeflowDdlAudit_1_7) e un trigger per catturare le modifiche dello schema
• Abilita CT nelle tabelle specificate (ignora le tabelle senza chiavi primarie)
• Concede VIEW CHANGE TRACKING le autorizzazioni all'utente specificato
• CLEANUP mode: rimuove gli oggetti di supporto DDL

Comportamenti importanti:

• Ignora automaticamente le tabelle senza chiavi primarie (è consigliabile usare CDC per queste tabelle)
• Individuazione intelligente con il 'ALL' parametro
• Idempotente: può essere eseguito più volte in modo sicuro

lakeflowSetupChangeDataCapture

Abilita CDC a livello di database e tabella con il supporto DDL e la gestione delle istanze di acquisizione.

Parametri:

@Tables opzioni parametro:

Che cosa fa:

• Abilita CDC a livello di database, se non è già abilitato
• Crea una tabella di rilevamento dell'istanza di acquisizione (lakeflowCaptureInstanceInfo_1_7)
• Crea procedure helper per la gestione delle istanze di acquisizione:
  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7
• Crea un ALTER TABLE trigger per la gestione automatica delle modifiche dello schema
• Abilita CDC nelle tabelle specificate
• Concede le autorizzazioni CDC necessarie all'utente specificato
• CLEANUP modalità: rimuove tutti gli oggetti di supporto DDL CDC

Comportamenti importanti:

• Funziona con tabelle con o senza chiavi primarie
• Gestisce automaticamente la rotazione dell'istanza di acquisizione nelle modifiche dello schema
• Idempotente: può essere eseguito più volte in modo sicuro

Supporto delle piattaforme

• SQL Server locale (EngineEdition 1-4)
• Database SQL di Azure (EngineEdition 5)
• Istanza gestita di SQL di Azure (EngineEdition 8)
• Amazon RDS per SQL Server (rilevato dal modello di nome del server)

Prerequisiti

• L'utente che esegue lo script deve essere membro del db_owner ruolo
• Per la configurazione CT: il rilevamento delle modifiche deve essere disponibile sulla piattaforma
• Per la configurazione di CDC: Change Data Capture deve essere disponibile nella piattaforma

Istruzioni per l'installazione

Scaricare ed eseguire lo script

1. Scaricare la versione più recente dello script:

   Scaricare utility_script.sql

2. Eseguire lo script:

   1. Aprire lo script scaricato in SQL Server Management Studio (SSMS), Azure Data Studio o nel client SQL preferito.
   2. Connettiti alla istanza di SQL Server.
   3. Verificare di essere connessi al database di destinazione in cui si desidera installare gli oggetti utilità.
   4. Esegui lo script.

3. Verificare l'installazione:

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

Alternativa: eseguire usando la riga di comando

Se si preferisce usare sqlcmd:

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

Esempio: Correzione delle autorizzazioni (solo sistema)

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

Esempio: Correzione delle autorizzazioni (con accesso alla tabella)

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

Esempi di configurazione del rilevamento delle modifiche

Solo a livello di database

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

Abilita in tutte le tabelle

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

Configurazione basata su schema

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

Tabelle specifiche

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

Crea oggetti di supporto DDL

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

Esempi: configurazione di CDC

Solo a livello di database

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

Abilita in tutte le tabelle

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

Tabelle specifiche

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

Acquisisci la proprietà di istanze di cattura preesistenti

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

Esempio: Approccio ibrido

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

Esempio: Pulizia

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

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

Oggetti di supporto DDL creati

Vengono creati gli oggetti di supporto DDL seguenti, a seconda che si usi il rilevamento delle modifiche o CDC.

Per il rilevamento delle modifiche

Per il Centro per la prevenzione e il controllo delle malattie (CDC)

Limitazioni del rilevamento delle modifiche

• Richiede chiavi primarie: le tabelle senza chiavi primarie non possono usare il rilevamento delle modifiche.
• Lo script ignora automaticamente le tabelle senza PKS e consiglia invece di usare CDC.

Comportamento specifico della piattaforma

• Database SQL di Azure: le stored procedure di sistema sono accessibili di default (non sono necessarie concessioni EXECUTE).
• Viste con ambito server: accesso limitato nel database SQL di Azure per viste come sys.change_tracking_databases.

Percorso di aggiornamento

Per aggiornare, riesegui lo script degli oggetti utilitari e poi riesegui le procedure di configurazione. Lo script ricrea le funzioni di utilità e rimuove solo gli oggetti con prefisso legacy replicantdi release precedenti. Lascia i tuoi oggetti di supporto DDL Lakeflow esistenti in posizione, così il trigger DDL attuale continua a catturare le modifiche dello schema finché non riesegui le procedure di configurazione. Per le istruzioni, vedi Passo 1: Installa o aggiorna oggetti utilitari.

Cosa accade durante un aggiornamento:

• Le funzioni di utilità e le procedure di configurazione definite dallo script vengono eliminate e ricreate.
• Gli oggetti legacy replicantcon prefisso -delle versioni precedenti sono stati rimossi.
• Gli oggetti di supporto Lakeflow DDL esistenti (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_*, e le procedure e trigger correlati) sono lasciati in posizione. Rieseguire le procedure di configurazione li fa passare alla nuova versione. Gli oggetti dell'istanza di cattura si spostano ogni volta che rilanci lakeflowSetupChangeDataCapture. La tabella di audit DDL e il trigger si spostano solo quando rilanci lakeflowSetupChangeTracking con @CreateDdlSupportingObjects = 1, perché sono opt-in.
• Il rilevamento delle modifiche e CDC rimangono abilitati nelle tabelle. L'aggiornamento non li disabilita.
• Le istanze di acquisizione CDC non sono interessate dallo script di aggiornamento.

Dopo aver eseguito lo script di aggiornamento, eseguire nuovamente le procedure di installazione per ricreare gli oggetti di supporto DDL per la nuova versione:

• lakeflowSetupChangeTracking: con @CreateDdlSupportingObjects = 1, trasporta la tabella di audit DDL e il trigger forward a lakeflowDdlAudit_1_7. Passa questo segnale se la tua installazione usa la cattura di cambio schema DDL (ha una lakeflowDdlAudit tabella), oppure se gli oggetti rimangono sulla versione precedente.
• lakeflowSetupChangeDataCapture: ricrea e le lakeflowCaptureInstanceInfo_1_7 procedure e i trigger correlati.

Entrambe le procedure sono idempotenti e sicure da eseguire nuovamente con i parametri di installazione originali.

Schema di controllo delle versioni: objectName_majorVersion_minorVersion. Gli oggetti correnti usano il _1_7 suffisso .

Procedure consigliate

• Esegui sempre come db_owner oppure come un utente con privilegi equivalenti.
• Eseguire prima il test sui database non di produzione.
• Usare l'approccio ibrido per una copertura completa.
• Eseguire lakeflowFixPermissions dopo l'installazione per garantire l'accesso utente appropriato.
• Prendere in considerazione i periodi di conservazione in base alla frequenza di acquisizione.

Risoluzione dei problemi

"L'utente che esegue questo script non è un membro del ruolo "db_owner"

Soluzione: eseguire come utente con db_owner ruolo

"Tracciamento delle modifiche non abilitato nel catalogo"

Soluzione: abilitare CT a livello di database o consentire alla routine di gestirla automaticamente

"Change Data Capture non è abilitato nel catalogo"

Soluzione: abilitare CDC a livello di database o consentire alla routine di gestirla automaticamente

Tabelle saltate a causa di chiavi primarie mancanti

Soluzione: usare lakeflowSetupChangeDataCapture invece per queste tabelle

Convalida dell'integrazione

Gli oggetti utilità seguenti vengono convalidati dal framework di convalida Java:

Note sulla migrazione

Se si esegue l'aggiornamento da versioni precedenti di oggetti di supporto DDL (era precedente agli oggetti utilitari):

• Lo script pulisce automaticamente gli oggetti legacy.
• Non è necessaria alcuna pulizia manuale.
• La versione 1.1 consolida tutte le funzionalità in procedure unificate.

Risorse aggiuntive

• Preparare SQL Server per l'inserimento usando lo script degli oggetti dell'utilità
• Configurare Microsoft SQL Server per l'inserimento in Azure Databricks
• Requisiti utente del database di Microsoft SQL Server
• Tenere traccia delle modifiche ai dati (SQL Server) nella documentazione di SQL Server
• Informazioni sul rilevamento delle modifiche (SQL Server) nella documentazione di SQL Server
• Cos'è la cattura dei dati di modifica (CDC)? nella documentazione di SQL Server