Redgate Hub

SQL Compare and Database Builds and Deployments

SQL Compare est désormais proche de ses 20 ans et a été utilisé de différentes manières que ce que les développeurs originaux de l’outil pouvaient imaginer. Après un bref rappel de ce que c’est et comment ça fonctionne, je vais passer en revue comment comparer des bases de données SQL avec SQL Compare, et comment cela rend les tâches de développement courantes suivantes plus faciles, moins sujettes aux erreurs et plus rapides :

Mettre à jour une base de données cible pour qu’elle soit identique à la source
Mettre à jour un sous-ensemble d’une base de données
Créer ou mettre à jour un répertoire de scripts à partir d’une base de données
Créer ou mettre à jour un script de création de base de données
Créer un “instantané” d’une base de données à un moment précis
Générer un script de migration de schéma pour les déploiements

Table des matières

Qu’est-ce que SQL Compare ?

SQL Compare est un outil graphique pour comparer des bases de données SQL Server. Il inspecte les différences structurelles entre les bases de données source et cible, et présente les différences côte à côte dans le code SQL DDL de chacune des tables et des modules de code. Il génère un script de déploiement (également appelé script de synchronisation unilatérale ou de migration) qui mettra à jour la base de données cible pour la rendre identique à la source, en préservant les données existantes dans la mesure du possible et en émettant un avertissement si ce n’est pas possible. Vous pouvez utiliser les options et les filtres de SQL Compare pour contrôler comment cette comparaison est effectuée et quels objets elle ignore.

SQL Compare offre une interface en ligne de commande (qui peut être soumise à une licence différente basée sur l’automatisation), à la fois pour Windows et Linux. Elle vous permet d’effectuer les comparaisons et les déploiements dans le cadre d’un processus scripté. Lorsqu’elle est utilisée en ligne de commande ou dans un script, SQL Compare peut fournir un rapport au format HTML ou Excel.

Le moteur de comparaison de SQL Compare est également intégré à SQL Change Automation et SQL Source Control.

Comment fonctionne SQL Compare ?

SQL Compare compare les schémas entre les bases de données, en particulier entre deux versions différentes de la même base de données SQL Server. La source et la cible peuvent être des bases de données SQL Server “live”, mais ce n’est pas une obligation car SQL Compare peut fonctionner avec n’importe quelle représentation d’une base de données à partir de laquelle il peut construire un “modèle” de la structure de cette base de données. Par exemple, il peut analyser un répertoire de scripts contenant un ensemble de scripts DLL pour chaque objet de la base de données, en un modèle de la base de données, ou même un seul script de création. De même, il peut fonctionner avec un instantané SQL Compare ou un projet de contrôle de source SQL ou un fichier de projet SQL Change Automation.

Lorsque la comparaison est lancée, SQL Compare génère une liste d’objets de base de données qui existent uniquement dans la source, qui existent uniquement dans la cible, ou qui existent dans les deux mais qui présentent des différences. Il affiche les différences en termes de métadonnées pour chaque objet, sous forme de script, et peut générer automatiquement le script de déploiement (ou “migration de schéma”) qui mettra à jour la cible pour que sa structure soit identique à celle de la source.

Si la cible est une base de données, le script créera les objets qui n’existent que dans la source, supprimera les objets qui n’existent que dans la cible, et mettra à jour tout objet qui est présent dans les deux de manière à ce que sa définition corresponde à celle de la source. De même, si la cible est un répertoire de code DDL existant, les fichiers seront ajoutés, supprimés ou modifiés pour refléter la base de données source. Dans chaque cas, SQL Compare émettra un avertissement pour toute situation susceptible de poser problème, y compris les cas où il ne peut pas conserver les données existantes. Vous pouvez inspecter le script de migration résultant et le modifier si nécessaire, avant de l’appliquer à la cible.

SQL Compare propose des filtres pour contrôler quels objets sont inclus dans la comparaison, ainsi que diverses options qui contrôlent la manière dont la comparaison et le scriptage ultérieur sont effectués.

Le script de migration de schéma généré automatiquement par SQL Compare s’exécute dans une transaction et se rollback automatiquement en cas d’erreur. Vous serez averti si certains objets, tels que les index plein texte, ne peuvent pas être déployés dans une transaction, et ils seront déployés séparément.

Les tâches délicates de développement de bases de données auxquelles SQL Compare s’attaque

Le diagramme suivant illustre bon nombre des tâches que SQL Compare peut effectuer dans le cadre du travail de développement, telles que générer le script de migration pour amener la base de données cible de sa version actuelle à la version source, ou créer un script de création pour créer la version actuelle à partir de zéro. Quelle que soit la source et la cible, SQL Compare offre toujours la possibilité de générer et d’enregistrer le script généré automatiquement qu’il va utiliser pour mettre à jour la cible, que vous pouvez ensuite adapter en fonction des besoins.

