Préparez SQL Server pour l'ingestion à l'aide du script d'objets d'infrastructures publiques.

Effectuez les tâches de configuration de la base de données SQL Server pour ingérer les données dans Databricks à l’aide de Lakeflow Connect.

Exigences

• L'utilisateur exécutant le script doit être membre du rôle db_owner . Ce rôle est requis uniquement pour l’exécution du script de configuration, pas pour l’utilisateur d’ingestion.

Pour ajouter un utilisateur au rôle db_owner, utilisez l'une des méthodes suivantes :

• SQL Server moderne (2012+) : utilisez ALTER ROLE

  SQL

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

• Environnements SQL Server hérités ou restreints : Utilisez sp_addrolemember

  SQL

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

• Pour la configuration de la CT : le suivi des modifications doit être disponible sur la plateforme.

• Pour la configuration CDC : la capture de données modifiées doit être disponible sur la plateforme.

Étape 1 : Installez ou mettez à niveau les objets d'infrastructures publiques

Cette étape installe les procédures stockées et les fonctions utilitaires nécessaires à la configuration de SQL Server. Pour plus de détails sur ce qui est installé, consultez la référence du script des objets d'infrastructures publiques SQL Server.

Le même script gère à la fois une première installation et une mise à niveau à partir d’une version précédente. Suivez les étapes numérotées ci-dessous. Les étapes marquées (Mise à niveau uniquement) s’appliquent uniquement si une version précédente des objets d’infrastructures publiques est déjà installée. Ignorez-les pour une première installation.

important

(Mise à niveau uniquement) Arrêtez la passerelle (pipeline basé sur une passerelle) ou mettez le pipeline en pause (CDC intégrée) avant d’exécuter le script. La mise en pause permet d’éviter qu’un changement de schéma n’échoue rapidement et ne nécessite une nouvelle tentative lors de la brève transition.

1. download le script :

Download utility_script.sql

2. Ouvrez le script dans SQL Server Management Studio (SSMS), Azure Data Studio ou votre client SQL préféré.

3. Connectez-vous à votre instance SQL Server en tant qu'utilisateur ayant le rôle db_owner.

4. Assurez-vous que vous êtes connecté(e) à la base de données cible.

5. (Mise à niveau uniquement) Mettre en pause l’ingestion : arrêtez la passerelle (pipeline basé sur une passerelle) ou mettez en pause le pipeline (CDC intégrée).

6. Exécutez le script. Lors d’une mise à niveau, le script recrée les fonctions d’infrastructures publiques et supprime uniquement les anciens objets préfixés par replicant. Il laisse vos objets de prise en charge DDL existants en place, de sorte que le trigger DDL actuel continue de capturer les modifications de schéma jusqu'à ce que vous réexécutiez les procédures de configuration à l'étape suivante.

7. (Mise à niveau uniquement) Réexécutez la procédure de configuration pour chaque méthode de capture que vous utilisez : lakeflowSetupChangeTracking si vous utilisez le suivi des modifications, et lakeflowSetupChangeDataCapture si vous utilisez la CDC. Cette action reporte vos objets vers la nouvelle version et restaure les autorisations de l’utilisateur d’ingestion. Valider @User uniquement. Vous n’avez pas besoin de @Tables, car le suivi des modifications et la CDC restent activés sur vos tables lors d’une mise à niveau.

   SQL

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

   Passez à nouveau les options autres que default afin que la mise à niveau préserve votre configuration :

   • Suivi des modifications : By default, la table d’audit DDL et le Trigger ne sont pas créés, de sorte que la plupart des mises à niveau ne transmettent rien de plus ici. Si votre installation utilise la capture de modifications de schéma DDL (elle possède une table lakeflowDdlAudit), transmettez @CreateDdlSupportingObjects = 1 sur lakeflowSetupChangeTracking pour déplacer ces objets vers la nouvelle version. Les installations antérieures à l’existence de cette option les ont créées automatiquement, veuillez donc inclure l’indicateur si vous vous appuyez sur la capture DDL, même si vous ne l’avez jamais défini.
   • CDC : si vous utilisez @AllowDisablePreExistingCaptureInstances = 1 sur lakeflowSetupChangeDataCapture, incluez-le à nouveau.

