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. Ta rola jest wymagana tylko do uruchamiania skryptu konfiguracji i nie dotyczy użytkownika przetwarzającego dane.

Aby dodać użytkownika do db_owner roli, użyj jednej z następujących metod:

  • Nowoczesny program SQL Server (2012+): Użyj ALTER ROLE

    USE [your_database];
    ALTER ROLE db_owner ADD MEMBER [your_setup_user];
    GO
    
  • Starsze środowiska programu SQL Server lub środowiska z ograniczeniami: użyj sp_addrolemember

    USE [your_database];
    EXEC sp_addrolemember 'db_owner', 'your_setup_user';
    GO
    
  • 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: Zainstaluj lub ulepsz obiekty użytkowe

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.

Ten sam skrypt obsługuje zarówno pierwszą instalację, jak i aktualizację poprzedniej wersji. Postępuj zgodnie z ponumerowanymi krokami poniżej. Kroki oznaczone (tylko Aktualizacja) mają zastosowanie tylko wtedy, gdy poprzednia wersja obiektów narzędzi jest już zainstalowana. Pomiń je przy pierwszym montażu.

Ważna

(Tylko w przypadku aktualizacji) Przed uruchomieniem skryptu zatrzymaj bramę danych (potok oparty na bramie) lub wstrzymaj potok (w przypadku zintegrowanego CDC). Wstrzymanie pozwala uniknąć natychmiastowego niepowodzenia zmiany schematu i konieczności ponowienia próby podczas krótkiego etapu przełączenia.

  1. Pobierz skrypt:

    Pobierz 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. (Dotyczy tylko uaktualnienia) Wstrzymaj ingestię danych: zatrzymaj bramę (potok oparty na bramie) lub wstrzymaj potok (zintegrowane CDC).

  6. Uruchom skrypt. Podczas aktualizacji skrypt ponownie tworzy funkcje narzędziowe i usuwa tylko obiekty ze starszym prefiksem replicant. Zostawia istniejące obiekty wsparcia DDL na miejscu, więc obecny wyzwalacz DDL nadal rejestruje zmiany schematu, dopóki nie uruchomisz procedur konfiguracyjnych ponownie w kolejnym kroku.

  7. (Tylko aktualizacja) Ponownie uruchom procedurę konfiguracji dla każdej metody przechwytywania: lakeflowSetupChangeTracking jeśli używasz śledzenia zmian, a lakeflowSetupChangeDataCapture jeśli używasz CDC. To migruje Twoje obiekty do nowej wersji i przywraca uprawnienia użytkownika importu. Przekaż tylko @User. Nie musisz używać @Tables, ponieważ śledzenie zmian i CDC pozostają włączone w tabelach podczas uaktualnienia.

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

    Ponownie podaj wszystkie opcje inne niż domyślne, aby aktualizacja zachowała Twoją konfigurację:

    • Śledzenie zmian: Domyślnie tabela audytu DDL i wyzwalacz nie są tworzone, więc w większości aktualizacji nie trzeba tu niczego dodatkowo podawać. Jeśli Twoja instalacja korzysta z przechwytywania zmian schematu DDL (ma tabelę lakeflowDdlAudit), podaj @CreateDdlSupportingObjects = 1 w lakeflowSetupChangeTracking, aby przenieść te obiekty do nowej wersji. Instalacje utworzone, zanim ta opcja została wprowadzona, tworzyły je automatycznie, więc uwzględnij tę flagę, jeśli korzystasz z przechwytywania DDL, nawet jeśli nigdy jej nie ustawiono.
    • CDC: Jeśli używasz @AllowDisablePreExistingCaptureInstances = 1 na lakeflowSetupChangeDataCapture, dodaj je ponownie.
  8. Sprawdź instalację:

    SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
    SELECT dbo.lakeflowDetectPlatform() AS Platform;
    
  9. (Tylko aktualizacja) Wznowienie pobierania: wznowienie bramy lub wznowienie pipeline.

Uwaga / Notatka

Rola db_owner jest wymagana tylko dla użytkownika uruchamiającego ten skrypt instalacyjny. Użytkownik pozyskiwania (określony w parametrze @User w kolejnych krokach) wymaga tylko określonych uprawnień przyznanych przez procedury konfiguracji. Aby uzyskać szczegółowe informacje, zobacz Wymagania użytkownika bazy danych programu Microsoft SQL Server .

Uwaga / Notatka

(Tylko aktualizacja) Unikaj wprowadzania zmian schematu w tabelach śledzonych podczas okna aktualizacji. Przełączenie odbywa się w ramach transakcji, więc współbieżna zmiana schematu nie zostanie utracona, ale może zostać na krótko zablokowana albo szybko zakończyć się niepowodzeniem i wymagać ponawiania prób do czasu zakończenia przełączenia.

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 CT na poziomie bazy danych na określonych tabelach. Aby również przechwytywać zmiany schematu (DDL), przekaż @CreateDdlSupportingObjects = 1, aby utworzyć obiekty obsługi DDL. Jest to opcjonalne i domyślnie wyłączone. 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

Aby umożliwić usłudze Lakeflow Connect przejęcie istniejącej instancji przechwytywania CDC dla tabeli (innej niż Lakeflow), zamiast pozostawić ją bez zmian, ustaw @AllowDisablePreExistingCaptureInstances = 1.

Uwaga / Notatka

Możesz użyć śledzenia zmian lub CDC, albo użyć obu tych metod. Usługa Databricks rekomenduje używanie śledzenia zmian dla tabel z kluczami podstawowymi (krok 2) i CDC dla tabel bez kluczy podstawowych (krok 3) do pełnego pokrycia.

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.

Uwaga / Notatka

W wersjach skryptów narzędzi starszych niż 1.4 program Lakeflow Connect używał prefiksu New_ dla wystąpień przechwytywania (na przykład New_schema_table_1). W przypadku uaktualniania z wcześniejszej wersji, uruchom zaktualizowany skrypt narzędziowy, aby dostosować się do konwencji nazewnictwa lakeflow_. Skrypt automatycznie obsługuje wsteczną kompatybilność z istniejącymi instancjami przechwytywania z prefiksem New_ podczas przejścia.

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.

Wskazówka

Jeśli oba gniazda przechwytywania są zajęte, użyj śledzenia zmian lub usuń jedno z istniejących gniazd przechwytywania, jeśli nie jest już potrzebne.

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'

Uwaga / Notatka

Procedury konfiguracji opisane w krokach 2 i 3 automatycznie udzielają niezbędnych uprawnień CT i CDC, ale może być konieczne uruchomienie tej procedury, aby udzielić dodatkowych uprawnień na poziomie SELECT tabeli lub jeśli uprawnienia zostały cofnięte.

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;

Uaktualnianie obiektów narzędziowych

Aktualizacja używa tego samego skryptu co przy pierwszej instalacji. Postępuj zgodnie z krokiem 1: Zainstaluj lub zaktualizuj obiekty narzędziowe, w tym kroki oznaczone (tylko aktualizacja), które obejmują wstrzymanie pobierania, ponowne uruchomienie procedur konfiguracji oraz wznowienie pobierania.

Uwaga / Notatka

Aby przywrócić poprzednią wersję skryptu obiektów narzędzi, skontaktuj się z pomocą techniczną Databricks.

Przykład: Podejście hybrydowe

Uwaga / Notatka

W tym przykładzie użyto 'ALL' do włączenia CT i CDC we wszystkich tabelach dla uproszczenia. W przypadku użycia w środowisku produkcyjnym należy wziąć pod uwagę typowe scenariusze na tej stronie, aby ukierunkować określone schematy lub tabele.

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