Introduction

Avec l’avènement des ORM tels qu’Entity Framework, les développeurs oublient souvent les problématiques liées aux développements de base de données. En effet les ORM et outils similaires s’occupent souvent de générer le schéma (tables, colonnes) de la base de données ainsi que les requêtes SQL sans que le développeur n’écrive une seule ligne de SQL. Bien que cela soit très pratique, une base de données ne se limite pas seulement à cela. On y trouve des procédures stockées, des fonctions CLR, des Queues (Service Broker), de la configuration (niveau de compatibilité, collation, snapshot isolation, etc. ), et plein d’autres choses.

Tous ces objets changent au fur et à mesure de l’évolution de l’application que l’on développe. Il est donc important, tout comme pour le code applicatif, d’avoir un référentiel de la base de données (autre que l’environnement de production de préférence) et une gestion des versions. De plus il faut être capable facilement de déployer ce référentiel vers l’environnement cible. Dans cet article nous allons voir comment le projet SQL Server Database Project peut répondre à cette problématique.

Intégré à Visual Studio

Ce projet est intégré à Visual Studio. Cela a de nombreux avantages :

• Tout le code de l’application est au même endroit (C# + SQL + autres)
• On profite de toutes les fonctionnalités de source control (TFS ou Git)
• Le projet est intégré au processus de build y compris pour les builds automatiques (intégration continue)
• Il s’agit d’un environnement familier pour de nombreuses personnes

Importer une base de données ou un script SQL

Il est possible d’initier le projet à partir d’une base de données. Ainsi même si vous n’utilisez pas ce type de projet pour créer votre base de données, vous pouvez commencer à profiter de toutes les fonctionnalités qu’il propose en quelques secondes. De même si vous avez déjà un script contenant des instructions SQL pour la création des objets, il sera parsé et chaque instruction CREATE sera extraite et placée dans un script distinct.

Plateforme cible et paramétrage de la base de données

On peut choisir la version cible de SQL Server (Azure compris). Cela permet de modifier le comportement de l’auto-complétion et de la validation des erreurs. En effet si on sélectionne SQL Server 2005, les nouvelles fonctionnalités (Table In-Memory, Sequences, etc. ) ne seront pas autorisées. On s’assure donc que le code que l’on écrit fonctionnera sur la version ciblée.

On peut également modifier le paramétrages de la base de données. On s’assure ainsi que tous les paramètres seront identiques quel que soit le serveur sur lequel la base de données sera déployée :

Modèle déclaratif

Un modèle de développement déclaratif est utilisé. Cela signifie que vous décrivez comment la base doit être, et non comment obtenir cet état. Cela se traduit par le fait que tous les objets sont sauvegardés avec des CREATE. Il n’y a donc aucune instruction DROP IF EXISTS ... ou ALTER .... On a donc bien un référentiel. C’est au processus de déploiement de choisir comment obtenir cet état (rappelez-vous l’article sur les dacpac).

Ce projet peut contenir des scripts SQL ainsi que du code C#. Les éditeurs sont complets et proposent la coloration syntaxique ainsi que l’autocomplétion, que ce soit pour les scripts SQL ou le code .NET :

Refactoring intelligent

Tout comme pour un projet C#, Visual Studio propose plusieurs refactoring :

• Changer le nom d’un objet
• Changer un objet de schéma
• Remplacer les SELECT * par la liste de toutes les colonnes
• Remplacer le nom des objets par des noms complets si possible (schema. table. colonne)

Ces différents refactoring peuvent s’avérer très pratique notamment si la base de données contient beaucoup d’objets :

Pourquoi intelligent ? Lorsque l’on renomme un objet, un fichier .refactorlog est créé. Ce fichier permet de modifier le comportement standard lors de la génération du script de déploiement de la base de données. En effet au lieu de dropper une colonne et d’en créer une nouvelle, le script renommera la colonne existante dans la base de données.

<?xml version="1.0" encoding="utf-8"?>
<Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
  <Operation Name="Rename Refactor" Key="2b862e7a-7b19-4cfb-af61-32fa43235070" ChangeDateTime="12/14/2015 20:32:16">
    <Property Name="ElementName" Value="[dbo].[Customer].[Identity_column]" />
    <Property Name="ElementType" Value="SqlSimpleColumn" />
    <Property Name="ParentElementName" Value="[dbo].[Customer]" />
    <Property Name="ParentElementType" Value="SqlTable" />
    <Property Name="NewName" Value="Identity_columnRenamed" />
  </Operation>
</Operations>

-- Extrait du script SQL de publication
PRINT N'The following operation was generated from a refactoring log file 2b862e7a-7b19-4cfb-af61-32fa43235070';
PRINT N'Rename [dbo].[Customer].[Identity_column] to Identity_columnRenamed';
GO
EXECUTE sp_rename @objname = N'[dbo].[Customer].[Identity_column]', @newname = N'Identity_columnRenamed', @objtype = N'COLUMN';
GO
-- Refactoring step to update target server with deployed transaction logs
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '2b862e7a-7b19-4cfb-af61-32fa43235070')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('2b862e7a-7b19-4cfb-af61-32fa43235070')
GO

