Installing PowerShell

Many DBAs have heard about the scripting technology from Microsoft, but have yet to really dive right in.

• By Joshua Jones
• 09/10/2009

You may have heard about a new scripting language for Windows admins: PowerShell. PowerShell isn't just for admins, though. It has a lot to offer SQL Server DBAs. In this series, I'll guide you through getting your feet wet with PowerShell, understanding what it can and can't do, and take an in-depth look at many of the specific features of PowerShell that can make your life easier.

For those of you who are unfamiliar, let me give you a very brief overview of PowerShell as a technology. PowerShell is, for lack of a better word, the next generation of scripting and command line functionality from Microsoft. At first glance, it looks like an old fashioned DOS prompt on steroids, but really it's much more than that. Where the old command prompt provided very limited scripting and the ability to call other executables, PowerShell functions more like an entire subsystem that happens to be a command shell.

The root of its usefulness is the .NET Framework. Because PowerShell relies on the .NETFX, it has the ability to access and manipulate almost any object in Windows (via WMI), as well as other Microsoft products, such as SQL Server. This allows administrators and programmers a great deal of flexibility in creating automated processes from the command line.

PowerShell comes with a plethora of applications, called cmdlets, that let you execute any number of tasks, and provide the basis for much of the scripting capabilities of the platform. Additionally, the PowerShell shell supports all of your favorite commands from the old DOS command prompt, so you can start using the PowerShell command line right away.

While PowerShell is a Windows technology, SQL Server 2008 introduced the new mini-shell, SQLPS. SQLPS is a collection of extensions for PowerShell that are designed to work specifically with SQL Server. In addition to those extensions, the SQL Server Agent in SQL Server 2008 has the ability to call the PowerShell subsystem to execute script steps, much the way it can with ActiveX and command line scripts.

There's a lot to learn about PowerShell and SQLPS, and it's all a bit much for this column. Before we can move into the more advanced material, you're going to need to get PowerShell up and running in your environment (because learning a scripting language is really hard if you don't have the right script engine in the first place). Unfortunately, since the introduction of PowerShell 1.0 a few years ago, its gotten a little confusing which operating systems ship with PowerShell, and which version of PowerShell they come with if any at all. So here are the basics:

If you have Windows 7 and/or Windows Server 2008 R2, you have PowerShell 2.0 already.

If you have any earlier version of Windows, you have to install PowerShell.

However, if you have Vista, you may already have PowerShell 1.0, as it was distributed as a Windows Update.

One quick test to find out if you have PowerShell installed is to open a standard command line and type:

C:\>powershell

If it tells you that “powershell” is an unrecognized command or application, then you need to install PowerShell. Otherwise, you should get a new prompt in the window shown in Fig. 1.

Figure 1. Running the basic PowerShell command brings up this information, telling you that you've got PowerShell installed. (Click image to view larger version.)

If you use the “Run…” box (or the all purpose search box in the Start menu on Vista and later), you should see Fig 2.

Figure 2. Your SQL administration is going to get easier, now that you see this box. (Click image to view larger version.)

If you need to install, click here to get to the PowerShell 1.0 downloadables.

PowerShell 2.0 is only currently available on Windows 7 and Windows Server 2008 R2. Microsoft is working on back porting it to the earlier platforms, but those are only available as a CTP as of this writing.

Click here for a blog post outlining what's available.

Click here for the direct link to the download.

Notice that this last bit is part of the new Windows Management Framework, which will be the “new” management software to install on Windows Vista and Windows Server 2008 to bring the functionality current with Windows 7 and Server 2008 R2.

Finally, if you're ready to go ahead and get SQLPS running, you should already have it if you've installed the SQL Server 2008 client tools on your machine. However, if you would like to use the SQLPS extensions on a machine without installing the SQL tools, you can go get it from the SQL Server 2008 Feature Pack. Scroll down and you'll see the downloads for both the SQLPS extensions as well as the SQL Server Management Objects (SMO), which is required to make the whole thing work.

Next time, we'll start using these bits and pieces. Until then, you can get started getting familiar with what PowerShell can do, by simply typing:

PS C:\>get-commands

Have fun!