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.


Comments (2)

  1. Have you ever seen or used the PowerShell? If so have you worked on PowerShell within SQL Server 2008,

Skip to main content