Détection des erreurs

Le projet est compilé. Cela permet d’identifier un maximum d’erreurs immédiatement sans avoir à exécuter chacune des procédures manuellement sur le serveur :

Cependant SQL Server permet de faire énormément de choses dont certaines ne peuvent pas être vérifiées statiquement. Certaines erreurs ne seront donc visibles qu’à l’exécution, mais au moins les plus bêtes seront évitées !

Code analysis

Egalement comme les projets .NET, Visual Studio peut vérifier que le code ne viole pas les conventions définie pour le projet :

Compilation & Déploiement

Le résultat de la compilation est un fichier dacpac permettant le déploiement de la base de données. Grâce à cet unique fichier, il est possible de créer une base de données vierge ou de mettre à jour une base de données déjà existante. Pour cela on peut utiliser Visual Studio (clic droit / Publish), SQL Server Management Studio ou SqlPackage. exe.

Ayant déjà écrit un article sur les fichiers dacpac, je vous invite à le lire pour plus de détails ici.

Scritps pré/post déploiement

Le processus de déploiement est peu personnalisable puisqu’intégralement géré par le mécanisme standard des dacpac. Cependant on peut tout de même exécuter un script SQL au début ou/et à la fin du déploiement. Cela peut par exemple permettre d’insérer ou mettre à jour des lignes dans les tables à la fin du déploiement.

Comparaison de schéma

Il est possible de comparer le contenu du projet avec une base de données, un fichier dacpac ou un autre projet SQL Server. Une fois la comparaison effectuée, on peut mettre à jour la cible et ce que ce soit la base de données ou un projet. Il est donc possible de mettre à jour le projet SQL Server à partir d’une base de données existante.

Gestion des dépendances

SQL Server permet l’utilisation de Linked Server permettant de faire des requêtes entre plusieurs bases de données.

Le projet n’a pas connaissance de la base de données ExternalDatabase et ne peut donc pas résoudre le nom. Comme pour un projet C# on ajoute donc une référence vers la base de données. Cette référence peut être de 3 types :

• Un autre projet SQL Server
• Un fichier dacpac
• Une base de données existante

Comme indiquer dans l’exemple d’usage, il faut remplacer ExternalDatabase.dbo... par le nom de la variable créée lors de l’ajout de la référence :

SELECT * FROM [$(ExternalDatabase)].dbo.Customer

Lors de la publication il est possible de modifier la valeur de cette variable. Cela permet de gérer le cas où le nom de la base externe diffère entre le poste de développement et la production.

Conclusion

Ce type de projet simplifie la création de la base de données, est intégré au gestionnaire de sources, et permet le déploiement et la mise à jour de la base vers le server SQL souhaité. Des fonctionnalités telles que le refactoring sont très appréciables. Bref beaucoup de bonnes choses lorsque l’on souhaite créer une base de données avec SQL Server :)