SQL Server voorbereiden voor opname met behulp van het script voor hulpprogrammaobjecten

Voltooi de installatietaken van de SQL Server-database voor opname in Azure Databricks met behulp van Lakeflow Connect.

Requirements

• De gebruiker die het script uitvoert, moet lid zijn van de db_owner rol. Deze rol is alleen vereist voor het uitvoeren van het installatiescript, niet voor de gebruiker die data binnenhaalt.

Als u een gebruiker aan de db_owner rol wilt toevoegen, gebruikt u een van de volgende methoden:

• Moderne SQL Server (2012+): Gebruik ALTER ROLE

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

• Verouderde SQL Server- of beperkte omgevingen: gebruiken sp_addrolemember

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

• Voor CT-installatie: Wijzigingen bijhouden moet beschikbaar zijn op het platform.

• Voor de installatie van CDC: Change Data Capture moet op het platform beschikbaar zijn.

Stap 1: Installeer of upgrade hulpobjecten

Met deze stap worden de opgeslagen procedures en functies van het hulpprogramma geïnstalleerd die nodig zijn voor het instellen van SQL Server. Zie de scriptreferentie voor SQL Server-hulpprogrammaobjecten voor meer informatie over wat er wordt geïnstalleerd.

Hetzelfde script behandelt zowel een eerste installatie als een upgrade ten opzichte van een eerdere versie. Volg de onderstaande genummerde stappen. Stappen gemarkeerd als (alleen upgraden) zijn alleen van toepassing als een eerdere versie van de hulpobjecten al is geïnstalleerd. Sla ze over voor een eerste installatie.

1. Download het script:

   Utility_script.sql downloaden

2. Open het script in SQL Server Management Studio (SSMS), Azure Data Studio of uw favoriete SQL-client.

3. Maak verbinding met uw SQL Server-exemplaar als gebruiker met de db_owner rol.

4. Zorg ervoor dat u bent verbonden met de doeldatabase.

5. (Alleen upgrade) Pauze van invoer: stop de gateway (gateway-gebaseerde pijplijn) of pauzeer de pijplijn (geïntegreerde CDC).

6. Voer het script uit. Bij een upgrade maakt het script de hulpfuncties opnieuw aan en verwijdert alleen legacy replicant-prefix objecten. Het laat je bestaande DDL-ondersteuningsobjecten op hun plaats, zodat de huidige DDL-trigger schemawijzigingen blijft vastleggen totdat je de setupprocedures opnieuw uitvoert in de volgende stap.

7. (Alleen upgrade) Voer de installatieprocedure opnieuw uit voor elke capture-methode die je gebruikt: lakeflowSetupChangeTracking als je change tracking gebruikt, en lakeflowSetupChangeDataCapture als je CDC gebruikt. Dit brengt je objecten door naar de nieuwe versie en herstelt de rechten van de invoergebruiker. Geef alleen @User door. U hebt @Tables niet nodig, omdat wijzigingen bijhouden en CDC ingeschakeld blijven op uw tabellen tijdens een upgrade.

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

   Sla alle niet-standaard opties opnieuw door zodat de upgrade je configuratie behoudt:

   • Wijzigingen bijhouden: Standaard worden de DDL-audittabel en -trigger niet aangemaakt, dus hoeft u hier bij de meeste upgrades niets extra's door te geven. Als je installatie DDL-schemawijzigingsregistratie gebruikt (er is een tabel lakeflowDdlAudit aanwezig), geef @CreateDdlSupportingObjects = 1 dan door aan lakeflowSetupChangeTracking om die objecten naar de nieuwe versie te migreren. Installaties van vóór deze optie maakten ze automatisch aan, dus voeg de vlag toe als je op DDL-capture vertrouwt, ook al heb je het nooit ingesteld.
   • CDC: Als je lakeflowSetupChangeDataCapture op @AllowDisablePreExistingCaptureInstances = 1 gebruikt, voeg het opnieuw toe.

8. Installatie controleren:

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

9. (Alleen bij upgrade) Hervat de gegevensinname: hervat de gateway of hervat de pijplijn.

Stap 2: Wijzigingen bijhouden inschakelen (voor tabellen met primaire sleutels)

Wijzigingen bijhouden is een lichtgewicht mechanisme waarmee wijzigingen in tabelrijen worden bijgehouden. Deze stap maakt CT mogelijk op databaseniveau op gespecificeerde tabellen. Om ook schemawijzigingen (DDL) vast te leggen, geef je @CreateDdlSupportingObjects = 1 door om de DDL-ondersteuningsobjecten aan te maken. Dit is standaard opt-in en uit. Zie de lakeflowSetupChangeTracking voor meer informatie.

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

Alternatieve opties:

• Voor alle tabellen met primaire sleutels: @Tables = 'ALL'
• Voor specifieke schema's: @Tables = 'SCHEMAS:Sales,HR,Production'
• Alleen voor het instellen op databaseniveau (geen tabel-inschakeling): @Tables = NULL

Stap 3: Wijzigingsgegevens vastleggen inschakelen (voor tabellen zonder primaire sleutels)

CDC legt de activiteit invoegen, bijwerken en verwijderen vast en is met name handig voor tabellen zonder primaire sleutels. Met deze stap schakelt u CDC in op databaseniveau, stelt u het beheer van capture-instanties in en maakt u triggers voor automatische verwerking van schemawijzigingen. Zie de lakeflowSetupChangeDataCapture voor meer informatie.

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

