Référence de script des objets utilitaires SQL Server
Accédez au matériel de référence pour le script d’objets utilitaires SQL Server, y compris les composants, les paramètres et le dépannage.
Présentation
Le script installe des procédures stockées et des fonctions d'utilitaire versionnées pour configurer votre base de données SQL Server pour l'ingestion dans Lakeflow Connect. Les tâches de configuration incluent :
- Gestion des autorisations
- Configuration du suivi des modifications (CT)
- Configuration de la capture de données modifiées (CDC)
- Détection de plateforme
- Le DDL prend en charge la création d'objets pour le suivi des modifications de schéma.
Informations sur la version
- Version actuelle : 1,5
- Version principale : 1
- Version mineure : 5
- Fonction de version :
lakeflowUtilityVersion_1_5()
Quoi de neuf dans la version 1.5
- Échec du Trigger d'audit DDL résolu sur Azure SQL Database.
- Rétrocompatibilité corrigée avec les anciens objets de support DDL.
- Perte de données corrigée lors des modifications de schéma en présence d’une instance de capture préexistante.
Composants clés
Fonctions
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_5()
Détecte la version des objets utilitaires.
Renvoie : '1.5'
Procédures stockées
lakeflowFixPermissions
Accorde les autorisations requises aux utilisateurs pour les opérations d'ingestion.
Paramètres :
parameter | Description |
|---|---|
| Obligatoire. Nom d'utilisateur auquel accorder les autorisations. |
| Facultatif. Contrôle la portée des autorisations au niveau de la table |
@Tables parameter options :
Option | Description |
|---|---|
| Accorder uniquement les autorisations au niveau du système (default) |
| Accorder des autorisations sur toutes les tables d’utilisateur dans la base de données |
| Accorder des autorisations sur toutes les tables des schémas spécifiés |
| Accorder des autorisations sur des tables spécifiques |
Prise en charge des caractères génériques | Exemple : |
Ce qu'il fait :
- Accorde
SELECTsur les vues système requises (sys.objects,sys.tables,sys.columns, etc.). - Accorde
EXECUTEsur les procédures stockées système (sp_tables,sp_columns_100, etc.) - Accorde éventuellement
SELECTsur les tables utilisateur en fonction du parameter@Tables - Gère les différences spécifiques à la plateforme (Azure SQL Database, Managed Instance, RDS, on-premise)
lakeflowSetupChangeTracking
Permet le suivi des modifications au niveau de la base de données et des tables avec prise en charge du DDL.
Paramètres :
parameter | Description |
|---|---|
| Facultatif. Tables sur lesquelles activer CT |
| Facultatif. Utilisateur auquel accorder des autorisations |
| Facultatif. Période de rétention CT (default: |
| Facultatif. |
@Tables parameter options :
Option | Description |
|---|---|
| Configurer uniquement la prise en charge des CT et DDL au niveau de la base de données (pas d’activation de table) |
| Activer CT sur toutes les tables utilisateur avec clés primaires. |
| Activer la CT sur les tables dans les schémas spécifiés |
| Activer CT sur des tables spécifiques |
Prise en charge des caractères génériques | Exemple : |
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_5) - Crée un trigger d'audit DDL pour capturer les changements de schéma
- Active CT sur les tables spécifiées (ignore les tables sans clé principale)
- Accorde
VIEW CHANGE TRACKINGautorisations à l'utilisateur spécifié CLEANUPmode : Supprime les objets de support DDL
Comportements importants :
- Ignore automatiquement les tables sans clé primaire (la CDC est recommandée pour ces dernières)
- Découverte intelligente avec le paramètre
'ALL' - Idempotent : Peut être exécuté plusieurs fois en toute sécurité
lakeflowSetupChangeDataCapture
Active le CDC au niveau des bases de données et des tables avec prise en charge DDL et gestion des instances de capture.
Paramètres :
parameter | Description |
|---|---|
| Facultatif. Tables sur lesquelles activer la CDC |
| Facultatif. Utilisateur auquel accorder des autorisations |
| Facultatif. |
@Tables parameter options :
Option | Description |
|---|---|
| Configurer uniquement le support CDC et DDL au niveau de la base de données |
| Activer le CDC sur toutes les tables utilisateur |
| Activer la CDC sur les tables dans les schémas spécifiés |
| Activer la CDC sur des tables spécifiques |
Ce qu'il fait :
-
Active le CDC au niveau de la base de données si ce n’est pas déjà activé.
-
Crée une table de suivi des instances de capture (
lakeflowCaptureInstanceInfo_1_5) -
Crée des procédures d’assistance pour la gestion des instances de capture :
lakeflowDisableOldCaptureInstance_1_5lakeflowMergeCaptureInstances_1_5lakeflowRefreshCaptureInstance_1_5
-
Crée un Trigger
ALTER TABLEpour la gestion automatique des changements de schéma -
Active la CDC sur les tables spécifiées
-
Accorde les autorisations CDC requises à l'utilisateur spécifié.
-
CLEANUPmode : supprime tous les objets de support DDL CDC
Comportements importants :
- Compatible avec les tables avec ou sans clés primaires
- Gère automatiquement la rotation des instances de capture lors des modifications de schéma
- Idempotent : Peut être exécuté plusieurs fois en toute sécurité
Prise en charge de la plateforme
- SQL Server on-premise (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)
Prérequis
- L'utilisateur exécutant le script doit être membre du rôle
db_owner - Pour la configuration de 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
Instructions d'installation
Download et exécutez le script
- download la dernière version du script :
-
Exécuter le script :
- Ouvrez le script download dans SQL Server Management Studio (SSMS), Azure Data Studio, ou votre client SQL préféré.
- Connectez-vous à votre instance SQL Server.
- Confirmez que vous êtes connecté à la base de données cible où vous souhaitez installer les objets d'infrastructures publiques.
- Exécutez le script.
-
Vérifier l'installation :
SQL-- Verify installation
SELECT dbo.lakeflowUtilityVersion_1_5() 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 utility_script.sql
Remplacez YourServerName et YourDatabase par les noms réels de votre serveur et de votre base de données. Utilisez -U username -P password au lieu de -E si l'authentification Windows n'est pas utilisée.
Exemple : Corriger les autorisations (système uniquement)
-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
@User = 'myuser';
Exemple : Corriger les autorisations (avec accès aux tables)
-- 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
Uniquement au niveau de la base de données
-- 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
Uniquement au niveau de la base de données
-- 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 créés par le support DDL
Les objets de support DDL suivants sont créés, selon que vous utilisez le suivi des modifications ou la CDC.
Pour le suivi des modifications
Type d'objet | Nom | Description |
|---|---|---|
Table |
| Historique des modifications DDL |
Déclencheur |
| Capture |
Pour CDC
Type d'objet | Nom | Description |
|---|---|---|
Table |
| Assure le suivi des instances de capture. |
Procédure |
| Supprime l'ancienne instance de capture |
Procédure |
| Merge les données entre les instances |
Procédure |
| Créer une nouvelle instance de capture |
Déclencheur |
| Gère les changements de schéma |
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 PK et recommande l'utilisation de la CDC à la place.
Comportement spécifique à la plateforme
- Azure SQL Database : les procédures stockées du système sont accessibles par default (aucune autorisation
EXECUTEnécessaire). - Vues au niveau du serveur : accès limité dans Azure SQL Database pour des vues comme
sys.change_tracking_databases.
Chemin de mise à niveau
Pour effectuer la mise à niveau, réexécutez le script d'objets utilitaires. Le script supprime automatiquement tous les objets versionnés précédents avant d’installer la nouvelle version. Pour des instructions étape par étape, consultez Mettre à niveau les objets utilitaires.
Ce qui se passe pendant une mise à niveau :
- Toutes les procédures stockées et fonctions Lakeflow précédentes sont supprimées et recréées avec le nouveau suffixe de version.
- Les objets de support DDL des versions précédentes (
lakeflowDdlAudit_*,lakeflowDdlAuditTrigger_*,lakeflowCaptureInstanceInfo_*, et les procédures et Trigger associés) sont supprimés. - Le suivi des modifications et le CDC restent activés sur vos tables. La mise à niveau ne les désactive pas.
- Les instances de capture CDC ne sont pas affectées par le script de mise à niveau.
Après avoir exécuté le script de mise à niveau, réexécutez les procédures de configuration pour recréer les objets de support DDL pour la nouvelle version :
lakeflowSetupChangeTracking: RecréelakeflowDdlAudit_1_5et le Trigger d'audit DDL.lakeflowSetupChangeDataCapture: RecréelakeflowCaptureInstanceInfo_1_5et les procédures et le Trigger associés.
Les deux procédures sont idempotentes et peuvent être réexécutées en toute sécurité avec les parameters de configuration d'origine.
Schéma de gestion de versions : objectName_majorVersion_minorVersion. Les objets actuels utilisent le suffixe _1_5.
Bonnes pratiques
- Toujours exécuter en tant que
db_ownerou un utilisateur avec des privilèges équivalents. - Testez d'abord sur des bases de données de non-production.
- Utilisez l'approche hybride pour une couverture complète.
- Exécutez
lakeflowFixPermissionsaprès l'installation pour garantir un accès utilisateur approprié. - Considérez les périodes de rétention basées sur votre fréquence d'ingestion.
Dépannage
« L'utilisateur exécutant ce script n'est pas membre du rôle 'db_owner' »
Solution : exécutez en tant qu'utilisateur disposant du rôle db_owner
« Le suivi des modifications n'est pas activé sur le catalogue »
Solution : Activez le CT au niveau de la base de données ou laissez la procédure le gérer automatiquement
« La capture de données modifiées n'est pas activée sur le catalogue »
Solution : Activez la CDC au niveau de la base de données ou laissez la procédure le gérer automatiquement.
« Tables ignorées en raison de clés primaires manquantes »
**Solution** : utilisez lakeflowSetupChangeDataCapture à la place pour ces tables.
Intégration de la validation
Les objets d'infrastructures publiques suivants sont validés par le framework de validation Java :
Objet | Description |
|---|---|
| Valide l'installation des objets utilitaires. |
| Valide la configuration CT/CDC |
| Valide les objets de support DDL. |
| Valide les autorisations |
Notes de migration
Si vous effectuez une mise à niveau à partir d'anciennes versions d'objets de support DDL (ère pré-objets utilitaires) :
- Le script nettoie automatiquement les objets hérités.
- Aucun nettoyage manuel n'est requis.
- Version 1,1 consolide toutes les fonctionnalités en procédures unifiées.
Ressources supplémentaires
- Préparez SQL Server pour l'ingestion à l'aide du script d'objets utilitaires.
- 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
- À propos de la capture des modifications (SQL Server) dans la documentation SQL Server.
- Qu'est-ce que la capture de données modifiées (CDC) ? dans la documentation SQL Server