0

Scripting et SMO (suite): scripter les objets directement en T-SQL

Posted on by David Baffaleuf
twitterlinkedinmail

Suite de l’article précédent, où il était question de générer le DDL d’une base avec C# et SMO. Si on pousse l’idée un peu plus loin, il serait séduisant de pouvoir le faire directement dans une procédure stockée, en s’interfaçant avec SMO et sa méthode Script(). La première solution qui vient à l’esprit, c’est évidemment SQLCLR.

SQLCLR vs COM Automation:

Seulement voilà, on s’y casse un peu les dents car comme le dit Bob Beauchemin, SMO n’est pas supporté dans SQLCLR. Donc à priori, adieu la méthode Script(), et retour au clic-droit …

Cet article propose une alternative qui utilise  COM Automation (ou OLE Automation). C’est une fonctionnalité qui permet de charger un composant COM et d’utiliser ses méthodes grâce à des procédures stockées étendues sp_OA… C’est un peu l’ancêtre de SQLCLR, mais avec moins de restrictions, donc plus de risques à l’utilisation, c’est la raison pour laquelle cette technique est pas mal décriée.

D’ailleurs, en théorie utiliser du SMO avec OLE Automation n’est pas supporté par SQL Server, pour des raisons de sécurité du code notamment, parce que par défaut la DLL externe que l’on souhaite utiliser est chargée dans le process address space du moteur, ce qui augmente le risque d’écraser de la mémoire liée à SQL Server et de crasher le processus tout entier ou de provoquer des fuites mémoire. Il existe tout un tas de recommandations contre l’usage de COM Automation avec SQL Server, et  beaucoup de cas ont été ouverts au support à cause de ça. Il faut à chaque fois que c’est possible lui préférer l’utilisation de SQLCLR. Sauf que dans notre cas, SMO n’étant  pas utilisable dans du SQLCLR, il ne reste que peu d’options.

Une technique acceptable pour utiliser COM Automation sans mettre en péril l’instance est de charger le COM dans un processus séparé et dédié, pour que tout problème de pointeur NULL ou de  garbage collection dans le COM n’affecte pas SQL Server. Par exemple dans un web service accessible par Service Broker comme le propose Simon Sabin, ou dans un serveur COM+ comme nous allons le voir ici. Donc en résumé:

Ecrire le COM object:

COM est un truc énorme et je ne peux pas en parler dans un article. Il faudrait un bon millier de pages pour décrire ce que c’est dans les détails et ce que ça fait, mais disons que l’idée derrière COM est qu’un objet puisse publier ses classes et ses méthodes pour permettre à des applications tierces de pouvoir les utiliser sans pour autant divulguer son code source. Les objets COM existent le plus souvent sous la forme de librairies dynamiques (DLL). Le principe est utilisé massivement dans à peu près toutes les applications Windows MFC ou Console, y compris SQL Server.

Pour pouvoir être réutilisé, un objet COM doit posséder les caractéristiques suivantes:
– Ses classes doivent être publiques et implémenter une interface publique.
– Chaque classe doit posséder un constructeur par défaut (vide).
– La DLL doit être signée.
– La DLL doit être enregistrée sur la machine sur laquelle on va l’utiliser.

Par exemple pour scripter juste une table:

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace getDDL
{
     public interface IgetDDL    // <- 1
     {
         string ddlThis(string _srvname, string _dbname, int _objid);
     }
     public class cgetDDL : IgetDDL     // <- 2
     {
         string ddlstr;

         public cgetDDL() { }     // <- 3

         public string ddlThis(string _srvname, string _dbname, int _objid)     // <- 4
         {           
             Server Srv = new Server(_srvname);
             Database Db = new Database();
             Db = Srv.Databases[_dbname];
             StringCollection _tablescr = Db.Tables.ItemById(_objid).Script();

             foreach (string _line in _tablescr)
                 ddlstr += _line;
             ddlstr += "GO"+'\n';

             IndexCollection _indexes = _t.Indexes;
             foreach (Index _i in _indexes)
             {
                 StringCollection _iscr = _i.Script();
                 foreach (string _line in _iscr)
                     ddlstr += _line + '\n';
             }
             if (_indexes.Count != 0)
                 ddlstr += "GO" + '\n';

             CheckCollection _checks = _t.Checks;
             foreach (Check _c in _checks)
             {
                 StringCollection _cscr = _c.Script();
                 foreach (string _line in _cscr)
                     ddlstr += _line + '\n';
             }
             if (_checks.Count != 0)
                 ddlstr += "GO" + '\n';

             TriggerCollection _triggers = _t.Triggers;
             foreach (Trigger _trg in _triggers)
             {
                 StringCollection _trgscr = _trg.Script();
                 foreach (string _line in _trgscr)
                     ddlstr += _line + '\n';
             }
             if (_triggers.Count != 0)
                 ddlstr += "GO" + '\n';

             foreach (Column _col in _t.Columns)
             {
                 if (_col.DefaultConstraint != null)
                 {
                     StringCollection _def = _col.DefaultConstraint.Script();
                     foreach (string _line in _def)
                         ddlstr += _line + '\n';
                     ddlstr += "GO" + '\n';
                 }
            }

             return ddlstr;
         }
     }
}

–> 1: Déclaration de l’interface publique, qui va publier aux applications externes l’unique méthode de la classe CgetDDL :: ddlThis()
–> 2: Déclaration de la classe publique qui implémente l’interface.
–> 3: Déclaration de son constructeur par défaut.
–> 4: Déclaration de la méthode, qui prend en arguments le nom de l’instance, le nom de la base et l’object_id de la table à scripter. Pour le reste, cf l’article précédent.

