SMO : SQL Server 2005 et .Net 2.0

Date de publication : 01/07/2005

Par Ronald VASSEUR (autres articles)
 

Sql Server Management Objects ou SMO est un ensemble de classes permettant d'accéder à la quasi totalité des fonctions de maintenance et d'administration de SQL Server, et cela, directement depuis votre code. Il est donc désormais possible d'effectuer une multitude d'actions dans SQL Server directement depuis une application .Net .


Introduction
1. Présentation de SMO
2. Connexion au serveur à l'aide de SMO
3. Lister les bases de données et leurs tables
4. Création d'une base de données
5. Mode Capture et Exécution
6. Sauvegarde et restauration de bases de données
Conclusion


Introduction

L'année 2005 sera une année importante dans les mémoires des développeurs utilisant les outils Microsoft : au cours de cette année, rien de moins que la version 2.0 du Framework .Net ainsi que la version 2005 de SQL Server, le célèbre serveur de bases de données. Dans cet article nous allons voir de plus près une grande nouveauté dans l'administration des bases de données SQL Server depuis des applications .Net : Sql Server Management Objects, ou encore SMO.

SMO est constitué de différentes classes permettant de réaliser des tâches de maintenance et d'administration au travers de code .Net. Auparavant ces opérations n'étaient principalement possible qu'au travers du langage T-SQL ou encore des outils d'administration intégrés de SQL Server 2000. On voit donc ici un exemple parmi tant d'autres, des améliorations apportées par le rapprochement de SQL Server et du Framework .Net; le but ne sera pas de faire une présentation exhaustive de SMO, cela serait un travail titanesque, mais simplement de voir quelques fonctionnalités intéressantes qu'il propose.


1. Présentation de SMO

SMO est donc un ensemble de plus d'une centaine de classes regroupées en 6 namespaces. On peut dire que SMO est le successeur de DMO (Distributed Management Objects) sous SQL Server 2000. SMO propose la quasi totalité des fonctionnalités d'administration et de maintenance de SQL Server, et cela, directement depuis votre code .Net, donc l'idée qui nous vient immédiatement à l'esprit est : plus besoin d'écrire de longs et pénibles (pour ceux qui le maitrisent mal) scripts en T-SQL, et de les faire exécuter par nos applications .Net. Vous verrez de plus que SMO est très simple d'emploi.

L'ensemble des classes de SMO peut potentiellement fonctionner avec SQL Server 7, 2000 et 2005, mais il est probable que certaines fonctionnalités ne soient disponibles que pour les versions 2000 ou 2005.

Pour utiliser SMO vous devez faire un Imports sur le namespace correspondant, sans oublier d'avoir au préalable ajouté la référence Microsoft.SqlServer.Management.Smo à votre projet, dans Visual Studio 2005 rien de plus simple : cliquez droit sur votre projet dans l'explorateur de solutions, puis faites "ajouter une référence", et sélectionnez alors la référence correspondante dans la liste.

Puis une fois la référence ajoutée vous devez ajouter en entête de votre classe la ligne de code suivante :

Imports Microsoft.SqlServer.Management.Smo

Une caractéristique importante de SMO est le fait qu'il utilise un fonctionnement "en cache", cela signifie que nous allons pouvoir programmer plusieurs actions a réaliser mais elles seront réalisées toutes ensembles au moment où on le décide, c'est-à-dire que l'on ne va pas avoir besoin de créer une connexion au serveur pour chaque action, toutes les actions seront transmises en une seule fois, tout cela permet d'économiser un bon nombre d'aller-retour entre l'application et le serveur SQL Server ; cela apporte donc un gain certain au niveau des performances. Un autre aspect de l'optimisation de SMO se situe au niveau de l'instanciation des objets. En effet il n'est pas nécessaire d'instancier toutes les propriétés d'un objet, il peut donc l'être de manière partielle, ce qui peut avoir un impact au niveau des performances de l'application. On le voit, Microsoft a conçu SMO en gardant deux idées en tête, simplicité et performance, voyons cela de plus près.


2. Connexion au serveur à l'aide de SMO

Pour se connecter à un serveur SQL Server par l'intermédiaire de SMO il existe comme avec ADO.Net deux modes principaux : authentification Windows et authentification SQL Server, cela dépend de la configuration de votre serveur SQL Server.

  • Authentification Windows :