1. Mettre à jour une base de données cible pour qu’elle soit identique à la source

Avec l’une des sources de données mentionnées précédemment, et comme indiqué dans le diagramme ci-dessus, SQL Compare va construire un “modèle” de cette base de données, le comparer à une base de données cible, et générer un script de migration pour mettre à jour les métadonnées de la cible de manière à ce qu’elle soit identique à la source. Cela présente des avantages évidents pendant le développement ; par exemple, vous pouvez mettre à niveau la version actuelle de toutes les bases de données sur le serveur de test vers la version candidate.

Le script de migration automatique de SQL Compare conservera les données existantes dans la cible, dans la mesure du possible, lors du déploiement des modifications. Cependant, si l’ampleur des différences entre la source et la cible est telle que SQL Compare ne peut pas comprendre ce qui s’est passé ou où les données doivent être déplacées, vous devrez intervenir. Si vous utilisez un répertoire de scripts pour représenter la base de données source, vous pouvez écrire des scripts personnalisés pour gérer ces problèmes de migration, et SQL Compare les ajoutera automatiquement au début ou à la fin de son script généré automatiquement. Consultez la section “Générer un script de migration pour les déploiements”.

Si la cible est une base de données vide, la comparaison se fait essentiellement entre la source et la représentation interne de la base de données modèle de SQL Compare, dans SQL Server, et le résultat est un script de création pour la base de données source. Consultez la section “Créer ou mettre à jour un script de création de base de données”.

2. Mettre à jour un sous-ensemble d’une base de données

Vous pouvez également comparer uniquement certaines parties d’une base de données. Par exemple, vous pouvez comparer les schémas entre les bases de données (ou un groupe de schémas), ou comparer les tables entre les bases de données, ou d’autres types d’objets, tels que les procédures stockées. Vous pouvez même comparer un seul objet nommé, tel qu’une vue (ainsi que les tables dont elle dépend).

Les nombreuses options de comparaison de SQL Compare permettent de contrôler la manière dont SQL Compare effectue cette comparaison, la façon dont il compare les objets de base de données et le type de script qu’il génère. Vous pouvez également utiliser ses filtres pour exclure certaines classes d’objets, tels que les utilisateurs de bases de données, ou des objets de base de données spécifiques ou des groupes d’objets, tels que tous les objets d’un schéma, de la comparaison.

Par exemple, vous avez peut-être simplement besoin de modifier et de tester une seule vue dans la base de données, mais cette vue dépend de 20 tables. Avec SQL Compare, vous sélectionnez simplement la vue requise dans la source, en laissant la cible vide. Du moment que vous n’oubliez pas de sélectionner l’option “Inclure toutes les dépendances”, il générera le script de la vue et de toutes ses dépendances, vous permettant de construire, de modifier et de tester uniquement cette partie de la base de données.

3. Créer ou mettre à jour un répertoire de scripts à partir d’une base de données

Si la cible d’une comparaison est un répertoire vide du système de fichiers, et que vous choisissez de permettre à SQL Compare de mettre à jour directement le répertoire de scripts, le résultat sera un ensemble ordonné de scripts CREATE, un pour chaque objet de la base de données source. Vous aurez donc la source de chaque table et routine sous la forme d’un fichier de script séparé, les scripts étant organisés en sous-répertoires selon le type d’objet de base de données.

Au fur et à mesure que vous apportez des modifications ultérieures à la base de données source, vous pouvez relancer la comparaison vers le répertoire de scripts cible, voir rapidement les différences entre la nouvelle version et les scripts source, et mettre à jour ces derniers pour refléter les modifications. Cela est pratique si vous devez suivre les modifications de votre base de données en dehors du contrôle de version, par exemple dans la phase de “proof of concept” d’un projet.

Si vous utilisez un répertoire de scripts source contrôlé comme cible, vous disposez alors d’un moyen rapide de placer une base de données sous contrôle de version :

Obtenir une copie “de référence” d’une base de données dans le système de développement, par exemple une copie de référence de la version actuelle de la base de données de mise en scène ou de production. Voir la rétro-ingénierie de la base de données de production dans le contrôle de source.
Mettre une base de données existante, pour laquelle il n’existe pas ou peu de source, sous contrôle de version. Voir le contrôle de source de base de données rétrospectif avec SQL Compare.
Détecter les “écarts” de base de données entre différentes versions ou versions d’une base de données. Par exemple, vous pouvez détecter tous les écarts entre la base de données de production actuelle et celle du contrôle de source, simplement en comparant avec le répertoire de scripts. Voir comment gérer la dérive de la base de données lors du déploiement à l’aide de SQL Compare.

