Comment gérer correctement le déploiement des bases de données avec SSDT et Visual Studio 2012 Database Projects ?

Question

Je suis en phase de recherche pour essayer d'adopter les projets de base de données 2012 sur un petit projet existant. Je suis un développeur C#, pas un DBA, donc je ne suis pas particulièrement à l'aise avec les meilleures pratiques. J'ai fait des recherches sur Google et stackoverflow pendant quelques heures maintenant, mais je ne sais toujours pas comment gérer correctement certains scénarios de déploiement clés.

1) Au cours de plusieurs cycles de développement, comment puis-je gérer plusieurs versions de ma base de données ? Si j'ai un client qui utilise la version 3 de ma base de données et que je souhaite le faire passer à la version 8, comment dois-je procéder ? Nous gérons actuellement des scripts de migration des schémas et des données élaborés à la main pour chaque version de notre produit. Devons-nous continuer à le faire séparément ou y a-t-il quelque chose dans le nouveau paradigme qui supporte ou remplace cela ?

2) Si le schéma change de telle manière que les données doivent être déplacées, quelle est la meilleure façon de gérer cela ? Je suppose qu'un certain travail va dans le script pré-déploiement pour préserver les données, puis le script post-déploiement les remet au bon endroit. Est-ce que c'est comme ça ou y a-t-il quelque chose de mieux ?

3) Tout autre conseil ou orientation sur la meilleure façon de travailler avec ces nouvelles technologies est également très apprécié !

UPDATE : Ma compréhension du problème a évolué depuis que j'ai posé cette question et, bien que j'aie trouvé une solution viable, ce n'est pas tout à fait celle que j'espérais. Voici une nouvelle formulation de mon problème :

Le problème que je rencontre est purement lié aux données. Si j'ai un client qui utilise la version 1 de mon application et que je veux le mettre à niveau vers la version 5 de mon application, je n'aurais aucun problème à le faire si sa base de données ne contenait aucune donnée. Je laisserais simplement SSDT comparer intelligemment les schémas et migrer la base de données en une seule fois. Malheureusement, les clients ont des données et ce n'est pas aussi simple. Les changements de schéma de la version 1 de mon application à la version 2 puis à la version 3 (etc.) ont tous un impact sur les données. Ma stratégie actuelle de gestion des données m'oblige à maintenir un script pour chaque mise à jour de version (1 à 2, 2 à 3, etc). Cela m'empêche de passer directement de la version 1 de mon application à la version 5 car je n'ai pas de script de migration de données pour y aller directement. La perspective de créer des scripts de mise à niveau personnalisés pour chaque client ou de gérer des scripts de mise à niveau pour passer de chaque version à chaque version supérieure est exponentiellement ingérable. Ce que j'espérais, c'était qu'il y avait une sorte de stratégie que SSDT permettait de mettre en œuvre et qui rendait la gestion des données plus facile, peut-être même aussi facile que la gestion des schémas. Ma récente expérience avec SSDT ne m'a pas donné l'espoir qu'une telle stratégie existe, mais j'aimerais bien découvrir le contraire.

Answer 1

J'y ai travaillé moi-même, et je peux vous dire que ce n'est pas facile.

Tout d'abord, pour répondre à la réponse de JT - vous ne pouvez pas ignorer les "versions", même avec les mécanismes de mise à jour déclarative que SSDT possède. SSDT fait un travail "assez décent" (à condition que vous connaissiez tous les commutateurs et les pièges) pour déplacer n'importe quel schéma source vers n'importe quel schéma cible, et il est vrai que cela ne nécessite pas de version en soi, mais il n'a aucune idée de la façon de gérer le "mouvement des données" (du moins pas à ma connaissance !). Donc, tout comme DBProj, vous êtes laissé à vos propres moyens dans les scripts Pre/Post. Parce que les scripts de mouvement de données dépendent d'un état de schéma de début et de fin connu, vous ne pouvez pas éviter de versionner la BD. Les scripts "data motion" doivent donc être appliqués à un instantané versionné du schéma, ce qui signifie que vous ne pouvez pas arbitrairement mettre à jour une BD de v1 à v8 et espérer que les scripts data motion v2 à v8 fonctionnent (vraisemblablement, vous n'auriez pas besoin d'un script data motion v1).

