Skip to main content

Data Tier Application avec SQL Server 2008 R2

Téléchargez dès à présent le document en format pdf

David BARBARIN (MVP SQL Server)

Article par David BARBARIN (MVP SQL Server)

David BARBARIN est actuellement consultant en bases de données pour une entreprise Suisse GOLD Partner Microsoft et participe au développement d’offres à valeur ajoutée de data management autour de la technologie Microsoft SQL Server. Intervenant en tant que speaker à deux reprises lors des sessions GUSS et SQLPass Suisse en 2010, David a également écrit de nombreux articles et billets de blog. De plus, il participe activement aux communautés SQL Server sur Developpez.com, Technet SQL Server et Beyond Relational.   

Expert en bases de données et performances SQL Server, David met son expérience au service des différents clients pour lesquels il a pu travailler.

Sommaire de l'article

Qu'est-ce que DAC ?

« Data Tier Application » est une nouvelle fonctionnalité introduite avec SQL Server 2008 R2 qui permet de rationnaliser les différentes étapes du cycle de vie d’une application.  Elle concerne surtout les applications qui reposent sur une architecture dirigée par les données (Data Driven Architecture).

 Il va s’en dire que cette fonctionnalité ne sera d’une grande utilité que pour les entreprises ayant déjà mis en place un processus de gestion des changements de leur application. Data Tier Application facilite la mise à jour d’une application en proposant une automatisation de tâches à différents niveaux de ce processus (détection des changements, développement,  packaging, déploiement en production …). Cette fonctionnalité concerne aussi bien les développeurs que les administrateurs de bases de données avec une amélioration de l’interopérabilité entre Visual Studio 2010 et SQL server.

Qu'est-ce qu'un package DAC ?

Une application gouvernée par les données peut être comparée à une entité qui peut contenir un certain nombre d’objets au niveau instance ou au niveau base de données comme les tables, les vues, les index, les procédures stockées ou encore des fichiers, des scripts qui définissent les exigences d’un déploiement. Cet ensemble de composant est communément appelé package DAC (Data Tier Application Components) et sera utilisé par les différents acteurs  (développeurs et administrateurs de bases de données) tout au long du cycle de vie de l’application. Un développeur utilisera Visual Studio 2010 (Edition Professional, Premium ou Ultimate) pour opérer les modifications nécessaires dans l’application alors qu’un administrateur de bases de données préférera utiliser SQL Server Management Studio pour la déployer et la gérer.  Enfin une application DAC installée sur une instance SQL Server est appelée instance DAC.

Le cycle de vie d'une application DAC

Le cycle de vie d'une appplication DAC

  • VS210 : Visual Studio 2010
  • SSMS : SQL Server Management Studio
  • UCP : Utility Control Point

DAC s’inscrit dans le schéma traditionnel du cycle de vie de conception d’une application. Il existe deux méthodes de génération de package DAC et deux méthodes de déploiement. 

Un package DAC peut être généré depuis SQL Server Management Studio pour une base de données existante ou depuis Visual Studio 2010.

Chaque package peut être ensuite déployé soit par un administrateur de bases de données par le biais de l’assistant prévu à cet effet dans SQL Server Management Studio soit directement par un développeur qui possèdent les droits nécessaires depuis Visual Studio 2010.

Le résultat d’un déploiement se traduit par une application DAC associée à une base de données et un enregistrement des métadonnées concernant l’application DAC dans la base de données msdb.

Création d'un projet DAC avec Visual Studio 2010

Création d'un projet DAC avec Visual Studio 2010

Visual Studio 2010 propose un modèle de création pour les projets de DAC :

Nous créerons un projet nommé DATA_TIER _APPLI_PROJECT avec le chemin par défaut proposé par Visual Studio. L’espace de travail devient le suivant :

Création d'un projet DAC avec Visual Studio 2010

La partie de droite concerne l’explorateur d’objets. Dans ce projet les objets suivants seront créés :

  • Une table T_UTILISATEUR
  • Une table T_NEWS
  • Une vue  V_UTILISATEUR_NEWS
  • Une procédure stockée PS_POST_NEWS

Création des tables

Pour créer un script de table il suffit de sélectionner le nœud Tables du projet et de faire clic droit > add > table

Création des tables

Création des tables

Le script de création de la table est le suivant :