SQL Compare peut créer ou mettre à jour un répertoire de scripts, même si la source est un simple script de création unique, tel que celui généré dans SSMS. Par exemple, supposons que vous souhaitez placer une version fonctionnelle de la base de données de mise en scène sous contrôle de version. Le schéma suivant permettrait d’y parvenir :

Utilisez SSMS pour générer un script de création de base de données (par exemple, Tâches -> Générer des scripts).
Testez-le pour vérifier que vous pouvez construire la base de données.
Enregistrez une copie du script de création vérifié dans un répertoire vide sur votre serveur de développement.
Utilisez SQL Compare pour comparer le répertoire de scripts de création en tant que source avec un répertoire vide dans votre dépôt de contrôle de version local.

Vous pouvez voir des avertissements indiquant qu’une “instruction sans schéma a été ignorée”, mais si vous persévérez et cliquez sur “Continuer sans résoudre les erreurs”, SQL Compare devrait générer le répertoire de code DDL habituel, contenant tous les objets de la base de données de mise en scène, avec les différents types d’objets placés dans des sous-répertoires.

Si la cible est un répertoire de scripts existant, SQL Compare générera sa liste d’objets dans la source qui sont différents de ceux dans la cible, à partir de laquelle vous pouvez générer le script pour déployer ces différences.

Enfin, si la source est un répertoire de scripts chaotique pour une base de données héritée et que vous avez placé tous les fichiers dans un seul répertoire, SQL Compare peut le comparer à un répertoire de scripts (ou une base de données) vide, ou vous pouvez utiliser l’option /empty2 dans la ligne de commande de SQL Compare. Vous devriez obtenir des scripts d’objets organisés de manière ordonnée.

4. Créer ou mettre à jour un script de création de base de données

Si la cible d’une comparaison est un répertoire vide du système de fichiers, et que vous choisissez de générer un script de déploiement, au lieu de permettre à SQL Compare de mettre à jour directement le répertoire de scripts, le résultat sera un seul script de création. SQL Compare peut également produire un script de création de document unique à partir d’une source “chaotiquement organisée”, comme décrit dans la section précédente.

Le script de création créera tous les objets de base de données, dans l’ordre des dépendances correctes, en amenant la base de données de “zéro” à la version requise. Si vous devez créer ce script de création manuellement, cela est plus difficile qu’il n’y paraît. La construction échouera si vous ne construisez pas les objets dans le bon ordre. Les types de données définis par l’utilisateur, par exemple, doivent être en place avant les tables ; les fonctions et les procédures ne peuvent pas être créées tant que les objets auxquels elles font référence n’existent pas ; les tables doivent être construites dans un certain ordre, en fonction des références et des contraintes existantes. L’utilisation de SQL Compare vous fera gagner beaucoup de temps.

Si la cible d’une comparaison est un répertoire de scripts contenant un seul script de création, et que la source est un script de création, vous pouvez simplement mettre à jour le script de création existant pour qu’il corresponde à la source. Consultez Maintaining a Database Build Script with SQL Compare pour un exemple.

5. Créer un “instantané” d’une base de données à un moment précis

Un instantané SQL Compare est simplement une copie à un instant précis des métadonnées d’une base de données. Il s’agit d’un fichier contenant une représentation interne des métadonnées, telle que produite par un analyseur. Il ne contient aucune donnée.

Un instantané ne peut pas être modifié et représente donc une vue stable de la structure de la base de données, telle qu’elle existait lors de sa création. La source pour le nouvel instantané peut être une base de données, un autre instantané, un répertoire de contrôle de source ou un simple répertoire de scripts.

Vous pouvez l’utiliser dans les comparaisons de la même manière qu’une base de données normale. Par exemple, avec un instantané en tant que source, SQL Compare le comparera à la cible et générera automatiquement un script de déploiement pour que l’état de la cible corresponde à celui de l’instantané.

Ils ont de nombreuses utilisations possibles. Par exemple :

Comparer des bases de données en direct avec des instantanés pour savoir ce qui a changé, puis enregistrer les changements.
Comparer deux bases de données si vous n’avez pas un accès direct à l’une ou à l’autre, ou comparer différentes versions plus anciennes d’une base de données pour voir ce qui a changé.
Capturer l’état de la base de données avant et après un refactorage, puis exécuter une comparaison pour déterminer ce qui a causé des problèmes si le changement a entraîné l’échec de certains tests.
Utiliser un instantané comme une technique simple de retour à la version précédente pour revenir rapidement à la dernière copie de travail. Voir Les instantanés SQL Compare : un mécanisme léger de contrôle de version et de retour arrière de base de données.