Avec l'authentification Windows activé sur SQL Server vous n'aurez aucune difficulté pour créer une connexion, en effet celle-ci est créée en même temps que l'objet SMO Server que vous utilisez. 

Authentification Windows :
Public Function connexionSMO() As Boolean

	Dim monServeur As New Server(Environment.MachineName)
	
End Function
Vous voyez qu'il est difficile de faire plus simple, j'ai passé au constructeur de l'objet Server Environment.MachineName pour récupérer facilement le nom de la machine locale, car mon serveur SQL Server 2005 se trouve sur la même machine où s'exécute mon application .Net. Si tel n'est pas le cas, vous devez spécifier la machine correspondante en la passant dans le constructeur, vérifiez a bien avoir les droits nécessaires, sinon une exception sera levée.

  • Authentification SQL Server :
En cas de mode d'authentification SQL Server vous pouvez être amené à fournir des paramètres d'un compte ayant les droits nécessaires pour se connecter au serveur SQL Server. Pour cela, une fois de plus, trois lignes de codes vont suffire, vous devez utilisez la méthode ConnectionContext sur l'objet Server, ainsi vous passerez le mode d'authentification, le login et le password. Comme pour toute connexion à un SGBD il est préférable pour des raisons de sécurité et d'évolutivité de votre application de stocker vos identifiants de comptes dans un fichier de configuration et sous forme cryptée. Voici le code permettant de se "logguer" avec un compte spécifique à SQL Server par le biais de SMO. 

Authentification SQL Server :
        ' Instanciation d'un objet SMO Server
        Dim monServeur As New Server(Environment.MachineName)
        ' Paramétrage du contexte de connexion
        monServeur.ConnectionContext.LoginSecure = False
        monServeur.ConnectionContext.Login = "login_sqlServer"
        monServeur.ConnectionContext.Password = "password_sqlServer"
Simplement grâce à ces trois petites lignes de code vous pouvez utilisez SMO sans passer par une authentification Windows.

Remarque : trois surcharges du constructeur de SMO Server sont disponibles, vous pourrez donc paramétrer cela selon vos besoins, de plus n'oublier pas de spécifier l'instance de SQL Server que vous souhaitez utiliser si vous n'utilisez pas l'instance par défaut. Comme dans n'importe quelle autre situation, pour spécifier l'instance désirée, vous devez ajouter \nom_de_l'instance à la suite du nom du serveur lui-même.

3. Lister les bases de données et leurs tables

  • Lister les bases de données d'un serveur :
Ici, le but sera de récupérer le nom de toutes les bases de données présentes sur votre serveur SQL Serveur, SMO permet cela de manière aisée grâce à la propriété Databases, qui nous renvoie une liste que l'on place alors dans un objet DatabaseCollection. Mais une démonstration valant souvent beaucoup plus qu'une longue explication, passons tout de suite au code. 

Récupération de la liste des bases de données
    Public Function listerBases() As StringBuilder

        Dim liste As New StringBuilder

        ' Instanciation d'u objet SMO Server
        Dim monServeur As New Server(Environment.MachineName)
        ' Paramétrage du contexte de connexion
        monServeur.ConnectionContext.LoginSecure = False
        monServeur.ConnectionContext.Login = "login_sqlServer"
        monServeur.ConnectionContext.Password = "password_sqlServer"

        ' "bases" va contenir une collection comportant le nom des bases
        Dim bases As DatabaseCollection
        Dim uneBase As Database


        bases = monServeur.Databases


        For Each uneBase In bases

            ' utilisation de la méthode Append sur le StringBuilder "liste"
            liste.Append(uneBase.Name.ToString & "</BR>")

        Next

        ' Renvoie du StringBuilder comportant les noms des bases
        Return liste

    End Function
Comme vous pouvez le voir j'ai à l'aide d'un For Each extrait le nom de chaque base présente puis je l'ai placé dans un objet StringBuilder, j'ai en plus concaténé un retour chariot à chaque fois pour avoir un retour à la ligne dans ma page ASP.Net lors de mon Response.Write sur mon objet liste. Il est évident que selon le type de votre application et le type d'objet dans lequel vous placez ces noms de bases de données il ne vous sera pas nécessaire de procéder de la sorte.

  • Lister les tables d'une base de données d'un serveur :
