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.
• Pour la configuration CT : le suivi des modifications doit être disponible sur la plateforme.
• Pour la configuration de capture de données modifiées : la capture de données modifiées doit être disponible sur la plateforme.

Étape 1 : Installer 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.

1. Téléchargez le script : 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. Exécutez le script.

6. Vérifiez l’installation :

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

É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 suit les modifications apportées aux lignes de table. Cette étape active le CT au niveau de la base de données sur les tables spécifiées et configure des objets de support DDL pour gérer les modifications de schéma. Pour plus d’informations, consultez lakeflowSetupChangeTracking la référence de script 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 la capture de données modifiées à l'échelle de la base de données, configure la gestion des instances de capture et crée des déclencheurs (triggers) pour la gestion automatique des modifications de schéma. Pour plus d’informations, consultez lakeflowSetupChangeDataCapture la référence de script 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

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 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 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 entraînent la suppression et la recréation des instances de capture préfixées par lakeflow_ (mais pas des 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 les autorisations système et table nécessaires pour l’utilisateur d’ingestion. 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, consultez lakeflowFixPermissions la référence de script 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 vérifier que le suivi des modifications et la capture de données de changement sont correctement configurés dans 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;

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