8. Vérifier l'installation :

   SQL

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

9. (Mise à niveau uniquement) Reprendre l’ingestion : reprenez la passerelle ou reprenez le pipeline.

remarque

Le rôle db_owner est uniquement requis pour l'utilisateur exécutant ce script de configuration. L'utilisateur d'ingestion (spécifié dans le paramètre @User des étapes suivantes) requiert uniquement les autorisations spécifiques accordées par les procédures de configuration. Consultez les exigences utilisateur de la base de données Microsoft SQL Server pour plus de détails.

remarque

(Upgrade only) Avoid making schema changes to tracked tables during the upgrade window. The cutover runs in a transaction, so a concurrent schema change is not lost, but it can block briefly or fail fast and need a retry until the cutover completes.

Étape 2 : activer le suivi des modifications (pour les tables avec clés primaires)

Change tracking is a lightweight mechanism that tracks changes to table rows. This step enables CT at the database level on specified tables. To also capture schema changes (DDL), pass @CreateDdlSupportingObjects = 1 to create the DDL support objects. This is opt-in and off by default. For details, see lakeflowSetupChangeTracking in SQL Server utility objects script reference.

SQL

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

Options alternatives :

• Pour toutes les tables avec clés primaires : @Tables = 'ALL'
• Pour les schémas spécifiques : @Tables = 'SCHEMAS:Sales,HR,Production'
• Pour la configuration au niveau de la base de données uniquement (aucune activation de table) : @Tables = NULL

Étape 3 : activer la capture des données modifiées (pour les tables sans clé primaire)

La CDC capture les activités d'insertion, de mise à jour et de suppression et est particulièrement utile pour les tables sans clés primaires. Cette étape active la CDC au niveau de la base de données, configure la gestion de l'instance de capture et crée des triggers pour la gestion automatique des changements de schéma. Pour plus de détails, consultez lakeflowSetupChangeDataCapture dans la référence de script des objets utilitaires SQL Server.

SQL

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

Options alternatives :

• Pour toutes les tables : @Tables = 'ALL'
• Pour les schémas spécifiques : @Tables = 'SCHEMAS:Sales,HR'
• Pour la configuration au niveau de la base de données uniquement : @Tables = NULL

Pour permettre à Lakeflow Connect de prendre en charge une instance de capture CDC préexistante (non Lakeflow) d’une table au lieu de la laisser en place, définissez @AllowDisablePreExistingCaptureInstances = 1.

remarque

Vous pouvez utiliser le suivi des modifications ou la CDC, ou vous pouvez utiliser les deux. Databricks recommande d'utiliser le suivi des modifications pour les tables avec des clés primaires (étape 2) et la CDC pour les tables sans clés primaires (étape 3) pour une couverture complète.

Gestion des instances de capture

Lakeflow Connect utilise une convention de nommage basée sur un préfixe pour gérer les instances de capture CDC sans affecter les instances de capture préexistantes créées par d'autres systèmes ou processus.

Dénomination des instances de capture Lakeflow

Lakeflow Connect crée et gère des instances de capture à l'aide du modèle de nommage suivant :

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

LakeFlow Connect gère uniquement les instances de capture qui correspondent à ce schéma de nommage. Les instances de capture préexistantes portant des noms différents sont conservées et ne sont pas affectées par les Opérations Lakeflow.

remarque

Dans les versions du script utilitaire antérieures à 1.4, Lakeflow Connect utilisait le préfixe New_ pour les instances de capture (par exemple, New_schema_table_1). Si vous effectuez une mise à niveau à partir d'une version antérieure, exécutez le script utilitaire mis à jour pour migrer vers la convention de dénomination lakeflow_. Le script gère automatiquement la rétrocompatibilité avec les instances de capture existantes préfixées par New_pendant la transition.

Capturer les exigences du slot d'instance

SQL Server autorise un maximum de 2 instances de capture par table. Pour que Lakeflow Connect fonctionne avec la CDC :

• Au moins l'un des deux emplacements d'instance de capture doit être disponible pour que Lakeflow puisse créer son instance préfixée lakeflow_.
• Si les deux emplacements sont déjà occupés par des instances de capture non-Lakeflow, Lakeflow Connect ne peut pas créer et gérer sa propre instance de capture. Bien que Lakeflow puisse lire à partir d'une instance de capture préexistante, il ne peut pas effectuer de refresh complet ou d'Opérations d'évolution des schémas.

