Przygotowanie serwera SQL do importowania przy użyciu skryptu obiektów użytkowych

Wykonaj zadania konfiguracji bazy danych SQL Server w celu importowania danych do usługi Azure Databricks przy użyciu narzędzia Lakeflow Connect.

Requirements

• Użytkownik, który uruchamia skrypt, musi być członkiem db_owner roli.
• W przypadku konfiguracji CT: Śledzenie zmian musi być dostępne na platformie.
• W przypadku konfiguracji usługi CDC: przechwytywanie zmian danych musi być dostępne na platformie.

Krok 1. Instalowanie obiektów narzędziowych

W tym kroku instalowane są wymagane do instalacji programu SQL Server procedury składowane i funkcje narzędzia. Aby uzyskać szczegółowe informacje o instalowanych elementach, zobacz Dokumentacja skryptu obiektów narzędziowych programu SQL Server.

1. Pobierz skrypt: utility_script.sql

2. Otwórz skrypt w programie SQL Server Management Studio (SSMS), Azure Data Studio lub preferowanym kliencie SQL.

3. Połącz się z wystąpieniem programu SQL Server jako użytkownik z rolą db_owner.

4. Upewnij się, że masz połączenie z docelową bazą danych.

5. Uruchom skrypt.

6. Sprawdź instalację:

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

Krok 2. Włączanie śledzenia zmian (dla tabel z kluczami podstawowymi)

Śledzenie zmian to lekki mechanizm, który śledzi zmiany w wierszach tabeli. Ten krok umożliwia śledzenie zmian na poziomie bazy danych w wyszczególnionych tabelach i konfiguruje obiekty obsługi Data Definition Language (DDL) do obsługi zmian schematu. Aby uzyskać szczegółowe informacje, zobacz lakeflowSetupChangeTracking dokumentację skryptu obiektów narzędziowych programu 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';

Opcje alternatywne:

• Dla wszystkich tabel z kluczami podstawowymi: @Tables = 'ALL'
• W przypadku określonych schematów: @Tables = 'SCHEMAS:Sales,HR,Production'
• Tylko w przypadku konfiguracji na poziomie bazy danych (brak włączania tabeli): @Tables = NULL

Krok 3. Włączanie przechwytywania zmian danych (w przypadku tabel bez kluczy podstawowych)

Usługa CDC przechwytuje działanie wstawiania, aktualizowania i usuwania oraz jest szczególnie przydatne w przypadku tabel bez kluczy podstawowych. Ten krok umożliwia Change Data Capture na poziomie bazy danych, konfigurowanie zarządzania instancjami przechwytywania i tworzenie wyzwalaczy na potrzeby automatycznej obsługi zmian schematu. Aby uzyskać szczegółowe informacje, zobacz lakeflowSetupChangeDataCapture dokumentację skryptu obiektów narzędziowych programu SQL Server.

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

Opcje alternatywne:

• Dla wszystkich tabel: @Tables = 'ALL'
• W przypadku określonych schematów: @Tables = 'SCHEMAS:Sales,HR'
• Tylko w przypadku konfiguracji na poziomie bazy danych: @Tables = NULL

Zarządzanie wystąpieniami przechwytywania

Program Lakeflow Connect używa konwencji nazewnictwa opartej na prefiksach do zarządzania wystąpieniami przechwytywania danych CDC, nie wpływając jednocześnie na istniejące wystąpienia przechwytywania utworzone przez inne systemy lub procesy.

Nazewnictwo instancji przechwytywania Lakeflow

Program Lakeflow Connect tworzy wystąpienia przechwytywania i zarządza nimi przy użyciu następującego wzorca nazewnictwa:

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

Program Lakeflow Connect zarządza tylko wystąpieniami przechwytywania, które pasują do tego wzorca nazewnictwa. Istniejące wystąpienia przechwytywania o różnych nazwach są zachowywane i pozostają nienaruszone przez operacje lakeflow.

Wymagania dotyczące miejsca wystąpienia przechwytywania