Malheureusement, je ne vois aucun mécanisme dans la publication SSDT qui me permette de gérer ce scénario de manière intégrée. Cela signifie que vous devrez ajouter votre propre scafolding.

La première astuce consiste à suivre les versions au sein de la base de données (et du projet SSDT). J'ai commencé à utiliser une astuce dans DBProj, et je l'ai transposée dans SSDT, et après avoir fait quelques recherches, il s'avère que d'autres l'utilisent aussi. Vous pouvez appliquer une propriété étendue DB à la base de données elle-même (appelez-la "BuildVersion" ou "AppVersion" ou quelque chose comme ça), et lui appliquer la valeur de la version. Vous pouvez ensuite capturer cette propriété étendue dans le projet SSDT lui-même, et SSDT l'ajoutera comme un script (vous pouvez alors cocher l'option de publication qui inclut les propriétés étendues). J'utilise ensuite les variables SQLCMD pour identifier les versions source et cible appliquées dans la passe actuelle. Une fois que vous avez identifié le delta des versions entre la source (snapshot du projet) et la cible (db cible sur le point d'être mis à jour), vous pouvez trouver tous les snapshots qui doivent être appliqués. Malheureusement, ceci est délicat à faire à partir de à l'intérieur de le déploiement SSDT, et vous devrez probablement le déplacer vers le pipeline de construction ou de déploiement (nous utilisons les déploiements automatisés TFS et avons des actions personnalisées pour le faire).

L'étape suivante consiste à conserver des instantanés du schéma avec les scripts de mouvement de données associés. Dans ce cas, il est utile de rendre les scripts aussi idempotents que possible (ce qui signifie que vous pouvez réexécuter les scripts sans effets secondaires indésirables). Il est utile de séparer les scripts qui peuvent être réexécutés en toute sécurité des scripts qui doivent être exécutés une seule fois. Nous faisons la même chose avec les données de référence statiques (dictionnaire ou tables de consultation) - en d'autres termes, nous avons une bibliothèque de scripts MERGE (un par table) qui maintiennent la synchronisation des données de référence, et ces scripts sont inclus dans les scripts post-déploiement (via la commande SQLCMD :r). La chose importante à noter ici est que vous doit les exécuter dans l'ordre correct au cas où l'une de ces tables de référence aurait des références FK entre elles. Nous les incluons dans l'ordre dans le script principal post-déploiement, et cela aide que nous ayons créé un outil qui génère ces scripts pour nous - il résout également l'ordre des dépendances. Nous exécutons cet outil de génération à la clôture d'une "version" pour capturer l'état actuel des données de référence statiques. Tous vos autres scripts de mouvement de données seront essentiellement des cas particuliers et très probablement à usage unique. Dans ce cas, vous pouvez faire l'une des deux choses suivantes : vous pouvez utiliser une instruction IF par rapport à la version du db build/app, ou vous pouvez effacer les scripts à usage unique après avoir créé chaque paquet d'instantanés.

Il est utile de se rappeler que SSDT désactivera les contraintes de contrôle FK et ne les réactivera qu'après l'exécution des scripts post-déploiement. Cela vous donne la possibilité de remplir de nouveaux champs non nuls, par exemple (en passant, vous devez activer l'option de génération de valeurs par défaut "intelligentes" temporaires pour les colonnes non nulles pour que cela fonctionne). Cependant, les contraintes de contrôle FK ne sont désactivées que pour les tables que SSDT recrée en raison d'un changement de schéma. Pour les autres cas, vous devez vous assurer que les scripts de mouvement de données sont exécutés dans le bon ordre pour éviter les plaintes relatives aux contraintes de contrôle (ou vous devez les désactiver/activer manuellement dans vos scripts).

DACPAC peut vous aider car DACPAC est essentiellement un instantané. Il contient plusieurs fichiers XML décrivant le schéma (similaire à la sortie de construction du projet), mais figé dans le temps au moment où vous le créez. Vous pouvez ensuite utiliser SQLPACKAGE.EXE ou le fournisseur de déploiement pour publier cet instantané de paquet. Je n'ai pas encore trouvé comment utiliser le versioning de DACPAC, car il est plus lié aux applications de données "enregistrées", donc nous sommes coincés avec notre propre schéma de versioning, mais nous mettons nos propres informations de version dans le nom de fichier DACPAC.

J'aimerais avoir un exemple plus concluant et plus probant à fournir, mais nous sommes encore en train de régler les problèmes ici aussi.

Une chose qui craint vraiment à propos de SSDT est que, contrairement à DBProj, il n'est actuellement pas extensible. Bien qu'il fasse un bien meilleur travail que DBProj pour beaucoup de choses différentes, vous ne pouvez pas modifier son comportement par défaut à moins que vous ne trouviez une méthode dans les scripts pré/post pour contourner un problème. L'un des problèmes que nous essayons de résoudre en ce moment est que la méthode par défaut de recréation d'une table pour les mises à jour (CCDR) est vraiment nulle lorsque vous avez des dizaines de millions d'enregistrements.

MISE À JOUR : Je n'ai pas vu ce message depuis un certain temps, mais apparemment il a été actif dernièrement, alors j'ai pensé ajouter quelques notes importantes : si vous utilisez VS2012, la version de juin 2013 de SSDT a maintenant un outil de comparaison de données intégré, et fournit également des points d'extensibilité - c'est-à-dire que vous pouvez maintenant inclure des contributeurs de construction et des modificateurs de plan de déploiement pour le projet.

Answer 2

Je n'ai pas vraiment trouvé d'autres informations utiles sur le sujet, mais j'ai passé du temps à me familiariser avec les outils, à bricoler et à jouer, et je pense avoir trouvé des réponses acceptables à ma question. Ce ne sont pas nécessairement les meilleures réponses. Je ne sais toujours pas s'il existe d'autres mécanismes ou de meilleures pratiques pour mieux prendre en charge ces scénarios, mais voici ce que j'ai trouvé :

Les scripts Pre- et Post-Deploy pour une version donnée de la base de données servent uniquement à migrer les données de la version précédente. Au début de chaque cycle de développement, les scripts sont nettoyés et, au fur et à mesure que le développement avance, ils sont étoffés avec tout le code SQL nécessaire pour migrer en toute sécurité les données de la version précédente vers la nouvelle. La seule exception ici concerne les données statiques de la base de données. Ces données sont connues au moment de la conception et sont présentes en permanence dans les scripts post-déploiement sous la forme d'instructions T-SQL MERGE. Cela permet de déployer n'importe quelle version de la base de données dans un nouvel environnement avec simplement le dernier script de publication. A la fin de chaque cycle de développement, un publish script est généré de la version précédente vers la nouvelle. Ce script comprendra le sql généré pour migrer le schéma et les scripts de déploiement élaborés à la main. Oui, je sais que l'outil Publish peut être utilisé directement contre une base de données, mais ce n'est pas une bonne option pour nos clients. Je connais également les fichiers dacpac mais je ne sais pas vraiment comment les utiliser. La publication générée script semble être la meilleure option que je connaisse pour les mises à niveau de production.

Donc, pour répondre à mes scénarios :

1) Pour mettre à niveau une base de données de la v3 à la v8, il me faudrait exécuter le publish script généré pour la v4, puis pour la v5, puis pour la v6, etc. C'est très similaire à la façon dont nous le faisons maintenant. C'est bien compris et les projets de base de données semblent rendre la création/maintenance de ces scripts beaucoup plus facile.

2) Lorsque le schéma change par rapport aux données sous-jacentes, les scripts Pre- et Post-Deploy sont utilisés pour migrer les données là où elles doivent aller pour la nouvelle version. Les données affectées sont essentiellement sauvegardées dans le script de Pre-Deploy et remises en place dans le script de Post-Deploy.

3) Je suis toujours à la recherche de conseils sur la meilleure façon de travailler avec ces outils dans ces scénarios et dans d'autres. Si je me suis trompé ou s'il y a d'autres problèmes que je devrais connaître, faites-le moi savoir ! Merci !

