Perl and Server Management Objects (SMO)?

No, I do not have squiggly worms in my head, and no, I haven’t gone over to the dark-side. It’s just that I had an opportunity over the past few days to attempt to use Microsoft’s Server Management Objects (SMO) with Perl to manage a SQL Server 2005 DB.

To make a long story into a short post, I blundered into the Win32::CLR module on CPAN, a little gem from Toshiyuki Yamato.

Here is all you need to get started.

1. Perl 5.10 (Activestate and Strawberry Perl both work fine.)
2. The Win32::CLR module.
3. MS Visual C++ Express 2005 or 2008 (it is free and you can get it here: www.microsoft.com/express/download/#webInstall)
4. Open a DOS prompt from Visual C++ and Makefile and the nmake install Win32::CLR.

Once you have a working version of Win32::CLR in your Perl installation, you should check that you have the SMO objects installed on you computer. These are normally stored in c:\Program Files\Microsoft SQL Server\100\SDK\Assemblies. The DLL we are interested in is called Microsoft.SqlServer.Smo.dll. If it is not there, you will most likely have to get the latest Service Pack from Microsoft.

Finally, so that Perl can find it, you will have to make sure that you put the directory of the DLL in the path.

Once you have jumped these little hurdles, you can then get down to coding. As good Perl programmers, we start with:

use strict;

and then use our CLR mod:

use Win32::CLR;
use utf8;
binmode STDOUT, ":encoding(Shift_JIS)"; # japanese character set
binmode STDERR, ":encoding(sjis)"; # if japanese windows

(I am not sure why we have to use the binmode here, so if you want an answer you will have to ask Toshi.)

We then load the SMO DLL . . .

my $smo= Win32::CLR->load_from("Microsoft.SqlServer.Smo.dll");

. . . and create a server object that we can play with.

my $svr = Win32::CLR->create_instance("Microsoft.SqlServer.Management.Smo.Server");
my $conn=$svr->get_property("ConnectionContext");
$conn->set_property("LoginSecure",0);
$conn->set_property("DatabaseName","xxx");
$conn->set_property("Login","xxx") ;
$conn->set_property("Password","xxx");

The above will set up the connection for us and then . . .

print "Edition=".$svr->get_property("Edition")."\n";

. . . will connect to the server and print out the Edition details. So if you run it you will see:

Edition=Enterprise Evaluation Edition (64-bit)

Now for the big question: Why?

For one thing, ‘SQL-DMO’, which I have been using with Win32::OLE, is on the way out for any SQL Server edition after 2005.

Second, I though I would give PowerShell a try as a replacement for my Perl Scripts. But, after a few hours trying to get my head around it, I found it was just going to be easier to use Perl for what I had to do.

I think that anyone using PowerShell might find that the above Perl solution will pay off in the long run, as any program you write in Perl can be fully modular or even object oriented in design, whereas PowerShell’s OO is more limited.

As a bonus, I found that the monitoring footprint (disk-space, memory usage, CPU usage) on my server was smaller with Perl than with PowerShell, and very much smaller than a similar C# .Net app.

Of course there are some limitations in using Win32::CLR and SMO, but nothing insurmountable. You will have to write a number of little helper subroutines because Win32::CLR exposes only the Base Class Library of objects, and not the many wrappers that one is more familiar with.

But that is another story—one that I’ll tell in an upcoming post.