SQL Server für die Aufnahme vorbereiten mit Verwendung des Hilfsobjekt-Skripts

Führen Sie die SQL Server-Datenbankeinrichtungsaufgaben aus, um sie mithilfe von Lakeflow Connect in Azure Databricks aufzunehmen.

Anforderungen

• Der Benutzer, der das Skript ausführt, muss Mitglied der db_owner Rolle sein. Diese Rolle ist nur für die Ausführung des Setup-Skripts erforderlich, nicht für den Benutzer für die Datenübernahme.

Verwenden Sie eine der folgenden Methoden, um der db_owner Rolle einen Benutzer hinzuzufügen:

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

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

• Ältere SQL Server- oder eingeschränkte Umgebungen: Verwenden sp_addrolemember

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

• Für CT-Einrichtung: Die Änderungsnachverfolgung muss auf der Plattform verfügbar sein.

• Für das CDC-Setup: Die Erfassung geänderter Daten muss auf der Plattform verfügbar sein.

Schritt 1: Installieren oder aktualisieren Sie Utility-Objekte

In diesem Schritt werden die gespeicherten Prozeduren und Funktionen des Hilfsprogramms installiert, die für die SQL Server-Einrichtung erforderlich sind. Ausführliche Informationen dazu, was installiert wird, finden Sie in der Skriptreferenz zu SQL Server-Hilfsobjekten.

Das gleiche Skript behandelt sowohl eine Erstinstallation als auch ein Upgrade gegenüber einer vorherigen Version. Folgen Sie den untenstehenden nummerierten Schritten. Schritte mit der Markierung (nur Upgrade) gelten nur, wenn bereits eine vorherige Version der Utility-Objekte installiert ist. Lass sie bei einer Erstinstallation weg.

1. Laden Sie das Skript herunter:

   Utility_script.sql herunterladen

2. Öffnen Sie das Skript in SQL Server Management Studio (SSMS), Azure Data Studio oder Ihrem bevorzugten SQL-Client.

3. Stellen Sie eine Verbindung mit Ihrer SQL Server-Instanz als Benutzer mit der db_owner Rolle her.

4. Stellen Sie sicher, dass Sie mit der Zieldatenbank verbunden sind.

5. (Nur beim Upgrade) Datenaufnahme pausieren: das Gateway stoppen (gatewaybasierte Pipeline) oder die Pipeline pausieren (integriertes CDC).

6. Führen Sie das Skript aus. Bei einem Upgrade erzeugt das Skript die Utility-Funktionen neu und entfernt nur noch legacy-präfixierte replicantObjekte. Es lässt deine bestehenden DDL-Supportobjekte bestehen, sodass der aktuelle DDL-Trigger Schemaänderungen weiterhin erfasst, bis du die Setup-Prozeduren im nächsten Schritt erneut ausführst.

7. (Nur bei Upgrade) Führe den Setupvorgang für jede von dir verwendete Erfassungsmethode erneut aus: lakeflowSetupChangeTracking wenn du Change Tracking verwendest, und lakeflowSetupChangeDataCapture wenn du CDC verwendest. Dies übernimmt Ihre Objekte in die neue Version und stellt die Berechtigungen des Erfassungsbenutzers wieder her. Geben Sie nur @User weiter. Du benötigst @Tables nicht, denn Change Tracking und CDC bleiben bei einem Upgrade für deine Tabellen aktiviert.

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

   Übergebe alle Nicht-Standard-Optionen erneut, damit das Upgrade deine Konfiguration bewahrt:

   • Änderungsverfolgung: Standardmäßig werden die DDL-Audittabelle und der Trigger nicht erstellt, sodass bei den meisten Upgrades hier nichts zusätzlich angegeben werden muss. Wenn Ihre Installation die DDL-Schemaänderungserfassung verwendet (sie hat eine lakeflowDdlAudit-Tabelle), geben Sie @CreateDdlSupportingObjects = 1 bei lakeflowSetupChangeTracking an, um diese Objekte in die neue Version zu übernehmen. Installationen aus der Zeit vor der Existenz dieser Option haben sie automatisch erstellt, also füge die Flagge hinzu, wenn du auf DDL-Erfassung angewiesen bist, auch wenn du sie nie eingestellt hast.
   • CDC: Wenn Sie lakeflowSetupChangeDataCapture auf @AllowDisablePreExistingCaptureInstances = 1 verwenden, fügen Sie es erneut hinzu.

