Preparare SQL Server per l'inserimento usando lo script degli oggetti dell'utilità

Completare le attività di configurazione del database di SQL Server da inserire in Azure Databricks usando Lakeflow Connect.

Requisiti

• L'utente che esegue lo script deve essere un membro del db_owner ruolo. Questo ruolo è necessario solo per l'esecuzione dello script di installazione, non per l'utente di inserimento.

Per aggiungere un utente al db_owner ruolo, usare uno dei metodi seguenti:

• Modern SQL Server (2012+):Use ALTER ROLE

  USE [your_database];
  ALTER ROLE db_owner ADD MEMBER [your_setup_user];
  GO
  

• Ambienti legacy di SQL Server o con restrizioni: usare sp_addrolemember

  USE [your_database];
  EXEC sp_addrolemember 'db_owner', 'your_setup_user';
  GO
  

• 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.

Passo 1: Installare o aggiornare gli oggetti utility

Questo passaggio installa le stored procedure di servizio e le funzioni necessarie per l'installazione di SQL Server. Per informazioni dettagliate su cosa viene installato, vedere Informazioni di riferimento sullo script degli oggetti dell'utilità SQL Server.

Lo stesso script gestisce sia una prima installazione sia un aggiornamento rispetto a una versione precedente. Segui i passaggi numerati qui sotto. I passaggi contrassegnati (solo Aggiornamento) si applicano solo se una versione precedente degli oggetti utility è già installata. Ignorali durante la prima installazione.

1. Scaricare lo script:

   Scaricare utility_script.sql

2. Aprire lo script in SQL Server Management Studio (SSMS), Azure Data Studio o nel client SQL preferito.

3. Collegati all'istanza di SQL Server come utente con ruolo db_owner.

4. Assicurarsi di essere connessi al database di destinazione.