Création des tables

Un fichier T_UTILISATEUR  est maintenant présent sous le nœud tables avec la commande DDL précédente :

Création des tables

Le script de création pour la table T_NEWS est le suivant :

Création des tables

Un deuxième fichier apparaît sous le nœud Tables :

Création des tables

Création de la vue

La création d’une vue s’effectue de la même façon que pour une table. Il suffit de sélectionner le bon objet dans la fenêtre :

Création de la vue

Note : se positionner sur le nœud View n’est pas une obligation. Il est possible par la suite de déplacer le fichier script généré avec un drag & drop par exemple.

Comme précédemment il suffit d’insérer la DDL suivante et d’enregistrer pour créer le fichier script associé.

Création de la vue

Note : la fonctionnalité IntelliSense peut être utilisée en mode déconnecté.

Création de la vue

Création de la procédure stockée

Comme vous l’avez sans doute compris la création d’objets est relativement simple. La DDL de la procédure stockée à enregistrer est la suivante :

Création de la procédure stockée

Le  fichier de script SQL est présent sous le nœud « Stored Procedures » :

Création de la procédure stockée

Génération d'un package DAC

La phase suivante consiste à générer le package DAC.  Cette génération s’effectue au moyen du menu BUILD du projet.

Génération d'un package DAC

La génération du package produit la sortie suivante dans Visual Studio 2010 :

Génération d'un package DAC

Le fichier a été généré sans erreurs dans le répertoire par défaut : C:\Users\Administrateur\documents\visual studio 2010\Projects\DATA_TIER _APPLI_PROJECT\DATA_TIER _APPLI_PROJECT\sql\debug\ et porte le nom DATA_TIER _APPLI_PROJECT.dacpac

Génération d'un package DAC

Un package DAC est en réalité une archive constituée de plusieurs fichiers.

  • Un fichier de script SQL qui contient les commandes de création des objets de la base de données
  • Deux autres fichiers de script SQL concernant les commandes de pré et post déploiement
  • Fichiers XML de configuration

Génération d'un package DAC

Déploiment d'un package DAC

Le déploiement d’un package DAC par un administrateur de bases de données s’effectue grâce à l’assistant graphique prévu à cet effet dans SQL Server Management Studio.

Déploiment d'un package DAC

Déploiment d'un package DAC

Déploiment d'un package DAC

Il faut choisir le package DAC à déployer. Une fois que celui-ci est sélectionné les informations le concernant (nom de l’application et la version) apparaissent dans la section DAC details.

Déploiment d'un package DAC

La fenêtre suivante permet de configurer les propriétés de la base de données associée. Il est possible ici de définir les répertoires d’installation de la base de données de l’application DAC.

Déploiment d'un package DAC

Déploiment d'un package DAC

La base de données nommée DATA_TIER_APPLI_PROJECT a bien été créé ainsi que l’application qui sont visibles respectivement sous le nœud Databases et Management > Date-tier Applications

Déploiment d'un package DAC

La définition de l’application est également stockée dans la base msdb. La table système sysdac_instances_internalpermet de connaître le nom des applications installées.

Déploiment d'un package DAC

On peut retrouver les objets créés dans notre projet dans la base de données DATA_TIER_APPLI_PROJECT :

Déploiment d'un package DAC

Déploiment du package DAC depuis Visual Studio 2010

Le déploiement d’une application DAC peut également s’effectuer depuis Visual Studio 2010 à condition d’avoir les droits minimums requis d’une part (à savoir db_creator) et d’avoir configurer les paramètres de déploiement liés au projet d’autre part.

Pour configurer les paramètres du projet il faut se rendre dans les propriétés de celui-ci (clic droit sur le projet et menu propriétés).

Déploiment du package DAC depuis Visual Studio 2010

La fenêtre des propriétés s’ouvre dans la partie centrale :

Déploiment du package DAC depuis Visual Studio 2010

… avec deux paramètres importants à configurer :

  • La chaîne de connexion permettant de se connecter au serveur cible
  • Le nom de l’application à déployer

Un 3ème paramètre facultatif concerne la vérification des règles de déploiement avant le déploiement.

Le paramétrage de la chaîne de connexion s’effectue en cliquant sur le bouton « Edit ». Une fenêtre de connexion s’ouvre alors :