6. Générer un script de migration pour les déploiements

Les migrations de bases de données, plutôt que des créations simples, deviennent nécessaires lorsque l’on doit mettre à niveau un système de base de données de production, in situ, de manière à garantir la préservation des données et à ne pas perturber le service. Lors de la test d’un candidat à la version de diffusion, par conséquent, avant qu’il n’entre dans le processus de déploiement (UAT, Mise en scène, etc.), l’équipe doit créer et tester soigneusement le script de migration qui mettra à jour la version de production actuelle vers la nouvelle version.

Le script de migration automatique de SQL Compare conservera les données dans la mesure du possible lors du déploiement des modifications sur la cible, et réussira souvent, surtout si les bases de données sont structurellement similaires. Il échouera seulement lorsqu’il sera confronté à une réingénierie du schéma qui signifie qu’il n’a aucun moyen de faire correspondre les tables et les colonnes dans la source et la cible, et qu’il ne peut donc pas conserver les données. Cela peut se produire si les tables ou leurs composants sont renommés, ou si les tables sont divisées, si les types de données sont modifiés, si des contraintes sont ajoutées ou modifiées, etc.

Les équipes qui évitent de traiter ces problèmes de migration ou qui espèrent que l’outil SQL Compare “fera simplement le travail” constateront que les “breaking changes” s’accumulent, que les déploiements deviennent intimidants et que les versions sont retardées. Au lieu de cela, vous devez “mettre un drapeau” sur les changements de schéma qui pourraient affecter les données existantes de manière imprévisible, dès le début du développement. Pour ces cas, vous fournissez une aide à SQL Compare.

Pour les ajustements simples, vous pouvez modifier le script généré automatiquement par SQL Compare, mais dans d’autres cas, vous devrez ajouter des scripts personnalisés pour gérer les problèmes de migration. Lorsque vous utilisez un répertoire de scripts comme source, SQL Compare peut exécuter automatiquement un script personnalisé de pré-déploiement (qui doit être placé dans un sous-répertoire “Custom Scripts/Pre-Deployment”), utilisé pour modifier la base de données cible après la génération de la partie auto-générée du script de migration, mais avant son exécution, et un script personnalisé de post-déploiement (sous-répertoire “Custom Scripts/Post-Deployment”), pour modifier la cible après l’exécution du script de migration automatique :

Vous pouvez gérer un certain nombre de migrations de cette manière. Par exemple, si vous refactorisez des tables, il peut être possible d’écrire un script de pré-déploiement pour extraire les données d’une colonne qui va être supprimée, les sauvegarder dans une table temporaire, puis utiliser un script de post-déploiement pour distribuer les données existantes dans la nouvelle conception de la table. Ou, si vous avez modifié les définitions de contraintes et que des données existantes enfreignent les nouvelles règles, vous pouvez déployer les nouvelles contraintes dans un état “désactivé” initialement, puis utiliser un script de post-déploiement pour corriger les données, puis les activer. Pour d’autres exemples, consultez l’utilisation de scripts de déploiement personnalisés avec SQL Compare ou SQL Change Automation.

Cependant, il faut faire preuve de prudence lors de la modification du processus de déploiement automatique de SQL Compare. Vous devrez “respecter les règles” lors de la rédaction des scripts personnalisés, en vous assurant qu’ils sont idempotents et en gérant vous-même toutes les transactions, afin de pouvoir les annuler en toute sécurité en cas de problème. Si vous devez apporter des modifications à la cible pour la mettre dans l’état requis avant l’exécution de la comparaison par SQL Compare, la seule façon de le faire consiste à exécuter les scripts de migration requis au préalable, en tant que processus séparé.

Dans l’ensemble, il peut être difficile de s’assurer que ce type de déploiement partiellement personnalisé, partiellement auto-généré est toujours sûr, même s’il est accidentellement exécuté sur la mauvaise version de la base de données cible. Si vous rencontrez fréquemment des blocages de déploiement en raison de problèmes avec le processus de déploiement auto-généré de SQL Compare, il est probablement plus prudent d’utiliser un outil de déploiement basé sur les migrations comme SQL Change Automation pour développer un script spécifiquement conçu pour mettre à niveau une base de données cible, à une version spécifique, vers la nouvelle version.

Références :

Documentation :

Documentation de SQL Compare

Formations Redgate University :

Vidéos de formation sur les outils de comparaison SQL

Articles d’apprentissage sur les produits Redgate connexes (non mentionnés dans le corps de cet article) :

GUIDE