Allen White

PowerShell and SMO

I started seriously trying to learn the new Windows PowerShell scripting language this week. I started with the TechNet Webcast: An Overview of Windows PowerShell (Level 200) that Don Jones did in December. I worked with Don at Microsoft on the MCITP:Database Developer Objective Domain, and he's got a great presentation style, so that complicated things become clear quickly. I then started searching for sites that specifically used SMO and PowerShell together.

After remembered having read about using SMO from PowerShell in Bob Beauchemin's blog, I downloaded his sample code and found the directory with his scripts. I had difficulty getting them to work, so started searching again.

The next thing I found was in Dan Sullivan's blog, where he used a script to initialize the environment and create some functions he calls PowerSMO. His code disallows its use in production code, though, so I continued my search.

I then found Rob Farley's blog, and he presented a much simpler script, which I was able to play with and actually start extracting information about servers other than my local server using SMO.

That, in fact, is my litmus test of a good demo - does it work against a server other than my local server? In my presentation at PASS on SMO I cautioned the attendees that my examples were in VB.Net because VBScript only allows you to connect with your local default server. Peter Ward had a session on SMO two days later where he told those present that there was "no problem" using VBScript to script SMO tasks, and then proceeded to demonstrate his code, connecting to his local default server. Don't get me wrong, I like Peter, and have learned from his sample code, but I felt he did a disservice to those present in his session.

So, now I'm working on building some useable scripts using PowerShell and SMO. I hope to put together a presentation once I get comfortable with the new language because I see amazing possibilities with it.

Allen