Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Why I Have Had a Hard Time Adopting PowerShell

PowerShell has been out for a while. When it first came out, I went and grabbed a copy and tinkered with it a bit. But really, I've not gained much expertise on it from that initial tinkering. I was sitting there thinking about that this morning and considering why that was, especially considering that Microsoft had said they want to move in that direction with respect to scripting and with respect to management of systems like Microsoft Exchange and SQL Server. And I think I've figured out why.

I Haven't Found That It Does Something I Haven't Already Gotten With Something Else I'm More Familiar With

Long title, but simple concept. Between old school VBScript script (yeah, I said it) and Perl, there hasn't been anything that I've needed that those two haven't already provided. Truth be told, Perl has stayed the mainstay ever since I learned it. So with a limited amount of time available to re-train, I'm choosing to learn things like SSIS, SSRS, Service Broker, F#, etc. You get the idea.

Have You Seen the Perl Script Libraries Out There?

I mean seriously, it's huge. So snippets of code on doing just about anything already exist. Google / Bing / {Your Favorite Search Provider Here} searches bring those snippets to my fingertips in a matter of seconds. Powershell is building and building fast. But Perl has quite a lead on it. So does, for that matter, VBScript (yeah, I went there again).

Have You Seen the Perl Packages?

I've used Perl to manage everything from Windows to SQL Server to Linux to Cisco firewalls.  I can get very, very low-level with Perl with respect to protocols and I've had to do so. In a lot of cases, folks have written packages that handle the inner guys of the communication. A great example was a package that handled connection to Cisco switches and allowed me to dump configs and the like with only a few lines of code. I really don't want to have to rewrite stuff like that. Again, I know PowerShell is the new kid on the block, but given it's relatively narrow focus, I don't see folks branching to do those types of tasks any time soon.

It's Not Installed Everywhere

VBScript used to be. Now it requires a separate install. But if I've got to do an install, and I've got to pick one thing to install, you can guess what I'm going to install (Perl). It does more for me and all the things I have my nose in. I'm glad Windows 7 and Windows Server 2008 have versions of PowerShell installed. But I still live primarily in a Windows XP/Vista/2003 world. And until those OSes start dropping off the map, this remains an issue.

I'm Lazy

There's a lot of truth in this statement. I know that unless Microsoft makes a sudden decision shift, PowerShell is going to be increasingly more important for the management of the SQL Servers I deal with. However, because I can already do everything with what I already have, there's been no pain point to make me want to actively learn PowerShell. So I've shifted it to the backburner so I can do other things. Truth be told, I should be allocating some time each day to play and learn PowerShell. Yeah, it's going on my goals list. I've done a pretty good job of learning tin whistle and right now the value of PowerShell for me is just slightly above that.



Posted by Brent Ozar on 14 April 2010

I agree 100%.  I know I should be learning it, but it's just too easy to fall back on the scripting stuff I already know and love - and it works already.

Posted by Kendal Van Dyke on 14 April 2010

I agree, the motivation just isn't there for someone to invest a significant amount of time learning how to do something they know how to do in another language. Put another way, there's more of a reward for learning something new than learning how to do something over again. For someone who is picking a scripting language to learn for the first time PS makes a lot of sense though.

Posted by Chris_K on 14 April 2010

Personally, powershell fascinates me.

However, I'll be in Win2k3 / SQL 2k5 land for a long time... there's just nothing there for me until we make some big upgrades.

Posted by K. Brian Kelley on 14 April 2010

Kendall, I agree if you were staying in the Microsoft-centric world. There's really no reason to learn anything else. And truth be told, if I had a choice way back when between learning VBscript and PowerShell, I would have gone with PowerShell. The only real reason I mastered VBscript was because I already knew VB and I was writing ASP code.

On the other hand, if you think you might branch outside of the Microsoft world, such as into VMware, Perl becomes compelling again.

Posted by Robert Davis on 14 April 2010

I used to use VBScript a lot, and now I use PowerShell quite a bit. Personally, I love being able to use SMO with it. I used SQL-DMO back in SQL 2000, but I did not adopt SMO when SQL 2005 came out because it wasn't a scripting language, and it was too big of a jump to go from scripts to managed code. Now, PowerShell makes SMO (and AMO, RMO) a scripting language.

