Welcome to Day 1 of my “A Month of PowerShell” series. PowerShell is one of those things that I know I need to learn more about in this profession. Recently, I was reading a SQLSkills Insider email where Paul Randal mentioned that he wants to learn PowerShell also – but he likes to learn in small doses, like what a blog series provides. However, he wasn’t able to find a blog series on PowerShell. So I said to myself: “Self, what better way is there to learn a subject than to write about it?” So, here is my blog series on PowerShell. I hope that you will find this useful, and I encourage comments/questions each day.

One of the ways that I learn is by doing, and I encourage you to do the same. With this, throughout this series I will be pointing you to other resources or the PowerShell help for a more in-depth look into areas. I encourage (and frankly, I am expecting) you to dig further into those resources.  Yes, I could copy/paste into this blog, but I feel that you need to learn how to find out this information yourself.

In this blog series, I’m using PowerShell 3 running on a 64-bit Windows 7 Ultimate laptop. (You can download PowerShell 3 from http://www.microsoft.com/en-us/download/details.aspx?id=34595.) On my system, I have four SQL Server instances, named SQL2005, SQL2008, SQL2008R2 and SQL2012. When we start working with SMO, I’m utilizing the sqlps module from SQL Server 2012 for this series.

This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Why PowerShell?

Microsoft is building PowerShell into all of its products, and has said that in the future, administration will need to be performed by PowerShell. As we start seeing Server Core installations, this becomes even easier to imagine – how do you interact with a server that doesn’t have a GUI? You need a powerful program that can run from your computer that can connect to remote computers, and Microsoft says that the program is PowerShell. It is inevitable that you will eventually need to become proficient at PowerShell.

PowerShell is Microsoft’s “new” command prompt/scripting environment. Since we’ve been hearing about PowerShell for a while now, “new” is obviously relative – it’s newer than our old familiar friend the DOS/CMD prompt, or even VBScript. But since many of us haven’t started using it yet, it’s new to us. So, let’s start off with a quick high-level look at the differences:

PowerShell is designed as .NET object-based scripting environment. Everything in PowerShell is an object, even the output. DOS is 100% text.  VBScript can use some objects (it’s not .NET based). Using objects provides several benefits: Objects have a defined type, and they can have properties (settings, which might be other objects) and methods (a function that performs a task on the object type).

In the older scripting environments, if you want to use data from one command into subsequent commands then you need to grab the output, manipulate it, and then send it to the next command. With PowerShell, the object output of one command can be used as the input into another command.

PowerShell Commands

Which brings us to the next item – PowerShell commands (referred to as Cmdlets) are customizable. First of all, you can create your own cmdlets. Secondly, you can assign an alias to any cmdlets. In PowerShell 3, there are hundreds of cmdlets. Various programs, such as SQL Server, will install additional cmdlets. PowerShell comes with many aliases already for cmdlets – traditional commands that you may already be accustomed to, such as DIR, CD, DEL, ls, man, etc. (Did you notice that there are aliases for not only the DOS commands that you are used to, but also UNIX commands?) Cmdlets have a unique naming convention: Verb-Noun. For instance, some of the available cmdlets are Get-Process, Get-Help, Get-ChildItem. To return a list of all cmdlets, just enter Get-Command. To get a list of all aliases, just enter… wait for it… (Can you guess what it is?)… Get-Alias.

Discoverability

PowerShell has built into it cmdlets to let you discover what all you can do within PowerShell. The main discoverability cmdlets are:

Getting Started

It’s time to start playing with PowerShell. You can launch PowerShell by going to the Start Menu | All Programs | Accessories | Windows PowerShell and launching Windows PowerShell or Windows PowerShell ISE (I prefer to use Windows PowerShell ISE). PowerShell will open up and present you with its command prompt. (Some versions of the Windows OS now have a PowerShell shortcut in the Quick Launch toolbar / taskbar.)

To see all of the processes running on your computer, run the cmdlet Get-Process. A result set is returned to the screen of all running processes.

Pipelining and Comparison Operators

Okay, that’s neat. But I’m only interested in the sql processes… the processes named sqlservr. We get those by sending the output of Get-Process to another cmdlet – Where-Object. This is performed by entering: Get-Process | Where-Object ProcessName –EQ ‘sqlservr’. And there you go… a list of just the sqlservr processes. The pipe sends the output (remember that the output is an object) from the cmdlet running on the left side to the cmdlet on the right side.

In addition to Pipelining, this command also introduces the Comparison Operators. The complete set of Comparison Operators can be seen with Get-Help about_comparison_operators.

Notice the “-“ that is in front of the operator – in PowerShell, all arguments need the preceding dash.

Drives

If you have a Unix background, you know that everything in Unix is treated as a drive. PowerShell Drives provides the same functionality. You can use Get-PSDrive to see the list of available drives in PowerShell. If you run this, you will see all of the physical drives, drives to the HKCU and HKLM registry hives, a drive to the Windows environmental variables and others.

Profiles

You can customize your PowerShell environment by establishing a profile. There are actually multiple profile files, one each for:

If using the PowerShell ISE, the path for Current User, Current Host is $Home[My ]DocumentsWindowsPowerShellMicrosoft.PowerShellISE_Profile.ps1

PowerShell creates a $Profile variable automatically, and it has properties for each of the above. See Get-Help about_profiles for more information about profiles.

I have my PowerShell environments customized based on the $Profile.CurrentUserAllHosts file. This file contains:

Source code

$global:CurrentUser = [System.Security.Principal.WindowsIdentity]::GetCurrent()
 # setup UI
."$HomeDocumentsWindowsPowerShellSetup-UIEnvironment.ps1"
 Import-Module “sqlps” -DisableNameChecking

The file Setup-UIEnvironment.ps1 customizes my actual PowerShell window, and it consists of the following:

Source code

function prompt
{ $wintitle = $CurrentUser.Name + " " + $Host.Name + " " + $Host.Version
$host.ui.rawui.WindowTitle = $wintitle
Write-Host ("PS " + $(get-location) +">") -nonewline -foregroundcolor Yellow return " " }

Today we covered some PowerShell basics – object-oriented, cmdlets, aliases, pipelining and comparison operators, drives and profiles. Come back tomorrow when we talk about variables and operators.