astuce

Si les deux emplacements d'instance de capture sont occupés, utilisez le suivi des modifications à la place, ou supprimez l'une des instances de capture existantes si elle n'est plus nécessaire.

Coexistence avec d'autres consommateurs CDC.

Lakeflow Connect peut coexister en toute sécurité avec d'autres consommateurs CDC sur la même table :

• Les instances de capture préexistantes sont conservées pendant toutes les Opérations Lakeflow (par exemple, refresh complète et évolution des schémas).
• Lakeflow supprime et recrée ses propres instances préfixées par lakeflow_ uniquement lorsque cela est nécessaire.
• D'autres systèmes consommant des données CDC à partir d'instances de capture non-LakeFlow continuent de fonctionner sans interruption.

Opérations qui recréent les instances de capture Lakeflow :

Les opérations suivantes entraînent la suppression et la recréation par Lakeflow de ses instances de capture préfixées lakeflow_ (mais pas des autres) :

• Opérations de refresh complète
• Ajout de colonnes aux tables (ADD COLUMN)

Exemple de scénario :

Si une table a une instance de capture préexistante nommée my_app_cdc:

1. Lakeflow Connect crée lakeflow_schema_table_1.
2. Les deux instances de capture coexistent en toute sécurité.
3. Lorsque Lakeflow effectue un refresh complet ou une évolution des schémas, il ne recrée que lakeflow_schema_table_1.
4. L'instance my_app_cdc reste intacte et continue de fonctionner pour l'autre système.

Étape 4 : accorder des autorisations supplémentaires (si nécessaire)

Cette étape accorde les autorisations système et de niveau table nécessaires à l'utilisateur d'ingestion. Alors que les étapes 2 et 3 accordent des autorisations spécifiques à CT et CDC, cette étape garantit que l'utilisateur dispose de toutes les autorisations SELECT requises. Pour plus de détails, consultez lakeflowFixPermissions dans la référence de script des objets utilitaires SQL Server.

SQL

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

Options alternatives :

• Pour toutes les tables : @Tables = 'ALL'
• Autorisations système uniquement : @Tables = NULL
• Schémas spécifiques : @Tables = 'SCHEMAS:Sales,HR'

remarque

Les procédures de configuration des étapes 2 et 3 accordent automatiquement les autorisations CT et CDC nécessaires, mais vous devrez peut-être exécuter cette procédure pour accorder des autorisations supplémentaires au niveau de la table SELECT ou si les autorisations ont été révoquées.

Étape 5 : Vérifier la configuration

Exécutez les queries suivantes pour confirmer que le suivi des modifications et la CDC sont correctement configurés sur votre base de données et vos tables :

SQL

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

Mettre à niveau les objets infrastructure publique

La mise à niveau utilise le même script qu'une installation initiale. Suivez la 1re étape : Installer ou mettre à niveau les objets utilitaires, y compris les étapes marquées (Mise à niveau uniquement), qui couvrent la suspension de l'ingestion, réexécution des procédures de configuration et la reprise de l'ingestion.

remarque

Pour rétablir une version précédente du script d’objets d’infrastructures publiques, veuillez contacter l’assistance Databricks.

Exemple : approche hybride

remarque

Cet exemple utilise 'ALL' pour activer CT et CDC sur toutes les tables pour plus de simplicité. Pour une utilisation en production, examinez les scénarios courants sur cette page pour cibler des schémas ou des tables spécifiques.

SQL

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

Scénarios courants

Scénario 1 : Suivi des modifications uniquement (schémas spécifiques)

SQL

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

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

Scénario 2 : CDC uniquement (tables spécifiques)

SQL

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

Scénario 3 : Approche hybride (CT pour certains schémas, CDC pour des tables spécifiques)

SQL

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

Ressources supplémentaires

• Référence de script des objets utilitaires SQL Server
• Configurer Microsoft SQL Server pour l'ingestion dans Databricks
• Exigences utilisateur de la base de données Microsoft SQL Server
• Suivre les modifications de données (SQL Server) dans la documentation SQL Server