Déploiment du package DAC depuis Visual Studio 2010

Le nom du serveur cible doit être renseigné ainsi que les informations de connexion.

Déploiment du package DAC depuis Visual Studio 2010

Avant de déployer à nouveau l’application et de la mettre à jour nous ajouterons une table T_FORUM et modifierons la définition de la table T_NEWS.

Le script de création de la table T_FORUM :

Déploiment du package DAC depuis Visual Studio 2010

Le script de création de la table T_NEWS :

Déploiment du package DAC depuis Visual Studio 2010

Pour déployer une application, il suffit d’effectuer un clic droit sur le projet et de choisir le menu « Deploy » 

Déploiment du package DAC depuis Visual Studio 2010

La fenêtre de sortie affiche la progression du déploiement et les éventuels messages d’erreurs rencontrés.

Déploiment du package DAC depuis Visual Studio 2010

Si l’on se rend dans SQL Server Management Studio, on peut remarquer qu’il existe maintenant deux bases de données ou plutôt deux versions de bases de données :

Déploiment du package DAC depuis Visual Studio 2010

Les objets de la base ont bien été modifiés :

Déploiment du package DAC depuis Visual Studio 2010

En interrogeant la table sysdac_history_internal dans la base de données msdb on peut visualiser les différentes étapes exécutées par SQL Server pour effectuer un déploiement de l’application DAC.

Déploiment du package DAC depuis Visual Studio 2010

… qui donne le résultat suivant :

Déploiment du package DAC depuis Visual Studio 2010

Les étapes exécutées sont les suivantes :

  • Création d’une base de données supplémentaire
  • Création des objets à l’image de l’application DAC initiale
  • Enregistrement de la nouvelle application DAC sur la nouvelle base créée
  • Mise en lecture seule de la base de données initiale
  • Déconnexion des utilisateurs de la base de données initiale
  • Désactivation des contraintes présentes dans la nouvelle base de données
  • Transfert des données depuis la base de données initiale vers la nouvelle base de données
  • Réactivation des contraintes présentes dans la nouvelle base de données
  • Réaction du mode lecture / écriture de la base de données initiale
  • Renommage de la base de données initiale
  • Renommage de la nouvelle base de données avec le nom d’origine de l’application DAC
  • Mise à jour de la nouvelle application DAC

Comparateur de schéma avec VS2010

Le comparateur de schéma est une fonctionnalité intéressante à utiliser avec une application DAC. Un développeur peut rapidement voir les différences qu’il existe entre une version de production et une version en développement.

Comparateur de schéma avec VS2010

On peut comparer notre application DAC (Project) et la base de données en production

Comparateur de schéma avec VS2010

Comparateur de schéma avec VS2010

Comparateur de schéma avec VS2010

Comparateur de schéma avec VS2010

On peut voir par exemple que la table dbo.T_NEW doit être créé et la table dbo.T_NEWS doit être supprimée ou encore que la vue dbo.V_UTILISATEUR_NEWS doit être mise à jour car elle référence la table dbo.T_NEW.

Mise à jour d'une application DAC avec SSMS

Nous avons vu un peu plus haut la mise à jour d’une application DAC depuis VS2010. Dans cette section nous allons voir cette même mise à jour depuis SSMS. Après avoir généré le package DAC de l’application, il faut lancer l’assistant de mise à jour prévue à cet effet.

Mise à jour d'une application DAC avec SSMS

Mise à jour d'une application DAC avec SSMS

Mise à jour d'une application DAC avec SSMS

Une détection et une comparaison des changements est effectuée.

Mise à jour d'une application DAC avec SSMS

Mise à jour d'une application DAC avec SSMS

SQL Server procède ensuite  à la vérification des objets du package afin de savoir si ceux-ci sont supportés par DAC.  Cette étape passée, la mise é jour de l’application peut commencer.

Mise à jour d'une application DAC avec SSMS

Règles de gestion de déploiment d'une application DAC

Il est possible de créer des règles de gestion de déploiement des packages DAC depuis Visual Studio 2010, ce qui permet de concevoir une véritable politique d’entreprise de déploiement sur les instances SQL Server. Par exemple, il est possible contrôler la mise à jour de versions des applications sur les serveurs et de ne pas déployer accidentellement une version ancienne en production. De la même manière il est possible de contrôler sur quels éditions de SQL Server ou sur quelle version du système d’exploitation  votre package peut être déployé. La sélection des règles de gestion de déploiement dans Visual Studio 2010 permet de réaliser un certain nombre de scénarios de contrôle pour le déploiement d’une application DAC.