Answer 3

D'après mon expérience de l'utilisation de SSDT, la notion de numéro de version (i.e. v1, v2...vX etc...) pour les bases de données disparaît en quelque sorte. Cela est dû au fait que SSDT offre un paradigme de développement connu sous le nom de développement déclaratif de base de données, ce qui signifie en gros que vous dites à SSDT dans quel état vous voulez que votre schéma soit et que vous laissez SSDT se charger de le mettre dans cet état en le comparant à ce que vous avez déjà. Dans ce paradigme, la notion de déploiement de la v4 puis de la v5 etc.... disparaît.

Vos scripts pré et post-déploiement, comme vous l'indiquez à juste titre, existent dans le but de gérer les données.

J'espère que cela vous aidera.

JT

Answer 4

Je voulais juste dire que ce fil de discussion a été excellent jusqu'à présent.

Je suis aux prises avec les mêmes préoccupations et je tente de résoudre ce problème dans notre organisation, sur une application patrimoniale assez importante. Nous avons entamé le processus de migration vers SSDT (sur une branche TFS) mais nous en sommes au point où nous avons vraiment besoin de comprendre le processus de déploiement, et de gérer les migrations personnalisées, et les données de référence/de consultation, en cours de route.

Pour compliquer encore les choses, notre application est une base de code unique mais peut être personnalisée par "client", ce qui fait que nous avons affaire à environ 190 bases de données, pour ce seul projet, et pas seulement à 3 ou plus comme c'est probablement la norme. Nous effectuons des déploiements en permanence et nous configurons même de nouveaux clients assez souvent. Nous nous appuyons fortement sur PowerShell maintenant avec les scripts de version incrémentale de la vieille école (et les scripts associés pour créer un nouveau client à cette version). Je prévois de contribuer une fois que nous aurons résolu tout cela, mais je vous invite à partager tout ce que vous avez appris. Je crois effectivement que nous finirons par maintenir des scripts de libération personnalisés par version, mais nous verrons. L'idée de maintenir chaque script dans le projet, et d'inclure une variable SqlCmd From et To est très intéressante. Si nous faisions cela, nous élaguerions probablement en cours de route, en supprimant physiquement les scripts de mise à niveau vraiment anciens une fois que tout le monde a dépassé cette version.

