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 : installer les objets utilitaires

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.

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. Exécutez le script.

6. Vérifier l'installation :

   SQL

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

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.

attention

Si vous mettez à niveau à partir d'une version précédente du script d'objets utilitaires, arrêtez la passerelle connectée à la base de données avant d'exécuter le script. La mise à niveau remplace les objets de support DDL, ce qui pourrait perturber un pipeline en cours d'exécution. Pour les étapes complètes de la mise à niveau, consultez Mettre à niveau les objets utilitaires.

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

Le suivi des modifications est un mécanisme léger qui suit les modifications apportées aux lignes de la table. Cette étape active la CT au niveau de la base de données sur les tables spécifiées et configure les objets de support DDL pour gérer les modifications de schéma. Pour plus de détails, consultez lakeflowSetupChangeTracking dans la référence de script des objets utilitaires SQL Server.

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

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

Pour effectuer une mise à niveau à partir d'une version précédente du script d'objets utilitaires, réexécutez le script. Le script supprime automatiquement tous les objets versionnés précédents avant d'installer la nouvelle version.

remarque

Une refresh complète du pipeline n'est pas requise après la mise à niveau. Le suivi des modifications et la CDC demeurent activés sur vos tables, et les pipelines reprennent à partir de leur dernier point de contrôle.

1. Arrêtez la passerelle connectée à la base de données. La mise à niveau remplace les objets de prise en charge DDL, ce qui pourrait perturber un pipeline en cours d'exécution.

2. download la dernière version du script :

Download utility_script.sql

3. Connectez-vous à la base de données cible en tant qu'utilisateur db_owner.

4. Exécutez le script.

5. Réexécutez les procédures de configuration de votre configuration en utilisant les mêmes paramètres que votre configuration d'origine :

   • Si vous utilisez le suivi des modifications : Exécutez lakeflowSetupChangeTracking.
   • Si vous utilisez CDC : Exécutez lakeflowSetupChangeDataCapture.

6. Vérifier la mise à niveau :

   SQL

   SELECT dbo.lakeflowUtilityVersion_1_5() AS UtilityVersion;

7. Reprendre la passerelle.

remarque

Évitez d'apporter des modifications de schéma aux tables suivies pendant la fenêtre de mise à niveau. Le Trigger d'audit DDL est remplacé pendant la mise à niveau, de sorte que les modifications de schéma qui se produisent avant la nouvelle exécution des procédures de configuration ne seront pas capturées.

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