Comme nous l'avons vu précédemment, grâce à SMO nous pouvons aisément lister les bases de données d'un serveur, nous pouvons faire de même pour les tables qu'elles contiennent. Il est également possible de détailler les champs contenus dans les tables, mais nous ne verrons pas ce cas ici, sachez cependant que cela est facilement réalisable en utilisant la propriété Tables de Databases, cela nous retourne une liste des tables de type TableCollection. Après un simple For Each permet de récupérer la liste des tables.


4. Création d'une base de données

Une fonctionnalité intéressante peut être la création d'une base de données lors du processus d'installation d'une application. La création d'une base, mais aussi des tables et de leurs champs, peut être entièrement réalisée par le code grâce à SMO. Il suffit pour cela de définir les paramètres que l'on souhaite (noms, types…) puis d'utiliser la méthode Create pour lancer la création dans SQL Server. Voici le code commenté qui vous permet de réaliser la création d'une base de données contenant une table et des champs, ce code est volontairement simpliste mais peut être étendu très facilement de manière à personnaliser très finement les caractéristiques de la base souhaitée. 

Code de création d'une base de données
    Public Function creationBase() As Boolean

        ' Booléen retourné par la fonction
        Dim creation As Boolean

        Try

            ' Instanciation d'un objet serveur correspondant à la machine locale
            Dim monServeur As New Server(Environment.MachineName)
            ' Définition du contexte de "connexion" avec un comtpe ayant les autorisations requises
            monServeur.ConnectionContext.LoginSecure = False
            monServeur.ConnectionContext.Login = "login_sqlServer"
            monServeur.ConnectionContext.Password = "password_sqlServer"

            ' Création d'un objet base de données, en passant le serveur et le nom souhaité de la base
            Dim maBase As New Database(monServeur, "maBaseDeTest")
            maBase.Create()

            ' Création de la table et de ses champs
            Dim maTable As New Table(maBase, "maTableDeTest")
            ' Création d'un nouveau champs
            Dim monChamp1 As New Column(maTable, "monChampDeTest1", DataType.VarChar(15))
            ' Ajout à la table du champ crée
            maTable.Columns.Add(monChamp1)
            Dim monChamp2 As New Column(maTable, "monChampDeTest2", DataType.Int)
            maTable.Columns.Add(monChamp2)
            ' Création effective de la table et de ses champs
            maTable.Create()

            creation = True

        Catch ex As SmoException

            creation = False
            Response.Write(ex.Message)

        End Try

        Return creation

    End Function

5. Mode Capture et Exécution

Une fonctionnalité particulièrement originale est incluse dans SMO, lorsque nous effectuons une opération sur une base de données depuis le code nous avons dans le contexte de connexion trois modes (SqlExecutionModes) de fonctionnement possibles.

  • Le premier, le mode CaptureSql, permet, comme son nom l'indique de capturer le script T-SQL généré par SMO. Ce script contient en fait toutes les commandes permettant d'effectuer dans SQL Server les modifications que nous avons souhaitées dans notre code. Avec le mode capture nous allons donc simplement capturer le script T-SQL généré par SMO, mais celui-ci ne sera pas exécuter. Une fois le script capturé il est placé dans ConnectionContext.CapturedSql.Text qui est un tableau de chaînes, un simple For Each permettra de le parcourir.

  • Le deuxième mode est ExecuteSql, ici nous nous contentons d'exécuter les commandes, sans que le script T-SQL soit capturé.

  • Le troisième et dernier mode, ExecuteAndCaptureSql, est en fait la combinaison des deux premiers, les commandes sont exécutées et le script T-SQL capturé.
Voici un exemple de code permettant d'utiliser le mode CaptureSql, ici notre script contiendra les commandes T-SQL permettant de créer une base avec une table et deux champs.

Pour utiliser ces différents modes d'exécution dans le contexte de connexion il est nécessaire d'importer le namespace Microsoft.SqlServer.Management.Common si l'on ne souhaite pas avoir à saisir des noms de type de données à rallonge. 


