Déploiement d’appartenances aux rôles de base de données dans les environnements de test

Article
07/13/2023

Cette rubrique explique comment ajouter des comptes d’utilisateur aux rôles de base de données dans le cadre d’un déploiement de solution dans un environnement de test.

Lorsque vous déployez une solution contenant un projet de base de données dans un environnement de préproduction ou de production, vous ne souhaitez généralement pas que le développeur automatise l’ajout de comptes d’utilisateur aux rôles de base de données. Dans la plupart des cas, le développeur ne sait pas quels comptes d’utilisateur doivent être ajoutés à quels rôles de base de données, et ces exigences peuvent changer à tout moment. Toutefois, lorsque vous déployez une solution contenant un projet de base de données dans un environnement de développement ou de test, la situation est généralement différente :

Le développeur redéploye généralement la solution régulièrement, souvent plusieurs fois par jour.

La base de données est généralement recréé à chaque déploiement, ce qui signifie que les utilisateurs de base de données doivent être créés et ajoutés à des rôles après chaque déploiement.

Le développeur a généralement un contrôle total sur l’environnement de développement ou de test cible.

Dans ce scénario, il est souvent utile de créer automatiquement des utilisateurs de base de données et d’attribuer des appartenances aux rôles de base de données dans le cadre du processus de déploiement.

Le facteur clé est que cette opération doit être conditionnelle en fonction de l’environnement cible. Si vous effectuez un déploiement dans un environnement intermédiaire ou de production, vous souhaitez ignorer l’opération. Si vous déployez dans un environnement de développement ou de test, vous souhaitez déployer des appartenances aux rôles sans intervention supplémentaire. Cette rubrique décrit une approche que vous pouvez utiliser pour relever ce défi.

Cette rubrique fait partie d’une série de tutoriels basés sur les exigences de déploiement d’entreprise d’une société fictive nommée Fabrikam, Inc. Cette série de tutoriels utilise un exemple de solution, la solution Contact Manager, pour représenter une application web avec un niveau de complexité réaliste, y compris une application MVC 3 ASP.NET, un service Windows Communication Foundation (WCF) et un projet de base de données.

La méthode de déploiement au cœur de ces didacticiels est basée sur l’approche de fichier projet fractionné décrite dans Présentation du fichier projet, dans laquelle le processus de génération est contrôlé par deux fichiers projet : l’un contenant des instructions de génération qui s’appliquent à chaque environnement de destination et l’autre contenant des paramètres de build et de déploiement spécifiques à l’environnement. Au moment de la génération, le fichier projet spécifique à l’environnement est fusionné dans le fichier projet indépendant de l’environnement pour former un ensemble complet d’instructions de build.

Vue d’ensemble des tâches

Cette rubrique suppose que :

Vous utilisez l’approche de fichier projet fractionné pour le déploiement de solution, comme décrit dans Présentation du fichier projet.
Vous appelez VSDBCMD à partir du fichier projet pour déployer votre projet de base de données, comme décrit dans Présentation du processus de génération.

Pour créer des utilisateurs de base de données et attribuer des appartenances aux rôles lorsque vous déployez un projet de base de données dans un environnement de test, vous devez :

Créez un script Transact langage SQL (Transact-SQL) qui apporte les modifications nécessaires à la base de données.
Créez une cible Microsoft Build Engine (MSBuild) qui utilise l’utilitaire sqlcmd.exe pour exécuter le script SQL.
Configurez vos fichiers projet pour appeler la cible lorsque vous déployez votre solution dans un environnement de test.

Cette rubrique vous montre comment effectuer chacune de ces procédures.

Script des appartenances aux rôles de base de données

Vous pouvez créer un script Transact-SQL de différentes manières et à l’emplacement de votre choix. L’approche la plus simple consiste à créer le script dans votre solution dans Visual Studio 2010.

Pour créer un script SQL

