Variables dans un projet de Base de données Visual studio – Sql Server Data Tools et SQLCmd Variables

Les projets de bases de données permettent entre autres bonnes idées de disposer d’un suivi de version sur les structures de base de données SQL Server.
Ce type de projet a évolué depuis plusieurs versions de Visual Studio. La solution présentée ici concerne Visual Studio 2013 avec SSDT Database pour SQL Server 2012, cependant le principe était valable avec les précédentes versions de SSDT et avec la version 2014 de SQL Server.

Déclaration des variables et utilisation dans les scripts

Dans ce type de projet, il est possible de joindre à différentes occasions des scripts SQL pour

  • Définir une fonction ou procédure stockée
  • Définir un filegroup, une fonction de partitionnement ou un schéma de partitionnement
  • Insérer des données via un postbuild script

Ces projets sont implicitement destinés à maintenir plusieurs environnements (Développement, Intégration, Production). Au sein de ces scripts, nous pouvons avoir besoin de rendre dynamique une section de code (le lecteur pour un FileGroup, le préfixe pour un nom de table…)
L’aspect dynamique de ces scripts SQL peut être piloté grâce à des variables SQLCmd.
Pour utiliser de telle variable dans les scripts sql de notre projet, la syntaxe est $(NomDeVariable)

Il existe 4 variables prédéfinies pour les projets de base de données :

  • DatabaseName
  • DefaultDataPath
  • DefaultFilePrefix
  • DefaultLogPath

Code sql avec une variable système : Script d’un FileGroup dans le projet Database

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [TestFG];

Si j’ai besoin de variables supplémentaires, par exemple pour distribuer sur 2 disques les filegroups de ma base de données, la procédure est la suivante :

1. Déclaration dans les propriétés du projet

Sur l’onglet SQLCMD Variables, déclarez les variables supplémentaires, ici « DataPathEven » et « DataPathOdd »
Fenêtre de propriétés du projet

2. Utilisation depuis les scripts

Ces variables peuvent ensuite être utilisées dans un script SQL avec la syntaxe permettant d’accéder aux variables. J’ajoute ici un nouveau FileGroup dans le répertoire Storage de mon projet de base de données.

Code sql avec une variable utilisateur : Script d’un FileGroup dans le projet Database

