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.7
• Version principale : 1
• Version mineure : 7
• Fonction de version : lakeflowUtilityVersion()

Nouveautés de la version 1.7

Highlights

• Corrige un bug de perte de données dans l’évolution du schéma. Dans les versions antérieures, ajouter une colonne à une table qui possède également une instance de capture préexistante (non-Lakeflow) pouvait ingérer la nouvelle colonne sous forme NULLde . Cela est corrigé, ainsi que plusieurs échecs liés à l’évolution du schéma (voir Corrections de bugs).
• Les objets de support DDL sont désormais optionnels pour le suivi des changements. La table d’audit DDL et le déclencheur ne sont plus nécessaires pour l’évolution automatique du schéma et sont désactivés par défaut. Ne posez @CreateDdlSupportingObjects = 1 que si lakeflowSetupChangeTracking vous en voulez toujours.
• Évolution de schéma plus fiable. Les modifications de contraintes sont classées de sorte que celles non cassées, comme les clés étrangères, ne déclenchent plus de rafraîchissements complets inutiles.

Corrections de bugs

• Correction de plusieurs ADD COLUMN échecs d’évolution de schéma, notamment des tables utilisant le masquage dynamique des données, des collations sensibles à la casse ou binaires, ainsi qu’une boucle de réinitialisation ou une ingestion interrompue lorsqu’une instance de capture préexistante est présente.
• Correction des erreurs de conflit de collation sur les bases de données avec une collation serveur non par défaut.
• Correction des déclencheurs DDL qui pouvaient échouer lorsqu’on l’exécutait depuis une session avec des options non par défaut SET .
• Corrigé lakeflowFixPermissions pour accorder les autorisations requises à portée serveur depuis la base de données principale.
• Correction de la désinstallation (CLEANUP mode) laissant des déclencheurs DDL orphelins qui pourraient bloquer ALTER TABLE.

Autres modifications

• Ajouté @AllowDisablePreExistingCaptureInstances à lakeflowSetupChangeDataCapture. Régle-le pour 1 permettre à Lakeflow Connect de prendre le contrôle et de gérer l’instance de capture CDC préexistante (non Lakeflow) d’une table au lieu de la laisser en place.

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

Détecte la version des objets utilitaires.

Retourne : '1.7'

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

Permet le suivi des modifications au niveau de la base de données et des tables, avec un support optionnel DDL (opt-in).

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é
• Lorsque @CreateDdlSupportingObjects = 1, crée une table d’audit DDL versionnée (lakeflowDdlAudit_1_7) et un déclencheur pour capturer les modifications du 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_7)
• Crée des procédures d’assistance pour la gestion des instances de capture :
  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7
• 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 la dernière version du script :

   Télécharger utility_script.sql

2. Exécutez le script :

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

3. Vérifiez l’installation :

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion() 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

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

Créer des objets de support DDL

-- Enable CT and create the DDL audit table and trigger for schema-change capture
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser',
    @CreateDdlSupportingObjects = 1;

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

Prendre possession d’instances de capture préexistantes

-- Allow Lakeflow to disable pre-existing (non-Lakeflow) capture instances
-- during schema change handling
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser',
    @AllowDisablePreExistingCaptureInstances = 1;

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

Pour mettre à jour, relancez le script utilitaire des objets, puis relancez les procédures de configuration. Le script recrée les fonctions utilitaires et ne supprime que les objets hérités replicantavec préfixe -des versions précédentes. Cela laisse vos objets de support DDL Lakeflow 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. Pour les instructions, voir l’étape 1 : Installer ou mettre à jour des objets utilitaires.

Que se passe-t-il pendant une mise à niveau :

• Les fonctions utilitaires et les procédures d’installation définies par le script sont supprimées et recréées.
• Les objets hérités replicantavec préfixe - des versions antérieures sont supprimés.
• Les objets de support DDL Lakeflow existants (lakeflowDdlAudit_*, lakeflowDdlAuditTrigger_*, lakeflowCaptureInstanceInfo_*, et les procédures et déclencheurs associés) sont laissés en place. Relancer les procédures de configuration les fait passer à la nouvelle version. Les objets d’instance de capture bougent chaque fois que vous relancez lakeflowSetupChangeDataCapture. La table d’audit DDL et le déclencheur ne bougent que lorsque vous relancez lakeflowSetupChangeTracking avec @CreateDdlSupportingObjects = 1, car ils sont volontaires.
• Le suivi des modifications et la capture de données modifiées 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 d’installation pour recréer des objets de prise en charge DDL pour la nouvelle version :

• lakeflowSetupChangeTracking: avec @CreateDdlSupportingObjects = 1, porte la table d’audit DDL et déclenche le transfert vers lakeflowDdlAudit_1_7. Passez ce drapeau si votre installation utilise la capture de changement de schéma DDL (elle a une lakeflowDdlAudit table), ou si les objets restent sur l’ancienne version.
• lakeflowSetupChangeDataCapture: recrée et les procédures et déclencheurs lakeflowCaptureInstanceInfo_1_7 associés.

Les deux procédures sont idempotentes et sécurisées à réexécuter avec les paramètres d’installation d’origine.

Schéma de contrôle de version : objectName_majorVersion_minorVersion. Les objets actuels utilisent le _1_7 suffixe.

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