Dans notre cas nous ne voulons pas que l’application DAC DATA_TIER_APPLI_PROJECT puisse être déployée sur une instance SQL Server Enterprise Edition. La règle de déploiement se traduit de la façon suivante sur Visual Studio 2010 (nœud Properties > ServerSelection.sqlpolicy).

Règles de gestion de déploiment d'une application DAC

Règles de gestion de déploiment d'une application DAC

Une règle de déploiement est composée de facettes et de conditions. Dans notre cas celle-ci sera paramétrée de la façon suivante :

[Edition] (Facet)
[is not equal to] (Comparison operator)
[Enterprise Edition] (Condition value)

Règles de gestion de déploiment d'une application DAC

Règles de gestion de déploiment d'une application DAC

Une fois la fois la règle de sélection définie nous pouvons déployer le package DAC depuis Visual Studio 2010 dans un premier temps et en utilisant SQL Server Management Studio sur une instance SQL Server Enterprise Edition dans un deuxième temps.  Il faut au préalable avoir activé l’option de vérification des règles de sélection pour le déploiement dans les propriétés du projet.

Règles de gestion de déploiment d'une application DAC

Déploiement via Visual Studio 2010

Règles de gestion de déploiment d'une application DAC

La fenêtre de sortie de déploiement est la suivante :

Règles de gestion de déploiment d'une application DAC

Le déploiement a échoué car l’instance SQL Server cible ne respecte pas la règle de déploiement paramétrée pour l’application DAC DATA_TIER_APPLI_PROJECT

Déploiement via SQL Server Management Studio

Dans ce scénario, il faut d’abord générer le package DAC. (cf. section génération package DAC). L’assistant DAC nous permet ensuite de déployer l’application DAC à l’aide du package généré.

Règles de gestion de déploiment d'une application DAC

Règles de gestion de déploiment d'une application DAC

Sélection du package

Règles de gestion de déploiment d'une application DAC

Une erreur est levée dans l’écran d’examen des règles de déploiement. A ce moment du processus de déploiement, l’administrateur de bases de données a la possibilité de passer outre les erreurs existantes et de forcer le déploiement (option ignore policy violations).

Règles de gestion de déploiment d'une application DAC

Conversion d'une base de données en application DAC

Une base de données existante peut être convertie en une application DAC. Cette nouvelle application peut également être alors déployée sur d’autres instances SQL Server ou modifiée par les équipes de développement dans Visual Studio 2010.

Conversion d'une base de données en application DAC

Conversion d'une base de données en application DAC

Il faut donner un nom pour la nouvelle application DAC ainsi qu’un numéro de version

Conversion d'une base de données en application DAC

Une tâche est ensuite démarrée pour vérifier si les objets que contient la base de données APPLI_PROD sont supportés par DAC. Si une erreur survient pendant cette phase de vérification à cause d’un objet non supportée par DAC, la base de données ne peut pas être convertie. L’administrateur de bases de données est alors invité à corriger les erreurs rencontrées pendant le processus de conversion.

Conversion d'une base de données en application DAC

Dans notre cas, aucun problème n’a été rencontré. Tous les objets de la base APPLI_PROD sont supportés par DAC.

Conversion d'une base de données en application DAC

Conversion d'une base de données en application DAC

La base de données APPLI _PROD est maintenant inscrite en tant que qu’application DAC. On peut le vérifier via le nœud « Data-tier Applications »

Conversion d'une base de données en application DAC

… ou la table système sysdac_instances dan la base de données msdb :

Conversion d'une base de données en application DAC

Par exemple la base de données exemple AdventureWorks2008R2 ne peut pas convertie en application DAC :

Conversion d'une base de données en application DAC

Voici quelques exemples d’objets présents dans la base de données AdventureWorks2008R2 et qui posent problème :

  • Index FULLTEXT
  • Type de données non supportés :
    • Hierarchy,
    • Geometry,
    • XML,
    • XML schemas collection
    • FILESTREAM
  • Triggers DDL
  • Propriétés étendues

Suppresion d'une application DAC