8. Installation überprüfen:

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

9. (Nur bei Upgrade) Datenaufnahme fortsetzen: Gateway oder Pipeline fortsetzen.

Schritt 2: Aktivieren der Änderungsnachverfolgung (für Tabellen mit Primärschlüsseln)

Die Änderungsnachverfolgung ist ein einfacher Mechanismus, mit dem Änderungen an Tabellenzeilen nachverfolgt werden. Dieser Schritt ermöglicht CT auf Datenbankebene auf festgelegten Tabellen. Um auch Schemaänderungen (DDL) zu erfassen, übergeben Sie @CreateDdlSupportingObjects = 1, um die Unterstützungsobjekte für DDL zu erstellen. Dies ist eine Opt-in-Funktion und standardmäßig deaktiviert. Ausführliche Informationen finden Sie in lakeflowSetupChangeTracking der Skriptreferenz zu SQL Server-Hilfsobjekten.

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

Alternative Optionen:

• Für alle Tabellen mit Primärschlüsseln: @Tables = 'ALL'
• Für bestimmte Schemas: @Tables = 'SCHEMAS:Sales,HR,Production'
• Nur für setup auf Datenbankebene (keine Tabellenaktivierung): @Tables = NULL

Schritt 3: Aktivieren der Änderungsdatenerfassung (für Tabellen ohne Primärschlüssel)

CDC erfasst Einfüge-, Aktualisierungs- und Löschaktivitäten und ist besonders nützlich für Tabellen ohne Primärschlüssel. Dieser Schritt ermöglicht CDC auf Datenbankebene, richtet die Verwaltung der Aufzeichnungsinstanz ein und erstellt Trigger für die automatisierte Verarbeitung von Schemaänderungen. Ausführliche Informationen finden Sie in lakeflowSetupChangeDataCapture der Skriptreferenz zu SQL Server-Hilfsobjekten.

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

Alternative Optionen:

• Für alle Tabellen: @Tables = 'ALL'
• Für bestimmte Schemas: @Tables = 'SCHEMAS:Sales,HR'
• Nur für Setup auf Datenbankebene: @Tables = NULL

Damit Lakeflow Connect die bereits vorhandene (nicht von Lakeflow stammende) CDC-Erfassungsinstanz einer Tabelle übernehmen kann, anstatt sie beizubehalten, setzen Sie @AllowDisablePreExistingCaptureInstances = 1.

Verwaltung der Aufzeichnungsinstanz

Lakeflow Connect verwendet eine präfixbasierte Benennungskonvention, um CDC-Aufnahmeinstanzen zu verwalten, ohne bereits vorhandene Aufnahmeinstanzen zu beeinflussen, die von anderen Systemen oder Prozessen erstellt wurden.

Benennung von Lakeflow-Aufnahmeinstanzen

Lakeflow Connect erstellt und verwaltet Erfassungsinstanzen mithilfe des folgenden Benennungsmusters:

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

Lakeflow Connect verwaltet nur Erfassungsinstanzen, die diesem Benennungsmuster entsprechen. Bereits vorhandene Erfassungsinstanzen mit unterschiedlichen Namen bleiben erhalten und bleiben von Lakeflow-Vorgängen unberührt.

Anforderungen an die Erfassungsinstanz-Slot

SQL Server ermöglicht maximal 2 Erfassungsinstanzen pro Tabelle. Damit Lakeflow Connect mit CDC funktioniert:

• Mindestens eines der beiden Aufnahmeinstanzplätze muss verfügbar sein, damit Lakeflow seine lakeflow_ präfixierte Instanz erstellen kann.
• Wenn beide Slots bereits von Nicht-Lakeflow-Erfassungsinstanzen belegt sind, kann Lakeflow Connect keine eigene Aufnahmeinstanz erstellen und verwalten. Obwohl Lakeflow aus einer bereits vorhandenen Aufnahmeinstanz gelesen werden kann, kann er keine vollständigen Aktualisierungs- oder Schemaentwicklungsvorgänge ausführen.