À propos de la réduction des déchets, nous venons également de passer beaucoup de temps à trouver un moyen d'automatiser l'application des conventions de nommage/type de données pour les colonnes, ainsi que la génération automatique de toutes les clés primaires et étrangères, sur la base des conventions de nommage, ainsi que des contraintes d'indexation et de vérification, etc. La partie la plus difficile a été de traiter les "déviants" qui ne suivaient pas les règles. Peut-être que je partagerai cela aussi un jour si cela intéresse quelqu'un, mais pour l'instant, je dois poursuivre cette histoire de déploiement, de migration et de données de référence de manière intensive. Merci encore. C'est comme si vous disiez exactement ce que j'avais en tête et ce que je cherchais ce matin.

Answer 5

Parlez-vous de changements dans les données de consultation/référence ou dans les données transactionnelles ?

Pour les données de consultation/référence, vous pouvez inclure un outil de comparaison de données dans le processus. En utilisant l'outil Red Gate SQL Source Control par exemple, vous pouvez inclure des données de consultation/référence dans le contrôle de la source, afin d'obtenir l'historique des modifications. Les données sont contrôlées à la source simplement sous forme d'instructions d'insertion, ce qui permet une bonne lisibilité, mais peut ralentir si vous avez de très grandes quantités de données. Vous pouvez sélectionner les tables qui sont liées pour les données.

Pour les données transactionnelles, il est évident qu'un outil de comparaison de schémas préservera les données existantes et émettra des avertissements/erreurs si les changements de schémas entraînent une perte de données. Pour les changements qui entraîneraient une perte de données, ou simplement les transformations de données que vous devez effectuer, vous devez être en mesure d'utiliser le SQL personnalisé. L'utilisation de scripts pré et post SQL peut être utile dans ce cas, mais si vous voulez que le processus soit aussi automatisé que possible, il est utile de pouvoir remplacer uniquement les changements sélectionnés et de laisser le moteur de comparaison faire son travail pour le reste.

Pour améliorer encore le processus de déploiement, vous pouvez envisager d'utiliser un outil de gestion des versions, pour orchestrer les choses et vous donner une visibilité sur ce qui a été déployé. Cela signifie également que vous pouvez déployer votre application et votre base de données de la même manière, en vous dirigeant vers la livraison continue. L'outil Deployment Manager de Red Gate est l'un de ces exemples, qui intègre une prise en charge des bases de données, basée sur SQL Compare.

[Avertissement : je travaille pour Red Gate]