Un administrateur de bases de données peut être amené à supprimer une application DAC d’une instance SQL Server. Cette suppression peut s’effectuer à l’aide de l’assistant prévu à cet effet. Ce dernier propose 3 options de suppression :

  • Par désinscription de l’application DAC : Cette option permet de supprimer les métadonnées de l’application DAC. La base de données associée  et les comptes de connexion sont  préservés.
  • Par détachement : Cette option supprime également les métadonnées associées à l’application DAC. A l’instar d’une opération de détachement d’une base de données classique, les fichiers de l’application DAC sont également détachés. Les comptes de connexion ne sont pas supprimés.
  • Par suppression : Les métadonnées ainsi que la base de données associée sont supprimés. Les comptes de connexion sont préservés.

Suppresion d'une application DAC

Suppresion d'une application DAC

Suppresion d'une application DAC

Extraction d'une application DAC

L’extraction  à partir d’une base de données existante via SQL Server Management Studio est une autre possibilité de création d’une nouvelle application DAC. L’assistant graphique permet de générer directement un package DAC.  SQL Server vérifie dans ce cas le contenu de la base de données source avec une tâche de validation des objets supportés par DAC et les comptes de connexion. Un package DAC est alors créé. Un des avantages de cette méthode est qu’elle est applicable pour différentes versions de SQL Server qui vont de 2008 à 2008 R2 pour le moment.

Extraction d'une application DAC

Extraction d'une application DAC

L’assistant requiert un nom pour la nouvelle application DAC, un numéro de version, une description (optionnelle) et le répertoire où sera généré le package DAC.

Extraction d'une application DAC

Phase de vérification du contenu de la base de données :

Extraction d'une application DAC

Phase de vérification réussie

Extraction d'une application DAC

La création du package DAC peut maintenant s’effectuer

Extraction d'une application DAC

Extraction d'une application DAC

Le package DAC a bien été généré

Extraction d'une application DAC

Important d'une application DAC dans Visual Studio

Il existe deux méthodes d’importation d’un package DAC dans Visual Studio :

  • En se connectant directement sur le serveur de bases de données
  • En important un package DAC préalablement généré depuis l’assistant prévu à cet effet

Il s’agit tout d’abord de créer un nouveau projet Data Tier Application. Une fois le projet créé il suffit d’importer l’application DAC avec une des méthodes citées ci-dessus. Nous utiliserons la méthode d’importation du package APPLI_PROD.dacpac généré auparavant.

Important d'une application DAC dans Visual Studio

Un nouvel assistant démarre

Important d'une application DAC dans Visual Studio

Nous devons alors choisir la méthode d’importation de l’application DAC. Nous choisirons la méthode d’importation d’un package DAC

Important d'une application DAC dans Visual Studio

Important d'une application DAC dans Visual Studio

Une fois l’application DAC importée on peut visualiser les scripts SQL générés dans l’explorateur de solutions.

Important d'une application DAC dans Visual Studio

Possibilités de refactoring dans Visual Studio 2010

Le refactoring est une autre fonctionnalité intéressante à utiliser avec Visual Studio 2010. Celle-ci n’est pas nouvelle mais il me semble important de rappeler son existence ici. Par exemple, changer le nom d’une table qui possède de multiples références vers d’autres objets peut devenir rapidement fastidieux et source d’erreurs. Dans un projet de type SQL Server (et DAC), cette action est relativement facile à réaliser.

Dans le projet DATA_TIER_APPLI_PROJECT nous devons renommer la table T_NEWS. Celle-ci est référencée dans la procédure stockée PS_POST_NEWS et la vue V_UTILISATEUR_NEWS. Le nom de la  table T_NEWS doit être, par conséquent, renommé dans ces 2 objets.

Il faut dans un premier temps se rendre dans la fenêtre de vue de schéma du projet DAC et utiliser ensuite le menu Refactor pour la table T_NEWS.

Possibilités de refactoring dans Visual Studio 2010

On renomme ensuite la table T_NEWS en T_NEW

Possibilités de refactoring dans Visual Studio 2010

Il est possible de pré-visualiser les changements que vont être opérés par le changement de nom de la table. On peut remarquer ici que les scripts SQL relatifs à la vue V_UTILISATEUR_NEWS et à la procédure stockée PS_POST_NEWS sont concernés. Il est possible de valider le changement ou de l’annuler à ce stade.