5. (Solo per l'aggiornamento) Sospendere l'ingestione: arrestare il gateway (pipeline basata su gateway) oppure mettere in pausa la pipeline (CDC integrato).

6. Esegui lo script. Durante un aggiornamento, lo script ricrea le funzioni di utilità e rimuove solo gli oggetti legacy replicantcon prefisso -. Lascia i tuoi oggetti di supporto DDL esistenti in posizione, così il trigger DDL attuale continua a catturare le modifiche dello schema finché non riesegui le procedure di configurazione nel passaggio successivo.

7. (Solo aggiornamento) Riesegui la procedura di configurazione per ogni metodo di cattura che usi: lakeflowSetupChangeTracking se usi il tracciamento delle modifiche e lakeflowSetupChangeDataCapture se usi CDC. Questo porta i tuoi oggetti avanti alla nuova versione e ripristina i permessi dell'utente di ingestione. Passa solo @User. Non è necessario @Tables, perché il monitoraggio delle modifiche e il CDC rimangono abilitati sulle tue tabelle tramite un aggiornamento.

   -- If you use change tracking:
   EXEC dbo.lakeflowSetupChangeTracking
       @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally
   
   -- If you use CDC:
   EXEC dbo.lakeflowSetupChangeDataCapture
       @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally
   

   Passa di nuovo tutte le opzioni non predefinite così l'aggiornamento preserva la tua configurazione:

   • Tracciamento delle modifiche: Per impostazione predefinita, la tabella di audit DDL e il trigger non vengono creati, quindi nella maggior parte degli aggiornamenti non è necessario specificare nulla di aggiuntivo. Se la tua installazione utilizza l'acquisizione delle modifiche dello schema DDL (include una tabella lakeflowDdlAudit), passa @CreateDdlSupportingObjects = 1 su lakeflowSetupChangeTracking per spostare tali oggetti alla nuova versione. Le installazioni effettuate prima che questa opzione esistesse li creavano automaticamente, quindi includi il flag se fai affidamento su DDL capture anche se non l'hai mai impostato.
   • CDC: Se usi @AllowDisablePreExistingCaptureInstances = 1 su lakeflowSetupChangeDataCapture, riincludilo di nuovo.

8. Verificare l'installazione:

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

9. (Solo per l'aggiornamento) Riprendi l'ingestione: riavvia il gateway o riprendi la pipeline.

Passaggio 2: Abilitare il rilevamento delle modifiche (per le tabelle con chiavi primarie)

Il rilevamento delle modifiche è un meccanismo leggero che tiene traccia delle modifiche apportate alle righe della tabella. Questo passaggio abilita la CT a livello di database su tabelle specificate. Per acquisire anche le modifiche allo schema (DDL), passa @CreateDdlSupportingObjects = 1 per creare gli oggetti di supporto per il DDL. Questo è opto per via opzionale e disattivato di default. Per ulteriori dettagli, vedere lakeflowSetupChangeTracking nel documento di riferimento dello script sugli oggetti dell'utilità 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';

Opzioni alternative:

• Per tutte le tabelle con chiavi primarie: @Tables = 'ALL'
• Per schemi specifici: @Tables = 'SCHEMAS:Sales,HR,Production'
• Solo per la configurazione a livello di database (nessuna abilitazione di tabella): @Tables = NULL

Passaggio 3: Abilitare Change Data Capture (per le tabelle senza chiavi primarie)

CDC acquisisce l'attività di inserimento, aggiornamento ed eliminazione ed è particolarmente utile per le tabelle senza chiavi primarie. Questo passaggio abilita CDC a livello di database, configura la gestione delle istanze di acquisizione e crea trigger per la gestione automatica delle modifiche dello schema. Per ulteriori dettagli, vedere lakeflowSetupChangeDataCapture nel documento di riferimento dello script sugli oggetti dell'utilità SQL Server.

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

Opzioni alternative:

• Per tutte le tabelle: @Tables = 'ALL'
• Per schemi specifici: @Tables = 'SCHEMAS:Sales,HR'
• Solo per la configurazione a livello di database: @Tables = NULL

Per consentire a Lakeflow Connect di assumere il controllo dell'istanza di cattura CDC preesistente (non Lakeflow) di una tabella invece di lasciarla invariata, imposta @AllowDisablePreExistingCaptureInstances = 1.

Registrare la gestione delle istanze

Lakeflow Connect usa una convenzione di denominazione basata su prefisso per gestire le istanze di acquisizione CDC senza influire sulle istanze di acquisizione preesistenti create da altri sistemi o processi.

Configurazione dell'istanza di acquisizione Lakeflow

Lakeflow Connect crea e gestisce le istanze di acquisizione usando il modello di denominazione seguente:

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

Lakeflow Connect gestisce solo le istanze di acquisizione che corrispondono a questo modello di denominazione. Le istanze di acquisizione preesistenti con nomi diversi vengono mantenute e rimangono invariate dalle operazioni Lakeflow.

Acquisizione dei requisiti dello slot dell'istanza

SQL Server consente un massimo di 2 istanze di acquisizione per tabella. Per l'uso di Lakeflow Connect con CDC:

• Per consentire a Lakeflow di creare un'istanza con prefisso lakeflow_, almeno uno dei due slot dell'istanza di acquisizione deve essere disponibile.
• Se entrambi gli slot sono già occupati da istanze di acquisizione non Lakeflow, Lakeflow Connect non può creare e gestire la propria istanza di acquisizione. Anche se Lakeflow può leggere da un'istanza di acquisizione preesistente, non può eseguire operazioni complete di aggiornamento o evoluzione dello schema.

Coesistenza con altri consumatori CDC

Lakeflow Connect può coesistere in modo sicuro con altri utenti CDC sulla stessa tabella.

• Le istanze di acquisizione preesistenti vengono mantenute durante tutte le operazioni Lakeflow, ad esempio l'aggiornamento completo e l'evoluzione dello schema.
• Lakeflow elimina e ricrea solo le proprie lakeflow_ istanze con prefisso quando necessario.
• Altri sistemi che usano dati CDC da istanze di acquisizione non Lakeflow continuano a funzionare senza interruzioni.

Operazioni che ricreano le istanze di acquisizione di Lakeflow:

Le seguenti operazioni causano la rimozione e la ricreazione da parte di Lakeflow delle sue istanze di acquisizione con prefisso lakeflow_ (ma non di altre):

• Operazioni di aggiornamento completo
• Aggiunta di colonne alle tabelle (ADD COLUMN)

Scenario di esempio:

Se una tabella ha un'istanza di acquisizione preesistente denominata my_app_cdc:

1. Lakeflow Connect crea lakeflow_schema_table_1.
2. Entrambe le istanze di acquisizione coesistono in modo sicuro.
3. Quando Lakeflow esegue un aggiornamento completo o un'evoluzione dello schema, ricrea solo lakeflow_schema_table_1.
4. L'istanza my_app_cdc rimane invariata e continua a funzionare per l'altro sistema.

Passaggio 4: Concedere autorizzazioni aggiuntive (se necessario)

Questo passaggio concede le autorizzazioni necessarie a livello di sistema e di tabella per l'utente di inserimento. Mentre i passaggi 2 e 3 concedono autorizzazioni specifiche di CT e CDC, questo passaggio garantisce che l'utente disponga di tutte le autorizzazioni necessarie SELECT . Per ulteriori dettagli, vedere lakeflowFixPermissions nel documento di riferimento dello script sugli oggetti dell'utilità SQL Server.

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

Opzioni alternative:

• Per tutte le tabelle: @Tables = 'ALL'
• Solo autorizzazioni di sistema: @Tables = NULL
• Schemi specifici: @Tables = 'SCHEMAS:Sales,HR'

Passaggio 5: Verificare la configurazione

Eseguire le query seguenti per verificare che il rilevamento delle modifiche e CDC siano configurati correttamente nel database e nelle tabelle:

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

Aggiornare gli oggetti utilità

L'aggiornamento utilizza lo stesso script di una prima installazione. Segui il Passo 1: Installa o aggiorna gli oggetti utility, inclusi i passaggi segnati (solo Aggiornamento), che comprendono la pausa dell'ingestione, la riesecuzione delle procedure di configurazione e la ripresa dell'ingestione.

Esempio: Approccio ibrido

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

Scenari comuni

Scenario 1: Solo rilevamento modifiche (schemi specifici)

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

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

Scenario 2: solo CDC (tabelle specifiche)

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

Scenario 3: Approccio ibrido (CT per alcuni schemi, CDC per tabelle specifiche)

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

Risorse aggiuntive

• Informazioni di riferimento sugli oggetti dell'utilità SQL Server
• 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