SQL Server 2008 Management Improvements – SQL Server Provider for PowerShell (Part 1)

At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post I’ll talk about one of those new improvements – our new PowerShell provider for SQL Server.


I’m going to predict that this improvement will be the “sleeper” of SQL Server 2008. A sleeper is a feature that people generally aren’t aware of or don’t use at first, but quickly find out its value. It then becomes wildly popular.


Why do I say this? Well, I guess it’s because as a DBA myself, I know that I don’t have a lot of time to learn something new. I have enough work to do just keeping the systems up and running with the methods I’ve already learned, and since that is working, I don’t see a need to change.


Until, of course, I find out that I have new servers coming, with more features, and with more surface to manage. Oh, and of course I don’t get any more headcount, so it’s the ever popular trio of me, myself, and I  that have to do the work. Then I start looking for ways to do things faster-better-cheaper, and that’s where PowerShell comes in.


What is PowerShell? Well, I’ve done a series of articles on that, and you can certainly find out more on the web, but in a nutshell it’s a new scripting language. “Great”, I hear you say, “That’s just what I needed. And you do know I’m not a developer, right? I really don’t have time to learn to program.” And this is exactly why I think this will be the sleeper feature. You see, there are really only two things you have to know about PowerShell to jump in and get started: Everything can be an object, and Pipes can send things from one place to another.


Let’s start with that first one. The word “Object” sounds awfully program-ery to me, but it really isn’t. When you open a command prompt today and type TYPE TEST.TXT, you’re really just using a command (TYPE) on an object (TEST.TXT). Easy peasy – you’ve done this lots of times.  The key is that the TEST.TXT part is an object. You might guess that it is a text file (it is) and that it has various things you can see – the name, the length, and so on.


PowerShell has a bunch of commands as well, just like your command prompt, called Command-lets. For instance, the same command we just typed is available in PowerShell:


get-Content TEST.TXT


This get-Content command is “aliased” to TYPE and CAT, so you don’t have to actually type it that way – TYPE TEST.TXT works fine.


Now here’s where it gets powerful – you can set a variable to an output and the variable becomes a pointer to the object, like this:


$MyFile = get-Content TEST.TXT


Try that (assuming that you have a TEST.TXT file in the same directory) and then try typing this:



How cool is that!  OK, that’s enough for one post – we’ll come back to this tomorrow, when I’ve had more sleep.


Skip to main content