Installing PowerShell

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

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:


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.

Running the PowerShell shell
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.

SQL admin via PowerShell
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!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Microsoft Endpoint Manager Improvements Highlighted at Ignite

    Improvements in the Microsoft Endpoint Manager (MEM) management solution were part of Tuesday's Microsoft Ignite online event.

  • Green City Illustration

    Microsoft Ignite 2020 Reaction, Part 1: A New Normal for Tech Conferences

    Something about Satya Nadella's opening keynote makes Brien wonder if Microsoft thinks we'd all be better off doing everything -- including conferences like Ignite -- remotely, even after the pandemic is over.

  • Microsoft Ignite: Azure Advances Across Five Frontiers

    To kick off the Microsoft Ignite virtual conference, CEO Satya Nadella made a bold claim about the public cloud with the second-largest market share behind Amazon.

  • Microsoft Buying Games Maker ZeniMax Media for $7.5 Billion

    Microsoft is buying ZeniMax Media, parent company of Bethesda Softworks and other game-maker affiliates, for $7.5 billion in cash.

comments powered by Disqus