Possibilités de refactoring dans Visual Studio 2010

Une fois le changement effectué on peut vérifier que les deux scripts concernés.

Possibilités de refactoring dans Visual Studio 2010

Possibilités de refactoring dans Visual Studio 2010

Le changement a bien été propagé dans les différents scripts concernés par la nouvelle table T_NEW.

Monitoring d'une application DAC

SQL Server 2008 R2 propose également une fonctionnalité de monitoring appelée SQL Server Utiliy qui permet de surveiller une ou plusieurs instances DAC. SQL Server Utilty fournit des informations sur l’utilisation des ressources par une instance DAC qui permettent de savoir si une instance utilise ou non de façon optimale les ressources du serveur qu’il héberge. Dans le cadre d’une mutualisation des ressources ces informations peuvent être très utiles.

Utilisation du stockage :

Monitoring d'une application DAC

Utilisation des ressources processeurs :

Monitoring d'une application DAC

SQL Server Utility permet égalent de définir des seuils d’utilisation de ressources qui permettent de déclencher des alertes visuelles pour à un administrateur de bases de données afin de l’informer d’une application DAC qui respecterait pas les limites d’utilisation des ressources en adéquation avec les besoins métiers de l’entreprise.

La table système msdb.dbo.sysdac_history_internal peut également être utilisée comme source de monitoring ‘une application DAC.

Monitoring d'une application DAC

Objets supportés par DAC

Comme nous l’avons vu un peu plus haut dans l’article, la conversion d’une base de données existante en application DAC requiert à priori une phase de validation des objets supportés par DAC. La liste des objets supportés est la suivante (extrait de la BOL Microsoft) :

DATABASE ROLEFUNCTION: Inline Table-valued
FUNCTION: Multistatement Table-valuedFUNCTION: Scalar
INDEX: ClusteredINDEX: Non-clustered
INDEX: UniqueLOGIN
SCHEMASTORED PROCEDURE: Transact-SQL
TABLE: Check ConstraintTABLE: Collation
TABLE: Column, including computed columnsTABLE: Constraint, Default
TABLE: Constraint, Foreign KeyTABLE: Constraint, Index
TABLE: Constraint, Primary KeyTABLE: Constraint, Unique
TRIGGER: DMLTYPE: User-defined Data Type
TYPE: User-defined Table TypeUSER
VIEW 

Versions de SQL Server supportant DAC

Chaque version de SQL Server possède un nombre plus ou mois restreint d’opérations DAC. Il faut maintenant ajouter aux versions traditionnelles SQL Azure.

DAC OperationSQL Server 2008 R2SQL AzureSQL Server 2008SQL Server 2005SQL Server 2000
Delete DACYesYesService Pack 2 (SP2) or laterNoNo
Deploy DACYesYesSP2 or laterNoNo
Extract DACYesYesYesYesYes
Register DACYesYesSP2 or laterNoNo
Upgrade DACYesNoSP2 or laterNoNo
Client Tools Support DAC OperationsYesNoNoNoNo

Contraintes d'utilisation

Même si DAC est une fonctionnalité intéressante à utiliser, il faudra compter avec certaines contraintes d’utilisations :

  • Le changement de nom d’une base de données associée  à une application DAC ne peut pas s’effectuer dans un schéma classique d’administration car les métadonnées de l’application DAC sont stockées  dans la base système msdb.
  • La structure physique de la base de données est figée. Il est impossible d’ajouter des groupes de fichiers supplémentaires ou de partitionner la base de données par exemple.
  • La liste des objets supportés par DAC réduit considérablement la liste des bases de données candidates à une éventuelle conversion et les possibilités de développement.
  • Les changements ne peuvent être effectués directement dans la base comme l’ajout d’un index, d’une table ou encore la modification d’une colonne. Bien que cela puisse être perçu comme une contrainte, il est tout à fait logique que ces changements soient effectués en amont dans la phase de développement du cycle de vie de l’application.
  • Le processus de mise à jour d’une application DAC fait une copie de l’ancienne version de la base de données associée à l’instance DAC. L’espace disque nécessaire et le temps de mise à jour peuvent être importants en fonction de la volumétrie des données. Les bases de données de petite taille sont plus adaptées à DAC.