Dans la fenêtre Explorateur de solutions, développez le nœud de votre projet de base de données.
Cliquez avec le bouton droit sur le dossier Scripts , pointez sur Ajouter, puis cliquez sur Nouveau dossier.
Tapez Test comme nom de dossier, puis appuyez sur Entrée.
Cliquez avec le bouton droit sur le dossier Test , pointez sur Ajouter, puis cliquez sur Script.
Dans la boîte de dialogue Ajouter un nouvel élément , donnez un nom explicite à votre script (par exemple, AjouterRoleMemberships.sql), puis cliquez sur Ajouter.
Dans le fichier AddRoleMemberships.sql , ajoutez des instructions Transact-SQL qui :
1. Créez un utilisateur de base de données pour la connexion SQL Server qui accédera à votre base de données.
2. Ajoutez l’utilisateur de base de données à tous les rôles de base de données requis.

Le fichier doit ressembler à ceci :

USE $(DatabaseName)
GO
CREATE USER [FABRIKAM\TESTWEB1$] FOR LOGIN[FABRIKAM\TESTWEB1$]
GO
USE [ContactManager]
GO
EXEC sp_addrolemember N'db_datareader', N'FABRIKAM\TESTWEB1$'
GO
USE [ContactManager]
GO
EXEC sp_addrolemember N'db_datawriter', N'FABRIKAM\TESTWEB1$'
GO

Enregistrez le fichier .

Exécution du script sur la base de données cible

Dans l’idéal, vous devez exécuter tous les scripts Transact-SQL requis dans le cadre d’un script de post-déploiement lorsque vous déployez votre projet de base de données. Toutefois, les scripts post-déploiement ne vous permettent pas d’exécuter une logique conditionnelle en fonction des configurations de solution ou des propriétés de build. L’alternative consiste à exécuter vos scripts SQL directement à partir du fichier projet MSBuild, en créant un élément Target qui exécute une commande sqlcmd.exe. Vous pouvez utiliser cette commande pour exécuter votre script sur la base de données cible :

sqlcmd.exe –S [Database server] –d [Database name] –i [SQL script]

Notes

Pour plus d’informations sur les options de ligne de commande sqlcmd, consultez Utilitaire sqlcmd.

Avant d’incorporer cette commande dans une cible MSBuild, vous devez déterminer dans quelles conditions vous souhaitez que le script s’exécute :

La base de données cible doit exister avant de modifier ses appartenances de rôle. Par conséquent, vous devez exécuter ce script après le déploiement de la base de données.
Vous devez inclure une condition afin que le script soit exécuté uniquement pour les environnements de test.
Si vous exécutez un déploiement « what if » (en d’autres termes, si vous générez des scripts de déploiement, mais que vous ne les exécutez pas réellement), vous ne devez pas exécuter le script SQL.

Si vous utilisez l’approche de fractionnement de fichier projet décrite dans Présentation du fichier projet, comme le montre l’exemple de solution Contact Manager, vous pouvez fractionner les instructions de génération de votre script SQL comme suit :

Toutes les propriétés spécifiques à l’environnement requises, ainsi que la propriété qui détermine s’il faut déployer des autorisations, doivent être placées dans le fichier projet spécifique à l’environnement (par exemple, Env-Dev.proj).
La cible MSBuild elle-même, ainsi que toutes les propriétés qui ne changeront pas entre les environnements de destination, doivent aller dans le fichier projet universel (par exemple, Publish.proj).

Dans le fichier projet spécifique à l’environnement, vous devez définir le nom du serveur de base de données, le nom de la base de données cible et une propriété booléenne qui permet à l’utilisateur de spécifier s’il faut déployer des appartenances de rôle.

<PropertyGroup>
   <CmTargetDatabase Condition=" '$(CmTargetDatabase)'=='' ">
      ContactManager
   </CmTargetDatabase>
   <DatabaseServer Condition=" '$(DatabaseServer)'=='' ">
      TESTDB1
   </DatabaseServer>
   <DeployTestDBRoleMemberships Condition="'$(DeployTestDBRoleMemberships)'==''">
      true
   </DeployTestDBRoleMemberships>