Il faudra également modifier la propriété ComVisible de l’assembly pour permettre d’y accéder depuis l’extérieur. Dans assembly.cs:

[assembly: ComVisible(true)]

Enfin, il faut signer la DLL avec un nom fort que l’on pourra générer avec VS:

Chargement du COM dans un serveur COM+:

Une fois la DLL compilée, il va falloir l’enregistrer sur la machine et générer un fichier Type Library (*.tlb) que l’on va utiliser ensuite dans le serveur COM+. Pour enregistrer une dll et générer le tlb, on utilise l’utilitaire regasm qui est fournit avec tous les frameworks .NET (et qui doit se trouver qq part sous C:\Windows\Microsoft .NET\Framework\…):

DOS >regasm /codebase getDDL.dll /tlb:getDDL.tlb
Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.3053
Copyright (C) Microsoft Corporation 1998-2004. Tous droits réservés.

Inscription des types réussie
L'assembly a été exporté vers 'C:\DBA\DEV\C#\getDDL\bin\Debug\getDDL.tlb' et la bibliothèque de types a été inscrite avec succès

A partir de là, on va créer un nouveau serveur COM+ en utilisant le service de composants comexp.msc: dans Services des composants -> Ordinateurs -> Poste de travail -> Applications COM+, on créé une nouvelle application serveur vide, démarrée sous un compte windows qui serait reconnu comme sysadmin sur l’instance (par exemple l’admin local, ou tout autre compte windows sysadmin). On dépose ensuite le fichier tlb dans les composants de la nouvelle application COM+, et on vérifie si la méthode est bien exposée:

Enfin on démarre le serveur COM+, et on vérifie qu’un nouveau processus hôte de dll (dllhost.exe) est bien démarré avec le bon compte. On va pouvoir s’intéresser à la partie T-SQL.

Ecriture de la procédure stockée:

Avant toute chose, pour pouvoir autoriser SQL Server à utiliser Ole Automation, il faut l’activer dans les paramètres de configuration:

sp_configure 'Ole Automation Procedures',1;
GO
reconfigure;
GO

Ensuite, on créé la procédure stockée qui va charger le COM et utiliser la méthode ddlThis():

create procedure usp_getDDL (@tablename varchar(128))
AS
BEGIN
     DECLARE @hr int, @obj int;
     DECLARE @Source nvarchar(255), @Desc nvarchar(255);
     DECLARE @DDLTEXT nvarchar(4000);
     DECLARE @dbname sysname, @objid int, @srvname sysname;

     select @srvname = @@servername
     select @dbname= db_name()
     select @objid = object_id(@tablename)
     if (@objid is NULL)
     BEGIN
         print 'Table '+@tablename+' non trouvee dans la base courante'
         RETURN
     END

     execute @hr = sp_OACreate 'getDDL.cgetDDL', @obj OUT, 4          --> context = 4 => COM stocké dans un dllhost.exe, pas dans sqlservr.exe
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OAMethod @obj, 'ddlThis', @DDLTEXT OUT, @srvname, @dbname, @objid
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OADestroy @obj

     select @DDLTEXT as 'DDL'
END

La procédure récupère le nom de la table, vérifie si celle-ci existe dans la base courante, et charge le COM dans le dllhost.exe avec sp_OACreate. Le nom du COM object est celui déclaré dans le serveur COM+ . Un autre paramètre d’extrême importance est le dernier passé à sp_OACreate  (4). Il s’agit du contexte de chargement du COM, dans ce cas 4 indique à SQL Server que le composant sera chargé en dehors de son process address space et ne peut avoir accès aux ressources mémoire dans sqlservr.exe (cf MSDN). C’est notre garde-fou et c’est indispensable, car par défaut, la ddl aurait été chargée dans sqlservr.exe et aurait pû causer des dégâts, il est donc très important de procéder de cette manière.

Ensuite la procédure appelle sp_OAMethod pour invoquer la méthode exposée par l’interface du COM, et récupère le DDL dans un nvarchar(4000). A la fin du processus, la référence vers l’objet est détruite avec sp_OADestroy.

A l’appel de la procédure:

DOS>sqlcmd -E
1> use CAPDATA
2> go
Le contexte de la base de données a changé ; il est maintenant 'CAPDATA'.
1> exec usp_getddl 'ANNONCE'
2> go

DDL
------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ANNONCE](
 [ID_ANNONCE] [int] NOT NULL,
 [ID_JOURNAL] [int] NOT NULL,
 [ID_PROPALOUER] [int] NOT NULL,
 [DATETIME_ANNONCE] [datetime] NOT NULL,
 [PRIX] [numeric](6, 2) NULL,
 [NB_PARUTION] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ANNONCE] ADD  CONSTRAINT [PK_ANNONCE] PRIMARY KEY CLUSTERED
(
 [ID_ANNONCE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Conclusion:

COM ou Ole Automation est une technique pour faire communiquer SQL Server avec des composants externes. Elle  présente des risques élevés de corruption mémoire lorsqu’on l’utilise avec les paramètres de contexte par défaut, mais lorsqu’on manipule l’objet à travers son propre conteneur, les risques sont limités.

Dans la mesure où SMO n’est pas supporté avec SQLCLR, elle représente une alternative intéressante pour pouvoir interfacer SQL Server avec des fonctions qui ne sont pas disponibles ailleurs.

A+. David B.

Continuez votre lecture sur le blog :

twitterlinkedinmail

David Baffaleuf

More Posts

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.