There are things you can do with SMO that is documented and supported that you can't do with T-SQL unless you use undocumented, unsupported commands like xp_instance_regread and xp_instance_regwrite. So things I have done in the past that were undocumented can now be done via script in a documented, supported fashion.

Also, I wrote a PS script to perform database mirroring using SMO (not passing in T-SQL), and one of the things I can do with SMO that I can't do with TSQL is programmatically verify availability of ports before assigning them to the endpoints. So if you get creative and move outside of just what you get with SMO and the other SQL assemblies, then you open up a lot of possibilities.

I'm not saying that PowerShell is currently as powerful as PERL, but I haven't done any Perl scripting in about 9 years, and I'm not really all that interested in getting back into it.

Posted by Brent Ozar on 14 April 2010

Robert Davis is a genius. That is all.

Posted by K. Brian Kelley on 14 April 2010

Robert, that's a great argument about why PowerShell is powerful if that's your primary scripting language. I can understand why some folks have adopted it. And if you're Windows centric, it's probably the direction you should go.

In relation to all that, just so there's no misconceptions from others who aren't familiar with what Perl can do, Perl can use SMO ( and things like registry reads/writes we do all the time through Perl with the default packages that come with the ActiveState ActivePerl builds.

Posted by on 14 April 2010

Well said Brian!

Like some other things, I delayed a bit learning PowerShell. I'm always a bit nervous embracing something new from Microsoft since they may decide at some point in the near future that it didn't gain enough of a foothold to warrant continuing the technology. I don't have the time to learn some that will be deprecated in a couple of years.

Since then however, I've learned a bit about PowerShell and it's very nice in many respects. I like it a lot better the VBScript.

As I type this on my MacBook Pro, I realize that you make a very compelling argument about Perl's portability, especially when compare to PowerShell.



Posted by Grant Fritchey on 14 April 2010

Not having learned VBScript back in the day (I was too busy with actual VB, Java and then C#) and actively loathing Perl, I needed a scripting language right about the time that Powershell came along. I'm still struggling with it (but that's my issue, not Powershell's) but I think it's worth it because it's getting so hooked into all the MS product set.

However, I'm with you. If you really do already know a good scripting language, it probably doesn't make any sense to learn this one.

Posted by Roy Ernest on 14 April 2010

Power shell is good but it is also vast. There are lots of things you need to learn before you can do anything significant with it. And it is not easy as everyone claims. (As far as I am concerned) I did write a blog regarding this.

Posted by Tim Benninghoff on 14 April 2010

Lots of people put a heavy focus on the scripting aspect of PowerShell, and when you compare it to what folks can already do with the languages you mentioned, why not stick with what you're comfortable with?

However, I will say that I use it more and more just as a shell, a place I can type out a quick command on one line to get the immediate information I want.  The language is certainly not universally intuitive, and there are places that it bugs the heck out of me, but there are some places where PowerShell contains some real elegance.  Like, when I want to know how many days between 2 dates, it's as simple as subtracting:

[datetime]'8/7/2010' - [datetime]'4/23/2010'

I don't have to try to remember datetime functions, etc.

Or when I want to see the services running on a machine, but can't be bothered to RDP to it and pull up the Services gui:

gwmi win32_service -computername <name> | Select Name, State

But, you know, maybe you already have a shell you're running for Perl.  In that case, I got nuthin'.

Posted by K. Brian Kelley on 14 April 2010

Tim the calculation is true, I like that aspect of it. But things like services... there's already sc. And that's really the point I'm making... a lot of what PowerShell does is already done by other tools that have been provided by MS. What isn't done by other tools has been covered by something else already in our personal toolboxes. So finding a compelling reason to learn has been difficult for me.

Posted by Robert Davis on 14 April 2010

I agree. If I was already using another scripting language that did everything I wanted it to do, I probably wouldn't have bothered learning PS either.

Posted by Steve Jones on 14 April 2010

Nice writeup, and good reasons. For me, #1 still rules as well. I'm just familiar with other tools that do the job. However if I were doing more Windows admin and scripting, and had more time, I think I'd make the investment in Powershell. It seems to be gaining enough traction to make it worthwhile.

Posted by wayne.arant on 14 April 2010

I feel a bit out of my league here, (I don't know much of anything about SQL and only use Perl lightly) but I have to agree completely that nothing has really compelled me to learn powershell. Anything I can't do in batch or a quick VB Script, I just write a .net command line app for. It is quick easy, and far easier to read/use.

Now I would like to know more about f#, it looks awesome!

Posted by cmille19 on 15 April 2010

I can see your point about Perl--If you know Perl then building the case to learn PowerShell is more difficult. Linchi Shea’s book, Real World SQL Server Administration with Perl is still one of the best scripting book for SQL Server DBAs. Even though I’ve been heavily using PowerShell for several years I still refer to Shea’s book at times. That said, having used Perl, the learning curve is much steeper than PowerShell. The main issue I have with Perl when compared to PowerShell is in the development/scripting model (parse and pray vs. working with objects)…

In Perl you typically find a command-line tool that produces the output you want albeit not in a usable format (Sure, sometimes you might get lucky and find a Perl module that does what you want, however when I used Perl I was never that lucky). This may involve several command lines tools. You’ll then parse the output of the tool and perhaps even use the output as input to other command-line tools.

If you’re parsing something in PowerShell you’re doing it the hard way. This is because PowerShell works with .NET, WMI and COM and you’ll typically find a .NET/WMI class or COM interface to do exactly what you want to do. No extra packages to install, it’s just there in PowerShell natively. PowerShell emits objects. Objects have properties and method you can work with directly. For example this one line of PowerShell code exposes over 97 properties of a SQL Server:

new-object (”Microsoft.SqlServer.Management.Smo.Server”) ‘Z002\SQL2K8′

I don't buy the VBScript argument. Any programming language that can't take of advantage of .NET on the Windows platform isn't worth learning. Because SMO is a set of .NET assemblies you can’t work with SMO in VBScript. Although you can work with SMO’s predecessor SQL-DMO, it is deprecated.

Because you work with the same .NET classes in PowerShell as C# and VB.NET if you’re coming from .NET background learning PowerShell is easier.  If you don’t know .NET then the investment you make in PowerShell pays dividends should you decide to move onto C# or VB.NET.  Because PowerShell support is part of Microsoft’s Common Engineering Criteria which means all releases of server products must support PowerShell, learning Powershell allows you to move from scripting SQL, Windows, SCOM, SharePoint and Hyper-V using a single automation language.

I’m not saying Perl is bad and PowerShell is good although I am saying VBScript is just plain awful:). Ultimately it’s about choosing the right tool for the job and hopefully you get the added benefit in applying the tool concepts to other things. If you need to work with non-Windows devices or parse Cisco logs then Perl is probably your best choice. On the other hand if you need to script in a Window environment against .NET or WMI then PowerShell is the better option.

Posted by K. Brian Kelley on 16 April 2010

Just a clarification: Perl can work with objects. In fact, on the Windows side, we often do. It has access to COM, .NET (with the right package), and WMI as well. And just as with PowerShell, the methods and properties are exposed.

With respect to VBscript, because you can't use SMO, I agree, there's a limitation. But for a lot of the day-in/day-out stuff, keep in mind that's not really a limitation. Yes, SQL-DMO is deprecated. But straight T-SQL is not. And establishing a connection and executing T-SQL is not an issue. As for other things like reading the registry, etc., those don't have to go through SMO. There are direct methods to it. I will say, though, that if you are learning a new scripting language, don't start with VBscript/Jscript. One big negative is the lack of solid error handling. That's always been the biggest knock against VBscript/Jscript. And now you have to do an install to get the pieces on the box, so you might as well go the PowerShell route.

Posted by cmille19 on 16 April 2010

Agreed Perl can work with objects, but my point is that when I used Perl it was more about parsing output than anything else. In Perl I also had to fiddle around with formatting the text outputted to the command-line. The thing about objects is PowerShell is that everything is truly an object and you are outputing an object so there's no messing around or worrying about how text will be emitted. This is because of a very rich formatting and type handling system in PowerShell. The formatting and type handling makes outputing commands to multiple formats (CSV, HTML, text) extremely easy. Having access to objects on the command-line means I work natively with the properties and methods without writing a bunch of code. Objects in PowerShell are first class citizens.

Posted by monkey_vegas on 2 May 2010

Well, since PowerShell syntax is heavily 'inspired' by Perl syntax, might as well go to the source.

Leave a Comment

Please register or log in to leave a comment.