Program SQL Server umożliwia maksymalnie 2 wystąpienia przechwytywania na tabelę. Aby program Lakeflow Connect współpracował z usługą CDC:

• Co najmniej jedno z dwóch miejsc dla instancji przechwytywania musi być dostępne, aby Lakeflow mogło utworzyć swoją instancję z prefiksem lakeflow_.
• Jeśli oba miejsca są już zajęte przez wystąpienia przechwytywania nienależące do usługi Lakeflow, program Lakeflow Connect nie może utworzyć własnego wystąpienia przechwytywania i zarządzać nim. Chociaż Lakeflow może odczytywać z istniejącego wystąpienia przechwytywania, nie może wykonywać operacji pełnego odświeżania ani ewolucji schematu.

Współistnienie z innymi użytkownikami usługi CDC

Program Lakeflow Connect może bezpiecznie współistnieć z innymi użytkownikami usługi CDC w tej samej tabeli:

• Istniejące wystąpienia przechwytywania są zachowywane podczas wszystkich operacji Lakeflow (na przykład pełnego odświeżania i ewolucji schematu).
• Usługa Lakeflow usuwa i tworzy ponownie tylko te wystąpienia, które posiadają prefiksy lakeflow_, gdy jest to konieczne.
• Inne systemy korzystające z danych CDC z instancji przechwytujących innych niż Lakeflow nadal działają bez przerwy.

Operacje, które odtwarzają wystąpienia przechwytywania w usłudze Lakeflow:

Następujące operacje powodują, że Lakeflow porzuca i ponownie tworzy wystąpienia przechwytywania z prefiksem lakeflow_ (ale nie inne):

• Operacje pełnego odświeżania
• Dodawanie kolumn do tabel (ADD COLUMN)

Przykładowy scenariusz:

Jeśli tabela ma wstępnie istniejące wystąpienie przechwytywania o nazwie my_app_cdc:

1. Program Lakeflow Connect tworzy lakeflow_schema_table_1.
2. Oba wystąpienia przechwytują współistnieją bezpiecznie.
3. Gdy usługa Lakeflow wykonuje pełne odświeżanie lub ewolucję schematu, odtwarza tylko lakeflow_schema_table_1.
4. Wystąpienie my_app_cdc pozostaje nietknięte i nadal działa dla drugiego systemu.

Krok 4. Udzielanie dodatkowych uprawnień (w razie potrzeby)

Ten krok udziela niezbędnych uprawnień systemowych oraz uprawnień na poziomie tabel dla użytkownika importującego. Chociaż kroki 2 i 3 udzielają uprawnień specyficznych dla CT i CDC, ten krok gwarantuje, że użytkownik posiada wszystkie wymagane SELECT uprawnienia. Aby uzyskać szczegółowe informacje, zobacz lakeflowFixPermissions dokumentację skryptu obiektów narzędziowych programu SQL Server.

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

Opcje alternatywne:

• Dla wszystkich tabel: @Tables = 'ALL'
• Tylko uprawnienia systemowe: @Tables = NULL
• Określone schematy: @Tables = 'SCHEMAS:Sales,HR'

Krok 5. Weryfikowanie konfiguracji

Uruchom następujące zapytania, aby potwierdzić, że śledzenie zmian i usługa CDC są prawidłowo skonfigurowane w bazie danych i tabelach:

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

Przykład: Podejście hybrydowe

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

Typowe scenariusze

Scenariusz 1. Śledzenie zmian (określone schematy)

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

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

Scenariusz 2: Tylko CDC (określone tabele)

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

Scenariusz 3. Podejście hybrydowe (CT dla niektórych schematów, CDC dla określonych tabel)

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

Dodatkowe zasoby

• Dokumentacja skryptu obiektów narzędziowych programu SQL Server
• Skonfiguruj Microsoft SQL Server dla potrzeby przetwarzania w Azure Databricks
• Wymagania użytkownika bazy danych programu Microsoft SQL Server
• Śledzenie zmian danych (SQL Server) w dokumentacji programu SQL Server