We got an e-mail the other day from a DBA who said he didn’t see the need for PowerShell for SQL Server. He thought our time might have been spent better elsewhere. I’ll restate the reply I gave him below, but it brings up an interesting point – when we make decisions, some folks are happy, others are not, and still others are a little confused! I’ll let you take a look at the reply and see what you think. By the way, at the end I’ll include a link to someone else who buys off on the feature:
Hello there –
My name is Buck Woody, and I’ve been a DBA for a really long time. I’ve seen just tons of technologies come and go for managing or working with database technologies. My life up until a couple of years ago was in multiple-vendor environments, from Oracle to SQL Server to DB2 and even a little MySQL thrown in for luck. The primary tools I used for all that were a lot of custom scripting in Perl and HTML for reports.
I joined Microsoft two years ago and a few months ago joined the Manageability team. When I heard about PowerShell (not the SQL Provider for it) I thought the same as you – I have vbscript, Perl and all that – yawn. But I started playing around with it, and I warmed up a little. It was interesting to have a shell that also was a scripting language. And it opened up all the .NET stuff, and while I’m an admin and not a developer, it was interesting to be able to code a little here and there.
The key concept is that you can treat objects like the registry as if it were a file system, using CD and DIR, but you’re also able to sling some pretty interesting code around as well. When we created PowerShell for SQL Server, I started playing with that.
There are some interesting things you get with this provider. One is that the database objects are treated like a drive. So what? Well, you can, for instance, in just a single line DIR the top tables by rowcount (or size if that’s what you want), send that to a sort mechanism, output that to an HTML file, and save it out to Excel all at the same time. And it’s easy enough so that I don’t have to program to do that. I could also find out the databases that haven’t been backed up, send that to a report, and then back them up to a drive that has enough space. Also without code.
The other interesting thing is that you can manage not only SQL Server, but Exchange, Windows, and other Microsoft platforms – all from the same shell. And we even included the PowerShell provider as an Agent step type, so you could start up that script on lots of servers at once, on a schedule, and on and on.
Of course – you don’t have to use it. If you like SQLCMD, then there it is. If you like vbscript, there it is. But if you give the PowerShell stuff a shot, you might find it useful – especially if you work with more than just SQL Server like I did. I think as time goes on you’ll see some interesting scripts that are pretty useful, and then it might make more sense.