Code de capture de script T-SQL
    Public Function captureScript() As StringBuilder


        Dim monScript As New StringBuilder


        ' Instanciation d'u objet SMO Server
        Dim monServeur As New Server(Environment.MachineName)
        ' Paramétrage du contexte de connexion
        monServeur.ConnectionContext.LoginSecure = False
        monServeur.ConnectionContext.Login = "login_sqlServer"
        monServeur.ConnectionContext.Password = "password_sqlServer"
        ' Positionnement du mode d'exécution SQL, ici Capture uniquement
        monServeur.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql


        ' Voici l'action dont nous allons capturer le script T-SQL : 
        ' création d'une base de données, d'une table, et de 2 champs

        ' Création de la base de données
        Dim maBase As New Database(monServeur, "maBaseDeTest")
        maBase.Create()

        'Création de la table et de ses champs
        Dim maTable As New Table(maBase, "maTableDeTest")
        Dim monChamp1 As New Column(maTable, "monChampDeTest1", DataType.VarChar(15))
        maTable.Columns.Add(monChamp1)
        Dim monChamp2 As New Column(maTable, "monChampDeTest2", DataType.Int)
        maTable.Columns.Add(monChamp2)
        maTable.Create()


        Dim ligneScript As String

        ' For Each permettant de récupérer toutes les lignes du script
        For Each ligneScript In monServeur.ConnectionContext.CapturedSql.Text

            monScript.Append(ligneScript)
            monScript.Append("</BR>")

        Next

        Return monScript

    End Function
Remarque : la balise /BR est concaténée dans le StringBuilder uniquement pour avoir un retour à la ligne entre chaque commande, son utilité n'est donc que "esthétique" et imposé par le fait que l'on va afficher le résultat dans une page ASP.Net, si nous étions en WinForm on pourrait imaginer remplacer cette balise par "\n\r".
Voilà le script T-SQL que m'a retourné ce code : 

Script T-SQL
CREATE DATABASE [maBaseDeTest]
USE [maBaseDeTest]
CREATE TABLE [dbo].[maTableDeTest]( [monChampDeTest1] [varchar](15), [monChampDeTest2] [int] ) 
USE [master]
Ces différents modes peuvent s'avérer très utiles dans certains cas, de plus il est ainsi possible de générer des scripts T-SQL complexes sans pour autant connaitre ce langage. La gestion du contexte de connexion nous permet de réaliser cette tâche de manière aisée, puisqu'il suffit d'une ligne pour déterminer le mode d'exécution et d'une ligne supplémentaire pour récupérer le tableau de chaînes généré.


6. Sauvegarde et restauration de bases de données

  • Sauvegarde d'une base de données :
Nous allons voir comment créer une sauvegarde d'une base de données et comment la restaurer depuis du code .Net par l'intermédiaire de SMO. Pour réaliser une telle tâche nous allons utiliser l'objet SMO Backup. Vous pourrez au choix sauvegarder seulement la base de données elle-même ou les logs.

Le paramétrage des sauvegardes est assez impressionnant, on peut la paramétrer très finement grâce à de très nombreuses propriétés (comme par exemple deux modes de sauvegarde : complète ou incrémentale) et méthodes. Vous trouverez ci-dessous, un bout de code commenté permettant de sauvegarder de manière basique une base de données dans un fichier .bak sur votre disque dur. 

Sauvegarde d'une base de données
    Public Function backupBase(ByVal baseASauvergarder As String, ByVal fichierSauvegarde As String) As Boolean
    	' baseASauvergarder : base de données que l'on souhaite sauvegarder
    	' fichierSauvegarde : chemin complet de la sauvegarde, par exemple: "c:\maSauvegarde.bak"

        Dim etatSauvegarde As Boolean

        Try

            Dim monServeur As New Server(Environment.MachineName)
            monServeur.ConnectionContext.LoginSecure = False
            monServeur.ConnectionContext.Login = "login_sqlServer"
            monServeur.ConnectionContext.Password = "password_sqlServer"

            ' Instanciation d'un objet SMO.Backup qui va nous permettre de réaliser notre backup
            Dim maSauvegarde As New Backup

            ' Définition du type d'action de sauvergarde
            maSauvegarde.Action = BackupActionType.Database

            ' Base de données à sauvegarder
            '            maSauvegarde.Database = nomBaseBackup
            maSauvegarde.Database = baseASauvergarder

            ' Choix du périph et de la destination de la sauvegarde
            maSauvegarde.Devices.AddDevice(fichierSauvegarde, DeviceType.File)

            ' Réalisation de la sauvegarde
            maSauvegarde.SqlBackup(monServeur)

            etatSauvegarde = True

        Catch ex As SmoException

            etatSauvegarde = False
            Response.Write(ex.Message)

        End Try

        Return etatSauvegarde

    End Function
  • Restauration d'une base de données :