Koexistenz mit anderen CDC-Verbrauchern

Lakeflow Connect kann sicher mit anderen CDC-Verbrauchern in derselben Tabelle koexistieren:

• Bereits vorhandene Erfassungsinstanzen werden während aller Lakeflow-Vorgänge beibehalten (z. B. vollständige Aktualisierung und Schemaentwicklung).
• Lakeflow löscht und erstellt bei Bedarf nur seine eigenen lakeflow_-präfixierten Instanzen neu.
• Andere Systeme, die CDC-Daten aus Nicht-Lakeflow-Aufnahmeinstanzen verwenden, funktionieren weiterhin ohne Unterbrechung.

Vorgänge, die Lakeflow-Erfassungsinstanzen neu erstellen:

Die folgenden Vorgänge führen dazu, dass Lakeflow seine Aufzeichnungsinstanzen mit vorangestelltem lakeflow_ löscht und neu erstellt (aber keine anderen):

• Vollständige Aktualisierungsvorgänge
• Hinzufügen von Spalten zu Tabellen (ADD COLUMN)

Beispielszenario:

Wenn eine Tabelle eine bereits vorhandene Aufnahmeinstanz mit dem Namen my_app_cdc hat:

1. Lakeflow Connect erstellt lakeflow_schema_table_1.
2. Beide Erfassungsinstanzen existieren sicher nebeneinander.
3. Wenn Lakeflow eine vollständige Aktualisierung oder Schemaentwicklung durchführt, wird nur lakeflow_schema_table_1 neu erstellt.
4. Die my_app_cdc Instanz bleibt unberührt und funktioniert weiterhin für das andere System.

Schritt 4: Erteilen zusätzlicher Berechtigungen (falls erforderlich)

In diesem Schritt werden die erforderlichen Berechtigungen auf System- und Tabellenebene für den Benutzer für die Datenaufnahme gewährt. Während die Schritte 2 und 3 CT- und CDC-spezifische Berechtigungen erteilen, stellt dieser Schritt sicher, dass der Benutzer über alle erforderlichen SELECT Berechtigungen verfügt. Ausführliche Informationen finden Sie in lakeflowFixPermissions der Skriptreferenz zu SQL Server-Hilfsobjekten.

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

Alternative Optionen:

• Für alle Tabellen: @Tables = 'ALL'
• Nur Systemberechtigungen: @Tables = NULL
• Bestimmte Schemas: @Tables = 'SCHEMAS:Sales,HR'

Schritt 5: Überprüfen des Setups

Führen Sie die folgenden Abfragen aus, um zu bestätigen, dass die Änderungsnachverfolgung und das CDC in Ihrer Datenbank und Tabellen ordnungsgemäß konfiguriert sind:

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

Aktualisieren von Hilfsprogrammobjekten

Das Upgrade verwendet dasselbe Skript wie eine Erstinstallation. Befolgen Sie Schritt 1: Installieren oder aktualisieren Sie Hilfsobjekte, einschließlich der mit „(nur Upgrade)“ gekennzeichneten Schritte, die das Pausieren der Erfassung, das erneute Ausführen der Einrichtungsverfahren und das Fortsetzen der Erfassung umfassen.

Beispiel: Hybrid-Ansatz

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

Häufige Szenarien

Szenario 1: Nur Änderungsnachverfolgung (bestimmte Schemas)

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

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

Szenario 2: Nur CDC (bestimmte 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';

Szenario 3: Hybridansatz (CT für einige Schemas, CDC für bestimmte 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';

Weitere Ressourcen

• Skriptreferenz für SQL Server-Hilfsobjekte
• Konfigurieren von Microsoft SQL Server für die Aufnahme in Azure Databricks
• Microsoft SQL Server-Datenbankbenutzeranforderungen
• Nachverfolgen von Datenänderungen (SQL Server) in der SQL Server-Dokumentation