SMO : SQL Server 2005 et .Net 2.0

Ronald VASSEUR

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.

- Titre : SMO : SQL Server 2005 et .Net 2.0

- Auteur : Ronald VASSEUR

- Parution : 1 juillet 2005

- Mise à jour : 29 février 2020

- Licence : 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'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- I. Présentation de SMO

- II. Connexion au serveur à l'aide de SMO

- III. Lister les bases de données et leurs tables

- IV. Création d'une base de données

- V. Mode Capture et Exécution

- VI. 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 : 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.

I - Présentation de SMO

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.

II - 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, 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.

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

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"

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

Remarque : trois surcharges du constructeur de SMO Server sont disponibles, vous pourrez donc tout paramétrer selon vos besoins. De plus, n’oubliez pas de spécifier l'instance de SQL Server que vous souhaitez utiliser si ce n’est 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.

III - 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'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.

IV - 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 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

V - 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é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.

Code de capture de script T-SQL

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

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 qu’ « esthétique » et imposée 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 « \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 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é.

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

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

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

Conclusion

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.