Informations de référence sur les scripts d’objets utilitaires SQL Server

Documents de référence d’accès pour le script d’objets utilitaires SQL Server, y compris les composants, les paramètres et la résolution des problèmes.

Aperçu

Le script installe des procédures stockées et des fonctions utilitaires avec version pour configurer votre base de données SQL Server pour l’ingestion dans Lakeflow Connect. Les tâches d’installation sont les suivantes :

• Gestion des autorisations
• Configuration du suivi des modifications (CT)
• Configuration de la capture des changements de données (CDC)
• Détection des plates-formes
• DDL prend en charge la création d’objets pour le suivi des modifications de schéma

Détails de version

• Version actuelle : 1.1
• Version principale : 1
• Version mineure : 1
• Fonction de version : lakeflowUtilityVersion_1_1()

Composants clés

Functions

lakeflowDetectPlatform()

Détecte le type de plateforme SQL Server.

Retourne : 'AZURE_SQL_DATABASE', 'AZURE_SQL_MANAGED_INSTANCE', 'AMAZON_RDS', 'ON_PREMISES', ou 'UNKNOWN'

lakeflowUtilityVersion_1_1()

Détecte la version des objets utilitaires.

Retourne : '1.1'

Procédures stockées

lakeflowFixPermissions

Accorde des autorisations requises aux utilisateurs pour les opérations d’ingestion.

Paramètres :

@Tables options de paramètre :

Qu’est-ce qu’il fait :

• Octroie sur les vues du système requises SELECT (sys.objects, sys.tables, sys.columns, etc.)
• Accorde EXECUTE sur les procédures stockées système (sp_tables, sp_columns_100, etc.)
• Accorde éventuellement des autorisations SELECT sur les tables d'utilisateur selon le paramètre @Tables
• Gère les différences spécifiques à la plateforme (Azure SQL Database, Managed Instance, RDS, Local)

lakeflowSetupChangeTracking

Active le suivi des modifications aux niveaux de base de données et de table avec prise en charge de DDL.

Paramètres :

@Tables options de paramètre :

Qu’est-ce qu’il fait :

• Active le suivi des modifications au niveau de la base de données s’il n’est pas déjà activé
• Crée une table d’audit DDL versionnée (lakeflowDdlAudit_1_2)
• Crée un déclencheur d’audit DDL pour capturer les modifications de schéma
• Active la fonctionnalité CT sur les tables spécifiées (ignore les tables sans clés primaires)
• Octroie des VIEW CHANGE TRACKING autorisations à l’utilisateur spécifié
• CLEANUP mode: Supprime les objets de support DDL

Comportements importants :

• Passe automatiquement les tables sans clés primaires (CDC est recommandée pour ces tables)
• Découverte intelligente avec le 'ALL' paramètre
• Idempotent : Sans risque d'exécuter plusieurs fois

lakeflowSetupChangeDataCapture

Active la capture de données modifiées (CDC) au niveau de la base de données et de la table avec la prise en charge des DDL et la gestion des instances de capture.

Paramètres :

@Tables options de paramètre :

Qu’est-ce qu’il fait :

• Active le Change Data Capture au niveau de la base de données si ce n'est pas déjà activé.
• Crée une table de suivi d’instance de capture (lakeflowCaptureInstanceInfo_1_2)
• Crée des procédures d’assistance pour la gestion des instances de capture :
  • lakeflowDisableOldCaptureInstance_1_2
  • lakeflowMergeCaptureInstances_1_2
  • lakeflowRefreshCaptureInstance_1_2
• Crée un ALTER TABLE déclencheur pour la gestion automatique des modifications de schéma
• Active la CDC (Capture de Données Modifiées) sur les tables spécifiées
• Octroie les autorisations cdc requises à l’utilisateur spécifié
• CLEANUP mode : Supprime tous les objets support de DDL CDC

Comportements importants :

• Fonctionne avec des tables avec ou sans clés primaires
• Gère automatiquement la rotation des instances de capture sur les modifications de schéma
• Idempotent : Sans risque d'exécuter plusieurs fois

Support de la plateforme

• SQL Server sur site (EngineEdition 1-4)
• Azure SQL Database (EngineEdition 5)
• Azure SQL Managed Instance (EngineEdition 8)
• Amazon RDS pour SQL Server (détecté par le modèle de nom de serveur)

Prerequisites

• L’utilisateur exécutant le script doit être membre du db_owner rôle
• Pour l’installation de CT : le suivi des modifications doit être disponible sur la plateforme
• Pour configurer la capture des modifications de données : la capture des modifications de données doit être intégrée à la plateforme.

Instructions d’installation

Télécharger et exécuter le script

1. Téléchargez le script : utility_script.sql

2. Exécuter le script

   • Ouvrez le script téléchargé dans SQL Server Management Studio (SSMS), Azure Data Studio ou votre client SQL préféré.
   • Connectez-vous à votre instance SQL Server.
   • Vérifiez que vous êtes connecté à la base de données cible où vous souhaitez installer les objets utilitaires.
   • Exécutez le script.