Alternatieve opties:

• Voor alle tabellen: @Tables = 'ALL'
• Voor specifieke schema's: @Tables = 'SCHEMAS:Sales,HR'
• Alleen voor installatie op databaseniveau: @Tables = NULL

Om Lakeflow Connect een reeds bestaande (niet-Lakeflow-)CDC-opname-instantie van een tabel te laten overnemen in plaats van deze ongewijzigd te laten, stel @AllowDisablePreExistingCaptureInstances = 1 in.

Exemplaarbeheer vastleggen

Lakeflow Connect maakt gebruik van een naamconventie op basis van voorvoegsels voor het beheren van CDC-capture-exemplaren zonder dat dit van invloed is op bestaande capture-exemplaren die zijn gemaakt door andere systemen of processen.

Naamgeving van capture-instanties in Lakeflow

Lakeflow Connect maakt en beheert capture-exemplaren met behulp van het volgende naamgevingspatroon:

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

Lakeflow Connect beheert alleen capture-exemplaren die overeenkomen met dit naamgevingspatroon. Bestaande capture-exemplaren met verschillende namen blijven behouden en blijven ongewijzigd door Lakeflow-bewerkingen.

Vereisten voor instantiesslot vastleggen

MET SQL Server kunnen maximaal 2 exemplaren per tabel worden vastgelegd. Opdat Lakeflow Connect met CDC werkt:

• Ten minste één van de twee opname-instantiesloten moet beschikbaar zijn voor Lakeflow om zijn met lakeflow_ voorvoegsel voorziene instantie te maken.
• Als beide slots al worden bezet door niet-Lakeflow capture-instanties, kan Lakeflow Connect geen eigen capture-instantie maken en beheren. Hoewel Lakeflow kan lezen uit een bestaand capture-exemplaar, kunnen er geen volledige vernieuwings- of schemaontwikkelingsbewerkingen worden uitgevoerd.

Co-existentie met andere CDC-consumenten

Lakeflow Connect kan veilig naast andere CDC-consumenten in dezelfde tabel worden gebruikt:

• Bestaande capture-exemplaren blijven behouden tijdens alle Lakeflow-bewerkingen (bijvoorbeeld volledige vernieuwing en schemaontwikkeling).
• Lakeflow verwijdert alleen zijn eigen instantie met een lakeflow_ voorvoegsel en maakt deze opnieuw, wanneer dat nodig is.
• Andere systemen die CDC-gegevens van niet-Lakeflow-capture-exemplaren gebruiken, blijven werken zonder onderbreking.

Bewerkingen waarmee Lakeflow-capture-exemplaren opnieuw worden gemaakt:

De volgende bewerkingen leiden ertoe dat Lakeflow zijn lakeflow_ voorvoegsels voor capture-instanties (maar niet andere) verwijdert en opnieuw creëert.

• Bewerkingen voor volledig vernieuwen
• Kolommen toevoegen aan tabellen (ADD COLUMN)

Voorbeeldscenario:

Als een tabel een bestaand capture-exemplaar heeft met de naam my_app_cdc:

1. Lakeflow Connect genereert lakeflow_schema_table_1.
2. Beide opname-exemplaren bestaan naast elkaar zonder problemen.
3. Wanneer Lakeflow een volledige vernieuwing of schema-evolutie uitvoert, wordt lakeflow_schema_table_1 alleen opnieuw aangemaakt.
4. Het my_app_cdc exemplaar blijft ongewijzigd en blijft werken voor het andere systeem.

Stap 4: Aanvullende machtigingen verlenen (indien nodig)

Deze stap verleent de nodige systeem- en tabelmachtigingen aan de opnamegebruiker. Hoewel stap 2 en 3 CT- en CDC-specifieke machtigingen verleent, zorgt deze stap ervoor dat de gebruiker alle vereiste SELECT machtigingen heeft. Zie de lakeflowFixPermissions voor meer informatie.

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

Alternatieve opties:

• Voor alle tabellen: @Tables = 'ALL'
• Alleen systeemmachtigingen: @Tables = NULL
• Specifieke schema's: @Tables = 'SCHEMAS:Sales,HR'

Stap 5: De installatie controleren

Voer de volgende query's uit om te controleren of wijzigingen bijhouden en CDC correct zijn geconfigureerd in uw database en tabellen:

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

Hulpprogrammaobjecten upgraden

Upgraden gebruikt hetzelfde script als bij een eerste installatie. Volg stap 1: Installeer of upgrade hulpprogramma-objecten, inclusief de stappen gemarkeerd (alleen upgraden), die het pauzeren van invoer, het opnieuw uitvoeren van de installatieprocedures en het hervatten van invoer omvatten.

Voorbeeld: Hybride benadering

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

Algemene scenario's

Scenario 1: Alleen wijzigingen bijhouden (specifieke schema's)

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: alleen CDC (specifieke tabellen)

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: Hybride benadering (CT voor sommige schema's, CDC voor specifieke tabellen)

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

Aanvullende bronnen

• Scriptreferentie voor SQL Server-hulpprogrammaobjecten
• Microsoft SQL Server configureren voor opname in Azure Databricks
• Gebruikersvereisten voor Microsoft SQL Server-database
• Gegevenswijzigingen bijhouden (SQL Server) in de SQL Server-documentatie