</PropertyGroup>

Dans le fichier projet universel, vous devez fournir l’emplacement du fichier exécutable sqlcmd et l’emplacement du script SQL que vous souhaitez exécuter. Ces propriétés resteront les mêmes quel que soit l’environnement de destination. Vous devez également créer une cible MSBuild pour exécuter la commande sqlcmd.

<PropertyGroup>
   <SqlCmdExe Condition=" '$(SqlCmdExe)'=='' ">
      C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe
   </SqlCmdExe>
</PropertyGroup>

<Target Name="DeployTestDBPermissions" 
        Condition=" '$(DeployTestDBRoleMemberships)'=='true' AND 
                    '$(Whatif)'!='true' ">
   <PropertyGroup>
     <SqlScript>
        $(SourceRoot)ContactManager.Database\Scripts\Test\AddRoleMemberships.sql
     </SqlScript>
     <_Cmd>"$(SqlCmdExe)" -S "$(DatabaseServer)" 
                          -d "$(CmTargetDatabase)" 
                          -i "$(SqlScript)"
     </_Cmd>
   </PropertyGroup>
   <Exec Command="$(_Cmd)" ContinueOnError="false" />
</Target>

Notez que vous ajoutez l’emplacement de l’exécutable sqlcmd en tant que propriété statique, car cela peut être utile pour d’autres cibles. En revanche, vous définissez l’emplacement de votre script SQL et la syntaxe de la commande sqlcmd en tant que propriétés dynamiques au sein de la cible, car elles ne seront pas nécessaires avant l’exécution de la cible. Dans ce cas, la cible DeployTestDBPermissions n’est exécutée que si ces conditions sont remplies :

La propriété DeployTestDBRoleMemberships a la valeur true.
L’utilisateur n’a pas spécifié d’indicateur WhatIf=true .

Enfin, n’oubliez pas d’appeler la cible. Dans le fichier Publish.proj , vous pouvez le faire en ajoutant la cible à la liste des dépendances pour la cible FullPublish par défaut. Vous devez vous assurer que la cible DeployTestDBPermissions n’est pas exécutée tant que la cible PublishDbPackages n’a pas été exécutée.

<Project ToolsVersion="4.0" 
         DefaultTargets="FullPublish" 
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
   ...
   <PropertyGroup>
      <FullPublishDependsOn>
         Clean;
         BuildProjects;
         GatherPackagesForPublishing;
         PublishDbPackages;
         DeployTestDBPermissions;
         PublishWebPackages;
      </FullPublishDependsOn>
   </PropertyGroup>
   <Target Name="FullPublish" DependsOnTargets="$(FullPublishDependsOn)" />
</Project>

Conclusion

Cette rubrique décrit une façon d’ajouter des utilisateurs de base de données et des appartenances aux rôles en tant qu’action post-déploiement lorsque vous déployez un projet de base de données. Cela est généralement utile lorsque vous recréez régulièrement une base de données dans un environnement de test, mais il doit généralement être évité lorsque vous déployez des bases de données dans des environnements intermédiaires ou de production. Par conséquent, vous devez vous assurer d’utiliser la logique conditionnelle nécessaire afin que les utilisateurs de base de données et les appartenances aux rôles ne soient créés que lorsqu’il est approprié de le faire.

En savoir plus

Pour plus d’informations sur l’utilisation de VSDBCMD pour déployer des projets de base de données, consultez Déploiement de projets de base de données. Pour obtenir des conseils sur la personnalisation des déploiements de bases de données pour différents environnements cibles, consultez Personnalisation des déploiements de bases de données pour plusieurs environnements. Pour plus d’informations sur l’utilisation de fichiers projet MSBuild personnalisés pour contrôler le processus de déploiement, consultez Présentation du fichier projet et Présentation du processus de génération. Pour plus d’informations sur les options de ligne de commande sqlcmd, consultez Utilitaire sqlcmd.

Précédent Suivant

Partager via