ALTER DATABASE [$(DatabaseName)]
ADD FilE ( NAME = N'TestFG1', FILENAME = N'$(DataPathOdd)TestFG1.ndf' , SIZE = 0KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO
ALTER DATABASE [$(DatabaseName)]
ADD FilE ( NAME = N'TestFG2', FILENAME = N'$(DataPathEven)TestFG2.ndf' , SIZE = 0KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

Déploiement depuis SSDT

Depuis SSDT, la publication du projet permet d’affecter, via un designer des valeurs aux variables SQLCmd utilisateur.
L’interface ci-dessous est alors disponible.
Affectation de valeurs aux variables dans la publication depuis SSDT
Ici, les valeurs « D:\Data\Odd\ » et « D:\Data\Even » viennent surcharger les valeurs configurées à la déclaration des variables pour la publication en cours.
Cette publication peut être sauvegardée sous forme de profile de publication dans le projet de manière à être réutilisée plus tard.
Les valeurs des variables SQLCmd sont alors embarquées dans le profil de publication.
Le xml ainsi généré contient dans mon cas la section suivante

<ItemGroup>
<SqlCmdVariable Include="DataPathEven">
<Value>D:\Data\Odd\</Value>
</SqlCmdVariable>
<SqlCmdVariable Include="DataPathOdd">
<Value>D:\Data\Even\</Value>
</SqlCmdVariable>
</ItemGroup>

Déploiement du projet en dehors de SSDT

Déploiement depuis le SQL Server Management Studio

Depuis le Management Studio de SQL Server, l’assistant de mise à jour de data-tiers application ne permettra plus le déploiement. En effet, cet assistant n’est prévu ni pour utiliser un profil de publication, ni pour configurer les SQLCmd Variables du projet. Or, la configuration de ces variables étant nécessaire au déploiement, celui-ci échoue avec le message :
Erreur lors du déploiement depuis SSMS
On abandonnera donc cette solution pour se tourner vers un déploiement scripté.
Plusieurs possibilités sont envisageables pour atteindre cet objectif dont:
• Utiliser l’utilitaire en ligne de commande SQLPackage et configurer au déploiement les valeurs à affecter aux variables ou réutiliser un profil de publication
• Utiliser des tâches de déploiement MSBuild et les profils de publication.
La seconde solution fera l’objet d’un article dans quelques temps, je ne la développe pas ici.

Déploiement avec SQLPackage et affectation de valeur aux SQLCmd Variables

Pour la solution reposant sur SQLPackage, comme nous l’indique MSDN, nous pouvons affecter via la ligne de commandes des valeurs aux SQLCmd variable lors de notre déploiement.
Sur mon installation, l’exécutable SQLPackage peut-être trouvé à l’emplacement (à adapter suivant votre version de SQL Server) :
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120
Dans mon cas, le déploiement pourrait être réalisé en utilisant une commande de la forme suivante (exécutée dans le répertoire bin\debug de mon projet de base de données) :

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject

Sous cette forme, le déploiement échoue m’indiquant que les valeurs des SQLCmd variables ne sont pas fournies. Le paramètre « Variables » nous permettra d’affecter des valeurs à nos variables.

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject /Variables:DataPathOdd=D:\Data\Odd /Variables:DataPathEven=D:\Data\Even

Déploiement avec SQLPackage et utilisation d’un profil de publication

Nous pouvons également réutiliser un profil de publication en indiquant son chemin avec la syntaxe :

SQLPackage /Action:Publish /SourceFile:DbProject.dacpac /TargetServerName:MyHost / TargetDatabaseName:DbProject /Profile:{ProjectPath}DbProject.publish.xml

Rétablir Online un fichier d’un FileGroup OffLine (par erreur) – Utilisation d’une connexion DAC

Petit mémo de survie si un jour un des fichiers composant un FileGroup de votre base de données la plus critique se retrouve Offline par erreur.

CECI N’EST PAS UN BEST PRACTICE MAIS PLUTOT UN SURVIVAL TRICK

Petit rappel important, théoriquement un fichier est passé manuellement au statut OffLine parce qu’il est inutilisable. Le fichier est alors écarté du FileGroup de manière à permettre de restaurer la base de données Online privée du fichier fautif. La seule opération possible sur le fichier est alors la suppression.
Mettons-nous dans le cas en déclarant explicitement Offline le fichier TestFG d’une base de données Demo, ce qu’il est possible de faire avec un simple ALTER TABLE :

ALTER DATABASE Demo
MODIFY File (NAME= TestFG, OFFLINE)


Evidemment, il n’existe pas d’opération me permettant de faire marche arrière, « ONLINE » n’étant pas un argument valide pour l’opération MODIFY File. Mon Filegroup est désormais considéré comme Offline et accessoirement ma base comme suspecte. L’opération attendue ici est en fait la restauration d’un backup de ce fichier pour permettre son retour Online.
Maintenant, si l’état de ce fichier est erroné (fausse manipulation par exemple) et que vous n’avez pas de backup voici une solution qui permet de tomber en marche.
4 Etapes pour vous sortir de l’embarras :

Etape 1 : Passer l’instance en single user
Arrêter l’instance SQL Server hébergeant la base de données
Redémarrer l’instance avec la commande suivant (exécutée dans le sous-répertoire BInn de l’installation de SQL Server) :

SQLServer –s InstanceName -m

Etape 2 : Etablir une connexion D.A.C. (Dedicated Administrator Connection)
Dans SSMS (ou via SQLCMD), créer une nouvelle requête en établissant une connexion au serveur dont vous précéder le nom par « ADMIN: »
Connexion DAC - localhost

Etape 3 : Modifier le statut du fichier dans les tables système
Les tables systèmes sont désormais accessibles.
Pour connaitre les tables contenant les informations concrètes liées à nos FileGroup, une astuce est d’activer le plan d’exécution et d’interroger les vues catalog.
En l’occurrence, pour les fichiers participant aux FileGroup, il s’agit de la table sys.sysbrickFiles

select * from sys.sysbrickfiles

update sys.sysbrickfiles
set internalstatus = 0
where lname = 'TestFG'

La première requête permet de vérifier le nom et l’état logique du fichier, la seconde de modifier son état pour rétablir un état ONLINE.

Etape 4 : Vérifier l’état de la base de données
Relancer l’instance en mode classique via le SQL Server Configuration Manager et contrôler le retour du FileGroup perdu.
Un DBCC CheckDb peut être nécessaire pour repasser la base en mode normal.