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 corrente: 1.5
• Versione principale: 1
• Versione secondaria: 5
• Funzione Versione: lakeflowUtilityVersion_1_5()

Novità della versione 1.5

• Correzione dell'errore del trigger di controllo DDL nel database SQL di Azure.
• Correzione della compatibilità con le versioni precedenti con gli oggetti di supporto DDL meno recenti.
• Correzione della perdita di dati durante le modifiche dello schema quando è presente un'istanza di acquisizione preesistente.

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_1_5()

Rileva la versione degli oggetti di utilità.

Restituisce: '1.5'

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

Abilita il rilevamento delle modifiche a livello di database e tabella con supporto DDL.

Parametri:

@Tables opzioni parametro:

Che cosa fa:

• Abilita il rilevamento delle modifiche a livello di database, se non è già abilitato
• Crea una tabella di controllo DDL versionata (lakeflowDdlAudit_1_5)
• Crea un trigger di controllo DDL per acquisire 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_5)
• Crea procedure helper per la gestione delle istanze di acquisizione:
  • lakeflowDisableOldCaptureInstance_1_5
  • lakeflowMergeCaptureInstances_1_5
  • lakeflowRefreshCaptureInstance_1_5
• 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 lo script: utility_script.sql

2. Eseguire lo script

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

3. Verificare l'installazione

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion_1_5() 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 lakeflow_utility_objects.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';

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

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 eseguire di nuovo l'aggiornamento, eseguire nuovamente lo script degli oggetti utilità. Lo script rimuove automaticamente tutti gli oggetti con versione precedenti prima di installare la nuova versione. Per istruzioni dettagliate, vedere Aggiornare gli oggetti dell'utilità.

Cosa accade durante un aggiornamento:

• Tutte le stored procedure e le funzioni lakeflow precedenti vengono eliminate e ricreate con il suffisso della nuova versione.
• Gli oggetti di supporto DDL delle versioni precedenti (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_*e le procedure e i trigger correlati) vengono rimossi.
• 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: ricrea lakeflowDdlAudit_1_5 e il trigger di controllo DDL.
• lakeflowSetupChangeDataCapture: ricrea e le lakeflowCaptureInstanceInfo_1_5 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_5 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