Après la sauvegarde, vient logiquement la restauration ! En effet, que faire d'une sauvegarde s'il l'on ne peut pas la restaurer… A l'image de la sauvegarde, la restauration est enfantine. Nous n'allons pas ici instancier un objet Backup, mais un objet Restore. Par l'intermédiaire des propriétés et des méthodes de cet objet vous allez paramétrer votre restauration. Voici le code commenté montrant comment réaliser une restauration d'une base de données sur SQL Server 2005 par le biais de SMO. 

Restauration d'une base de données
    Public Function restaureBase(ByVal cheminSauvegarde As String, ByVal nomBase As String) As Boolean
		' nomBase : base dans laquelle la sauvegarde doit être restaurée
		' cheminSauvegarde : chemin complet du fichier contenant la sauvegarde,
		'                    par exemple "c:\maSauvegarde.bak"
		
        Dim etatRestauration As Boolean

        Try

            Dim monServeur As New Server(Environment.MachineName)
            monServeur.ConnectionContext.LoginSecure = False
            monServeur.ConnectionContext.Login = "login_sqlServer"
            monServeur.ConnectionContext.Password = "password_sqlServer"

            ' Instanciation d'un objet SMO.Restore qui va nous permettre de réaliser notre restauration
            Dim maRestauration As New Restore

            ' Nom de la base à restaurer
            maRestauration.Database = nomBase

            ' Type de restauration : restauration d'une base de données
            maRestauration.Action = RestoreActionType.Database

            ' Chemin vers le fichier ou se trouve la sauvegarde à restaurer
            maRestauration.Devices.AddDevice(cheminSauvegarde, DeviceType.File)

            ' Action à effectuer si la base existe déjà
            maRestauration.ReplaceDatabase = True

            ' Réalisation de la restauration
            maRestauration.SqlRestore(monServeur)

            etatRestauration = True

        Catch ex As SmoException

            etatRestauration = False
            Response.Write(ex.Message)


        End Try

        Return etatRestauration

    End Function
Comme dans les autres exemples vous voyez que nous utilisons une gestion des exceptions de base pour intercepter d'éventuels problèmes. La tâche nous est grandement facilitée par les exceptions du type smoException, qui nous permettent de récupérer directement les éventuelles erreurs depuis SQL Server. Comme nous sommes dans le cadre d'une application ASP.Net dans cet article j'utilise un simple response.write() pour afficher le message associé à l'exception, cela peut bien évidemment être adapté sans difficulté à une application WinForm, par exemple en l'affichant dans une Message Box.

Voilà qui conclut cette partie sur la sauvegarde et la restauration de bases de données SQL Server grâce à SMO, les possibilités et les paramétrages sont nombreux. Comme pour toutes les autres opérations, SMO rime réellement avec simplicité.


Conclusion

Comme nous venons de le voir au cours de cet article SMO peut s'avérer très intéressant et productif. Il faut de plus garder à l'esprit que nous avons uniquement étudié un nombre restreint des fonctionnalités de SMO, en effet, il en existe des dizaines d'autres.

Vous pourrez désormais inclure facilement dans vos applications des fonctions d'administration de bases de données, et cela pour des serveurs SQL Server 2005 mais aussi 2000 et 7.0. La maintenance d'une base de données SQL Server pourra maintenant être implémentée si rapidement qu'il serait dommage de se priver de telles possibilités.

Si je devais résumer SMO en deux mots je choisirais : productivité, simplicité ! J'espère au travers de cet article vous avoir donné envie d'utiliser, ou au moins d'essayer, SMO. Je vous donne au plus vite rendez-vous dans un prochain article.


Un grand merci à Bestiol pour la relecture de cet article.


Copyright © 2005 Ronald VASSEUR. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide Microsoft DotNET - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Hébergement - Participez - Copyright © 2000-2009 www.developpez.com - Legal informations.