Préparer SQL Server pour l’ingestion à l’aide du script d’objets utilitaires

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

Spécifications

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

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

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

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

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

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

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

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

Étape 1 : Installer ou mettre à jour des objets utilitaires

Cette étape installe les procédures stockées et les fonctions de l’utilitaire nécessaires à l’installation de SQL Server. Pour plus d’informations sur les éléments installés, consultez la référence de script des objets utilitaires SQL Server.

Le même script gère à la fois une première installation et une mise à jour par rapport à une version précédente. Suivez les étapes numérotées ci-dessous. Les étapes marquées (Mise à jour uniquement) ne s’appliquent que si une version antérieure des objets utilitaires est déjà installée. Évitez-les pour une première installation.

1. Téléchargez le script :

   Télécharger 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 avec le db_owner rôle.

4. Vérifiez que vous êtes connecté à la base de données cible.

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

6. Exécutez le script. Lors d’une mise à niveau, le script recrée les fonctions utilitaires et ne supprime que les objets hérités préfixés par replicant. Cela laisse vos objets de support DDL existants en place, donc le déclencheur DDL actuel continue de capturer les changements de schéma jusqu’à ce que vous relancez les procédures de configuration à l’étape suivante.

7. (Mise à jour uniquement) Relancez la procédure de configuration pour chaque méthode de capture que vous utilisez : lakeflowSetupChangeTracking si vous utilisez le suivi des changements, et lakeflowSetupChangeDataCapture si vous utilisez CDC. Cela permet de transférer vos objets vers la nouvelle version et de restaurer les permissions de l’utilisateur d’ingestion. Transmettez uniquement @User. Vous n’avez pas besoin de @Tables, car le suivi des modifications et le CDC restent activés sur vos tables lors d’une mise à niveau.

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

   Indiquez de nouveau toutes les options non par défaut afin que la mise à niveau préserve votre configuration :

   • Suivi des changements : Par défaut, la table d’audit DDL et le déclencheur ne sont pas créés, donc la plupart des mises à jour ne passent rien de plus ici. Si votre installation utilise la capture des modifications de schéma DDL (elle comporte une table lakeflowDdlAudit), passez @CreateDdlSupportingObjects = 1 sur lakeflowSetupChangeTracking pour déplacer ces objets vers la nouvelle version. Les installations d’avant l’existence de cette option les créaient automatiquement, donc incluez le drapeau si vous comptez sur la capture DDL même si vous ne l’avez jamais configurée.
   • CDC: Si vous utilisez @AllowDisablePreExistingCaptureInstances = 1 sur lakeflowSetupChangeDataCapture, incluez-le à nouveau.

8. Vérifiez l’installation :

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

9. (Mise à niveau uniquement) Reprendre l’ingestion des données : reprendre la passerelle ou reprendre le pipeline.

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

Le suivi des modifications est un mécanisme léger qui assure le suivi des modifications apportées aux lignes des tables. Cette étape permet la TC au niveau de la base de données sur des tables spécifiées. Pour capturer également les modifications de schéma (DDL), fournissez @CreateDdlSupportingObjects = 1 pour créer les objets de prise en charge du DDL. Cette option est facultative et désactivée par défaut. Pour plus d'informations, veuillez consulter lakeflowSetupChangeTracking dans Référence des scripts des objets utilitaires 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';

Autres options

• Pour toutes les tables avec des clés primaires : @Tables = 'ALL'
• Pour des 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 de données modifiées (pour les tables sans clés primaires)

CDC capture l’activité d’insertion, de mise à jour et de suppression, et est particulièrement utile pour les tables sans clé primaire. Cette étape active CDC au niveau de la base de données, configure la gestion des instances de capture et crée des déclencheurs pour la gestion automatique des modifications de schéma. Pour plus d'informations, veuillez consulter lakeflowSetupChangeDataCapture dans Référence des scripts des objets utilitaires SQL Server.

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

Autres options

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

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

Gestion des instances de capture

Lakeflow Connect utilise une convention d’affectation de noms basée sur des préfixes pour gérer les instances de capture CDC sans affecter les instances de capture CDC préexistantes créées par d’autres systèmes ou processus.

Nommage d’instance de capture Lakeflow

Lakeflow Connect crée et gère les instances de capture selon le 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 modèle de nommage. Les instances de capture préexistantes avec différents noms sont conservées et restent inchangées par les opérations Lakeflow.

Exigences de créneau de l’instance de capture

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

• Au moins l’un des deux emplacements d’instance de capture doit être disponible pour Lakeflow afin de créer sa lakeflow_ instance préfixée.
• Si les deux emplacements sont déjà occupés par des instances de capture autres que 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 d’opérations complètes d’actualisation ou d’évolution du schéma.

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, l’actualisation complète et l’évolution du schéma).
• Lakeflow ne supprime et ne recrée que ses propres lakeflow_ instances préfixées lorsque c'est nécessaire.
• D’autres systèmes consommant des données CDC provenant d’instances de capture non Lakeflow continuent de fonctionner sans interruption.

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

Les opérations suivantes amènent Lakeflow à supprimer et recréer ses instances de capture préfixées par lakeflow_ (mais pas les autres) :

• Opérations d’actualisation complètes
• Ajout de colonnes à des tables (ADD COLUMN)

Exemple de scénario :

Si une table possède 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. Lorsqu'une actualisation complète ou une évolution de schéma sont effectuées par Lakeflow, elle 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 à l'utilisateur d'ingestion les autorisations système et les autorisations au niveau des tables nécessaires. Bien 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 requises SELECT . Pour plus d'informations, veuillez consulter lakeflowFixPermissions dans Référence des scripts des objets utilitaires SQL Server.

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

Autres options

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

Étape 5 : Vérifier l’installation

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

-- 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 des objets utilitaires

La mise à niveau utilise le même script qu’une première installation. Suivez l’étape 1 : Installez ou mettez à jour les objets utilitaires, y compris les étapes marquées (Mise à jour uniquement), qui incluent la pause de l’ingestion, la reprise des procédures de configuration et la reprise de l’ingestion.

Exemple : approche hybride

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

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)

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)

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

• Informations de référence sur les scripts d’objets utilitaires SQL Server
• Configurer Microsoft SQL Server pour l’ingestion dans Azure Databricks
• Configuration requise pour l’utilisateur de base de données Microsoft SQL Server
• Suivre les modifications de données (SQL Server) dans la documentation SQL Server