SMO : SQL Server 2005 et .Net 2.0

L'année 2005 sera une année importante dans les mémoires des développeurs utilisant les outils Microsoft : cette année aura vu les arrivées de rien de moins que la version 2.0 du Framework .Net ainsi que de 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, abrégé 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 possibles 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, car cela serait un travail titanesque, mais simplement de voir quelques fonctionnalités intéressantes qu'il propose.

SMO est donc un ensemble de plus d'une centaine de classes regroupées en six 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. L'idée qui nous vient donc immédiatement à l'esprit est : plus besoin d'écrire de longs et pénibles (pour ceux qui le maîtrisent 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 la référence correspondante dans la liste.

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 à réaliser, mais qu’elles seront réalisées toutes ensemble au moment où on le décide. Cela signifie que l'on ne va pas avoir besoin de créer une connexion au serveur pour chaque action et que toutes les actions seront transmises en une seule fois. Tout cela permet d'économiser un bon nombre d'allers-retours 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, qui 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.

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, qui dépendent de la configuration de votre serveur SQL Server.

• Authentification Windows

Avec l'authentification Windows activée 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.

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 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 au constructeur. Veillez à bien avoir les droits nécessaires, sinon une exception sera levée.

• Authentification SQL Server

En mode d'authentification SQL Server, vous pouvez être amené à fournir les 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 code vont suffire. Vous devez utiliser la méthode ConnectionContext sur l'objet Server pour passer le mode d'authentification, le login et le mot de passe. 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 chiffrée. Voici le code permettant de se « logguer » avec un compte spécifique à SQL Server par le biais de SMO.

        ' 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"

Grâce à ces trois petites lignes de code, vous pouvez utiliser SMO sans passer par une authentification Windows.

• 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.

    Public Function listerBases() As StringBuilder

        Dim liste As New StringBuilder

        ' 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"

        ' "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

        ' Renvoi 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 sera pas nécessaire de procéder de la sorte.

• Lister les tables d'une base de données

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, qui retourne une liste des tables de type TableCollection. Ensuite, un simple For Each permet de récupérer la liste des tables.

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.

    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 compte 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 champ
            Dim monChamp1 As New Column(maTable, "monChampDeTest1", DataType.VarChar(15))
            ' Ajout à la table du champ créé
            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

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écuté. Une fois capturé, le script 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 ne 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 types de données à rallonge.

    Public Function captureScript() As StringBuilder


        Dim monScript As New StringBuilder


        ' 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"
        ' 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

Voilà le script T-SQL que m'a retourné ce code :

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 connaître 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é.

• 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 paramétrer très finement grâce à de très nombreuses propriétés (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.

    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 sauvegarde
            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 si l'on ne peut pas la restaurer ? À 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 la restauration d'une base de données sur SQL Server 2005 par le biais de SMO.

    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 où 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é.

Comme nous venons de le voir dans 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.