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: Hulpprogrammaobjecten installeren

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.

1. Download het script: utility_script.sql

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. Voer het script uit.

6. Installatie controleren:

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

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

Wijzigingen bijhouden is een lichtgewicht mechanisme waarmee wijzigingen in tabelrijen worden bijgehouden. Met deze stap kunt u CT op databaseniveau op opgegeven tabellen instellen en DDL-ondersteuningsobjecten instellen om schemawijzigingen af te handelen. 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

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;

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