I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes. I learn therefore I am! I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed [...]
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.
Here is all you need to get started.
- Perl 5.10 (Activestate and Strawberry Perl both work fine.)
- The Win32::CLR module.
- MS Visual C++ Express 2005 or 2008 (it is free and you can get it here: www.microsoft.com/express/download/#webInstall)
- 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:
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 . . .
. . . 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.
Most people think Windows administrators make a living with their right-hand—you know, right-clicking and left-clicking the user interface to get things done. While anybody can do that in Windows, the real value comes in when you no longer need to rely so much on the user interface but instead write scripts. Lower total cost of ownership is achieved when the administrative costs are kept low, and this is where Windows PowerShell comes in.
I’ve spent a fair amount of time writing VBScript scripts to administer Windows servers and workstations and automating repetitive tasks. One reason for me moving into Windows PowerShell is its roots in the Microsoft .NET Framework, as I have done a fair amount of .NET programming. But what is Windows PowerShell anyway?
Windows PowerShell is an extensible command-line shell and an associated scripting language built on top of the .NET Framework v2.0. It was released in 2006 and is currently available for Windows XP SP2/SP3, Windows Server 2003, Windows Vista, and is included in Windows Server 2008.
PowerShell will be included as a common engineering criteria (CEC) in future releases of Microsoft server products, making it a must-learn for Microsoft server administrators.
Administrators (DBAs included) have been using scripting to automate administrative tasks with scripting languages like DOS batch, VBScript, Perl, and a few third-party tools like KiXtart and WinScript. Windows PowerShell complements the administrators’ existing scripting toolkit to easily manage and administer Windows workstations and servers and other Microsoft server products as THEY are being built using the .NET Framework.
Although it is designed for operating systems, Windows PowerShell can be used to administer SQL Server 2005 instances and higher, as Server Management Objects—the object model used to manage SQL Server 2005—are built using the .NET Framework, thus exposing the object model in PowerShell. And since SMO is compatible with SQL Server 2000, you can administer SQL Server 2000 instances using Windows PowerShell. SQL Server 2008 even ships with its own PowerShell snap-in.
No wonder it makes sense to learn a thing or two about Windows PowerShell. Besides, I’ve seen Windows administrators being “forced” to do SQL Server DBA tasks even without knowing what T-SQL is. Windows PowerShell makes it a level playing field.
I will be posting a series of blog posts on getting started with Windows PowerShell, and how any Windows administrator can use it for their day-to-day tasks. In the process, I’ll also cover how to use Windows PowerShell for administering SQL Server instances.
The slides are brilliant even without seeing Theo talk and I highly recommend the time it takes to flip through them, for anyone who is interested in systems performance. If anyone took an mp3 of this talk I’m dying to hear it, please let me know.
For those of you unfamiliar with OmniTI, Theo is the CEO of this rather remarkable company specializing in Internet-scale architecture consulting. They generalize on Internet-scale architecture, not on one specific dimension the way Pythian specializes on the database tier. This allows them to see Internet-scale workloads from a unique systemic, multidisciplinary point of view; from the user experience all the way up the stack, through the load balancer (or not), the front-end cache, the application server, the database server, the operating system, the storage, and so on. This approach lets them build Internet architectures and solve scalability problems in a unique and powerful, wholistic way.
Pythian first collaborated with OmniTI in 2001, and they deserve all of their success and profile that they’ve built since then. Trivia: both Pythian and OmniTI were founded in September 1997 and both companies continue to be majority-owned and controlled by founders (in Pythian’s case, yours truly).
Here’s the slide deck. Let me know your thoughts.
In about 15 minutes, Giuseppe Maxia will begin a webinar in which the main focus is a presentation on “How to have a good presentation”. Talk about meta!
Giuseppe posted how to join the free webinar.
The slides can be found at http://datacharmer.org/downloads/2009_03_Presentation.pdf.
Welcome to the 128th edition of Log Buffer, the weekly review of database blogs.
Let’s begin with some PostgreSQL blogs. Jignesh Shah shares his recipe for making a PostgreSQL 8.3 appliance based on OpenSolaris using VirtualBox. While we’re on appliances, Dave Page shows off PostgreSQL management on the iPhone with an application he himself wrote. Stealth DBA for the bus-rise home.
On Database Soup, Josh Berkus has been finding useless indexes. He begins, “I’d say, in general, that you can’t have really well-chosen indexes without the help of a performance expert. However, anyone can improve their use of indexes in PostgreSQL fairly easily using a few system tools … and a little improvement is all that a lot of users need.” And it’s what Josh offers.
Sometimes a DBA is asked to make something real purty-like, contrary to his or her nature though that may be. On the Postgres OnLine Journal, Leo Hsu and Regina Obe offer some help with the first of a series on Fusion charts and PostgreSQL. (”Fusion Charts . . . is a flash-based charting product that makes beautiful flash charts.”)
And now—hey what’s MySQL maven Baron Schwartz doing with a Postgres post on xaprb? He’s asking, what are your favorite PostgreSQL performance resources?
Maybe he’s considering crossing the floor out of weariness with all the contention in the MySQL world? Can’t say I blame him. Lately, the conversation in the MySQL ’sphere has been dominated by non-technical talk of the pluses and minuses of 5.1, of forking, community vs. enterprise, and so on. This week was no exception.
The week began with Jay Pipes’s advice to MySQL: “Drop the current roadmap . . . Forget Windows for now . . . Clean up the abysmal messiness of the code base . . .” It’s strong stuff and worth a read.
Speaking of which, the MySQL Performance Blog announced the Percona XtraDB Storage Engine: a drop-in replacement for standard InnoDB.
On High Availability MySQL, Mark Callaghan showed us how to make MySQL faster in one hour. Nice stuff. And real purty charts, too.
Let’s see what happened in SQL Server now. Kalen Delany opined that there is no such thing as a SQL Server, and she’s not the only one with an opinion on this (one would think) straight-forward matter.
Lots of thoughtful comment on that one, and a blog response from Ward Pond, who says that Linchi Shea makes an interesting point about hints, vis-a-vis the set-based and procedural paradigms.
The Data Management Journal looked into extracting numbers with SQL Server: “We all have perfectly normalized tables, with perfectly scrubbed data, right? I wish! Sometimes we are stuck with dirty data in legacy applications. What’s worse is that we are sometimes expected to do interesting things with dirty data. In this blog, I will show you how to extract a number from a varchar column that contains letters and numbers.”
Moving into things Oracle, Chen Shapira was thinking about a musical analogy for the DBA. Their not “rockstar programmers” or “jazz programmers”, says Chen. But I won’t give away her conclusion—click on.
Chet Justice, the Oracle Nerd, was pursuing ontology too in the second part of his Application Developers vs. Database Developers. (I wonder if it’s generally true that apps developers have such terrible manners.)
On An Expert’s Guide to Database Solutions, James Koopman suggested, maybe itÃ¢â‚¬â„¢s time to extend the DBAÃ¢â‚¬â„¢s realm of influence, using tools like Spotlight on Oracle.
Or perhaps with other tools, such as TOra? Here on the Pythian Blog, Brad Hudson posted his howto, Installing TOra with Oracle support on Ubuntu 8.04LTS (Hardy Heron).
Until next time, Happy Holidays to all our readers!
I’m pleased to announce that there will be the formal launch of Pythian Europe at the premises of the Canadian Embassy in Prague on Wednesday the 6th of August from 17:00 to 18:30. This historic event will be announced by Mrs. Sameena Qureshi, Trade Counsellor, Embassy of Canada; and Paul VallÃ©e, President and Founder, The Pythian Group. Present will be various members from the press (IT and Business), as well as representatives from Oracle and Sun Microsystems, the Canadian Chamber of Commerce in Prague, and many more. We will prepare some unusual and very tasty snacks and refreshments.
We would love for readers of this blog to join us, so please consider this your special, personal invitation from me. Please come if you’re in Prague on Wednesday. If you plan to attend, please contact Dan at firstname.lastname@example.org.
I am traveling to Europe next week to brief major prospects in Germany (Daimler, MAN) as well as to attend to administrative matters at Pythian Europe in Prague and would love to meet any readers of this blog during this trip!
I’m especially interested in meeting:
- DBAs, Applications Administrators and Systems Administrators,
- Potential customers (IT Directors, DBA Managers, Supply Managers for IT), and
- Potential partners (IT product of service companies that could partner with Pythian to delight our mutual customers)
Here is my itinerary:
- Sunday, August: Frankfurt,
- Monday, August 4: Stuttgart,
- Tuesday, August 5: Munich, and
- Wednesday, August 6 through Saturday, August 9: Prague, Czech Republic.
Please reach out to me using email@example.com if you would like to meet!