3. Vérifier l’installation

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion_1_1() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;
   

Alternative : Exécuter à l’aide de la ligne de commande

Si vous préférez utiliser sqlcmd:

sqlcmd -S YourServerName -d YourDatabase -E -i lakeflow_utility_objects.sql

Exemple : Corriger les autorisations (système uniquement)

-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser';

Exemple : Corriger les permissions (avec accès à la table)

-- Grant system permissions plus access to all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'ALL';

-- Grant permissions for specific schemas
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'SCHEMAS:Sales,HR,Production';

-- Grant permissions for specific tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'Sales.Orders,HR.Employees';

Exemples : Configuration du suivi des modifications

Au niveau de la base de données uniquement

-- Setup CT infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = NULL,
    @User = 'myuser';

Activer sur toutes les tables

-- Enable CT on all user tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

Configuration basée sur le schéma

-- Enable CT on all tables in specific schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'myuser',
    @Retention = '3 DAYS';

Tables spécifiques

-- Enable CT on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'dbo.Table1,Sales.Orders,HR.Employees',
    @User = 'myuser';

Exemples : configuration CDC

Au niveau de la base de données uniquement

-- Setup CDC infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = NULL,
    @User = 'myuser';

Activer sur toutes les tables

-- Enable CDC on all user tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Tables spécifiques

-- Enable CDC on specific tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'dbo.Table1,Sales.Orders',
    @User = 'myuser';

Exemple : approche hybride

-- Step 1: Enable CT on tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

-- Step 2: Enable CDC on remaining tables (without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

Exemple : Nettoyage

-- Remove CT DDL support objects
EXEC dbo.lakeflowSetupChangeTracking
    @Mode = 'CLEANUP';

-- Remove CDC DDL support objects
EXEC dbo.lakeflowSetupChangeDataCapture
    @Mode = 'CLEANUP';

Objets de support DDL créés

Les objets de prise en charge DDL suivants sont créés, selon que vous utilisez le suivi des modifications (change tracking) ou la capture de données modifiées (CDC).

Pour le suivi des modifications

Pour cdc

Limitations du suivi des modifications

• Nécessite des clés primaires : les tables sans clés primaires ne peuvent pas utiliser le suivi des modifications.
• Le script ignore automatiquement les tables sans clé primaire et recommande d’utiliser la capture de données modifiées (CDC) à la place.

Comportement spécifique à la plateforme

• Azure SQL Database : les procédures stockées système sont accessibles par défaut (aucune subvention n’est EXECUTE nécessaire).
• Vues délimitées au serveur : accès limité dans Azure SQL Database pour les vues telles que sys.change_tracking_databases.

Chemin de mise à niveau

• Le script supprime automatiquement toutes les versions précédentes lors de l’exécution.
• Schéma de contrôle de version : objectName_majorVersion_minorVersion
• Les objets actuels utilisent _1_1 le suffixe pour le suivi des versions.

Meilleures pratiques

• Exécutez toujours en tant qu’utilisateur db_owner avec des privilèges équivalents.
• Testez d’abord les bases de données hors production.
• Utilisez l’approche hybride pour une couverture complète.
• Exécutez lakeflowFixPermissions après l’installation pour garantir un accès utilisateur approprié.
• Tenez compte des périodes de rétention en fonction de votre fréquence d’ingestion.

Résolution des problèmes

« L’utilisateur exécutant ce script n’est pas un membre de rôle « db_owner »

Solution : Exécuter en tant qu’utilisateur avec le rôle db_owner

« Le suivi des modifications n’est pas activé sur le catalogue »

Solution : activez CT au niveau de la base de données ou laissez la procédure la gérer automatiquement

« La capture de données modifiées n’est pas activée sur le catalogue »

Solution : activer le CDC au niveau de la base de données elle-même ou laisser la procédure le gérer automatiquement

« Tables ignorées en raison de clés primaires manquantes »

Solution : Utiliser lakeflowSetupChangeDataCapture pour ces tables à la place

Intégration de la validation

Les objets utilitaires suivants sont validés par l’infrastructure de validation Java :

Notes de migration

Si vous mettez à niveau à partir d'anciennes versions d'objets de support DDL (avant l'ère des objets utilitaires) :

• Le script nettoie automatiquement les objets hérités.
• Aucun nettoyage manuel n’est nécessaire.
• La version 1.1 consolide toutes les fonctionnalités dans des procédures unifiées.

Ressources supplémentaires

• Préparer SQL Server pour l’ingestion à l’aide du script d’objets utilitaires
• 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
• À propos du suivi des modifications (SQL Server) dans la documentation DE SQL Server
• Qu’est-ce que la capture des changements de données (CDC) ? dans la documentation SQL Server