PowerShell for SQL Server Essentials: Manage and monitor SQL Server administration and application deployment with PowerShell

Before PowerShell, systems and network administrators managing Microsoft software stacks had to resort to using different tools, languages, and technologies to enable automation and integration tasks. For some tasks, administrators used batch files that could be run using Command Prompt (or DOS Shell, for those of you who still remember this term). For other tasks, maybe Visual Basic Scripting Edition (VBScript) was used. Yet, for additional tasks, maybe Windows Scripting Host (WSH) was used. The list goes on.

In a lot of ways, administrators had to be creative because there was not one single language and tool they could use to bridge different Microsoft (and non-Microsoft) tasks together. Unix and Linux administrators, on the other hand, always had C-shell and trusty bash to rely on. At that time, Microsoft just did not have that powerful a command-line tool.

Enter PowerShell. PowerShell was born out of this need for integration and automation. Jeffrey Snover, the inventor of PowerShell, initially incubated PowerShell under the project named Monad. He originally described Monad as the next generation platform for automation.

More than 10 years after this manifesto was written, PowerShell has already improved and matured in leaps and bounds and has indeed become the platform for automation and integration for Microsoft products (and even non-Microsoft packages).

As of today, many Microsoft products have adopted PowerShell and delivered numerous cmdlets (we will talk about them later) with their respective product installations. Windows Server, Active Directory, Exchange, SharePoint, SQL Server are products that support PowerShell (to different extents), but the support has widened through the years.

There are two environments that come with PowerShell when you install it: the PowerShell console and the PowerShell Integrated Scripting Environment (ISE). These environments have improved a lot since the first version and should be more than sufficient for you to start working with PowerShell. If you prefer a different environment, there are other PowerShell editors out there. Some editors are free and some commercial. Some vendors that provide PowerShell editors are Idera (PowerShell Plus), Dell (PowerGUI), and SAPIEN Technologies (PowerShell Studio 2014).

In a 64-bit system, PowerShell will come in two flavors: 32 bit and 64 bit. The 32-bit version has the label suffix (x86). Note that 64-bit add-ons and snap-ins for PowerShell will only load in the 64-bit console or ISE. The following screenshot shows the result of searching PowerShell in Windows:

The PowerShell console

The PowerShell console is very similar to the Command Prompt. By default, the interface is blue, compared to the usual black of the Command Prompt:

The PowerShell console is great for administrators and IT professionals who prefer to work on a purely command-line environment. The console is also great for running predefined scripts either manually or through a job via the Windows task scheduler or SQL Server Agent.

The PowerShell ISE

A standard installation of PowerShell also comes with an Integrated Scripting Environment (ISE). The PowerShell ISE is a more Graphical User Interface (GUI) way of working with PowerShell and comes with a few handy features, including IntelliSense and syntax help, as shown in the following screenshot:

Some of the compelling features that the ISE has are listed as follows:

• The script editor and PowerShell console in a single environment

• The autocomplete and on-hover usage/syntax guide

• A command pane that allows you to visually fill in parameters and transfer the syntax over to your editor

• Multiple tabs that allows the opening of multiple scripts at the same time

• A zoom slider, which is great for presentations or just basic readability

We will use the PowerShell ISE for most examples in this book.

Running PowerShell as an administrator

Most of the time, you will use PowerShell to perform administrative tasks, so you will need to run it as an administrator. You can do this by right-clicking on the application (console or ISE) and clicking on Run as administrator.

You will know you've successfully run the application as the administrator by looking at the title bar. It should show Administrator: Windows PowerShell:

If you do not run your PowerShell environment as the administrator, you might not have sufficient permission to run some of your commands or scripts. You will most likely get Access Denied errors.

A useful trick to identify whether you are running the shell as the administrator is to change the appearance of the shell based on the elevation status of the session. This can be accomplished by adding a snippet of code to your profile that checks whether the session is run by an administrator and then changing some properties accordingly.

First you need to check whether your profile exists. You can check the path to your profile by typing the following command:

$profile

If this file doesn't exist, you can simply create it by typing the following:

New-Item -ItemType File $profile -Force

The $profile command is equivalent to $profile.CurrentUserCurrentHost, which means the settings you provided will only work on the current host. Note that your console and ISE will each have its own profile, so you may need to create one for each. The values you can specify with the profile are AllUUsersAllHosts, AllUsersCurrentHost, CurrentUserAllHosts, and CurrentUserCurrentHost.

Here is a simple snippet you can add to your profile that changes the background and foreground color of your shell if you running the shell as an administrator:

if ($host.UI.RawUI.WindowTitle -match "Administrator")
{
   $host.UI.RawUI.BackgroundColor = "DarkRed"
   $host.UI.RawUI.ForegroundColor = "White"
}

The execution policy

At the risk of sounding like a dictionary, I will define execution policy as the policy applied to determine whether a script can be executed by PowerShell. Execution policies do not make the scripts more secure. They simply lay the ground rules before a script is executed.

The available execution policies are provided in the following table:

Policy	Runs a command?	Runs a local script?	Runs a remote script?
Restricted	Yes	No	No
AllSigned	Yes	Must be signed	Must be signed
RemoteSigned	Yes	Yes	Must be signed
Unrestricted	Yes	Yes	Yes—prompts before running downloaded scripts
Bypass	Yes	Yes	Yes—no warnings or prompts

The default execution policy depends on the operating system you are using. For Windows 8, Windows Server 2012, and Windows 8.1, the default policy is Restricted. For Windows Server 2012 R2, it is RemoteSigned.

Should you need to sign your scripts, you can refer to Scott Hanselman's blog post available at http://www.hanselman.com/blog/SigningPowerShellScripts.aspx. Although this was written a few years ago, the content is still relevant. Patrick Fegan from Risual also has a good, more recent tutorial on self-signing PowerShell scripts at http://consulting.risualblogs.com/blog/2013/09/20/signing-powershell-scripts/.

Note

To get more information about execution policies, including risks and suggestions on how to manage them, you can type Get-Help about_Execution_Policies in the command-line window, or you can visit the TechNet page at http://technet.microsoft.com/en-us/library/hh847748.aspx for more detailed descriptions.

If you want to check which execution policy you are running on, you can use the following command:

Get-ExecutionPolicy

If you want to change it, use the following command:

Set-ExecutionPolicy

The following is a screenshot of what you can expect when you run these two cmdlets:

It would be good to read more on execution policies, evaluate the risks that come with the different settings, and evaluate your needs before deciding which setting you should use.

PowerShell has matured since its inception and has undergone several version upgrades. At the time of writing of this book, the most recent version is PowerShell V4.

The following table shows the different PowerShell versions that Microsoft released, operating systems that support them, required .NET Framework version, and some of the notable features:

PowerShell matures with every release and the requirements and features will change with different operating systems.

To determine which PowerShell version you are using, you can type in $PSVersionTable in your console or ISE:

If you have PowerShell v3 or v4, you can also downgrade your PowerShell session. You can do this by supplying the -Version parameter when you start your session:

Powershell.exe -Version 2

At the heart of PowerShell is a cmdlet (pronounced as commandlet). A cmdlet is described in MSDN (available at http://msdn.microsoft.com/en-us/library/ms714395(v=vs.85).aspx) as:

In other words, cmdlets get the job done in PowerShell. You can think of cmdlets as small commands—very specific commands—which you can use to accomplish your task.

To explore the cmdlets available in your PowerShell version, you can use the Get-Command cmdlet. You can filter the results as well. For example, if you want to look for log-related cmdlets, you can use the following command:

Get-Command –Name "*Log*"

Cmdlet parameters

Note that cmdlets can accept parameters or switches. This makes cmdlets quite flexible. You can supply parameters to cmdlets by specifying a dash followed by a parameter name, space, and the parameter value:

Cmdlet -ParameterName ParameterValue -ParameterName ParameterValue

It will be easier to understand how parameters work if we go through an example. Let's take a look at the usage syntax for Get-Service:

Each block in the help section, shown in the preceding screenshot, represents a parameter set. Each parameter set specifies different combinations of parameters and switches that are all valid when you use Get-Service.

Note

Anything in square brackets is optional; anything between angle brackets is mandatory.

Let's consider the following first line of command:

    Get-Service [[-Name] <String[]>] [-ComputerName <String[]>]

The [[-Name] <String[]>]part means that you can specify -Name, which should be your parameter name:

Get-Service –Name *SQL*

Since [[-Name] <String[]>] is surrounded by square brackets, it means it's optional. This parameter name can be left out and you can provide just the value. This makes it positional, meaning the value you provide will map to the parameter defined for that cmdlet at that position. In the following example, the first value will be mapped to the first parameter for Get-Service:

Get-Service *SQL*

The next part [-ComputerName <String[]>] is still overall an optional parameter. However, if you decide to supply the value, you have to specify the parameter name, which is ComputerName. Note that there is no square bracket around ComputerName.

When you specify parameter names, you can also take shortcuts. You can specify just the first few characters of the parameter name, and as long as it's unique, PowerShell will figure out which parameter you are referring to:

Get-Service –Na *SQL*

Note

Although it's quite tempting to use shortcuts, when you are first learning how to use PowerShell, try to always completely spell out the parameter names. This will make your code more readable and easier for the rest of your team to work with your code.

If you have a cmdlet that requires input and you don't provide it, you will be prompted for the values interactively:

Cmdlet aliases

Some of the cmdlets also have aliases by default. This means these cmdlets can be invoked by using a different name than their formal cmdlet name. For example, the following screenshot shows the aliases for Get-ChildItem:

You can also create your own aliases using New-Alias. Aliases can be useful because in some ways, they allow you to use some of the terms you may already be familiar with and leverage them in PowerShell. Aliases also let you personalize PowerShell to your liking. Be careful not to create too many of these though; it may make your PowerShell scripts confusing and even unreadable to others.

Simply put, a PowerShell provider provides a way for PowerShell to access a data store. To get a visual of a provider, think of the file system. The file system is a data store that contains information about files and folders and their properties. We can access the file system via the Command Prompt, PowerShell console, or Windows Explorer. Now try to apply this concept to another data store, for example, SQL Server. Imagine that you can navigate through the objects of SQL Server just the way you navigate your file system.

Learning about providers is important because this allows you to extend what you can do with PowerShell. To list the current providers in your system, use the Get-PSProvider cmdlet:

What you see in the preceding screenshot are the default available providers that come with PowerShell v4 on a Windows Server 2012 R2 Standard server. A lot of the providers are accessed using what is called drives. To list the current drives, you can use Get-PSDrive:

In a file system, if you wanted to change drives, you can use the cd command, which is an alias for Set-Location:

C:\> cd J:\

To navigate to a different provider, you can use the same concept. For example, if you want to navigate the HKLM registry hive (which stands for HKEY_LOCAL_MACHINE), you can use the following command lines:

C:\> cd HKLM:
HKLM:\>

To work with items in PSDrive, Microsoft has provided a number of Item cmdlets that are generic enough to perform the task regardless of which drive you are in. To get a list of these cmdlets, you can type Get-Command *Item*. For example, if you are using a file system, you can use the New-Item cmdlet to create a new folder or file. If you are in the registry, it will create a new registry entry.

The recent releases of Microsoft products come with their own PowerShell providers, which you can readily use. You can also create your own providers if you prefer.

You can extend PowerShell by loading snap-ins and modules. Snap-ins or PSSnapins are dynamic linked library (DLLs) compiled from .NET code, which may contain additional cmdlets and PSProvider. The PSSnapins are old school—they are primarily how you extend version 1, but still supported in version 2, version 3, and version 4. Although considered old school, you can still create snap-ins. Refer to http://msdn.microsoft.com/en-us/library/ms714450(v=vs.85).aspx on how to do this.

The related snap-in cmdlets are as follows:

Instead of snap-ins, the recommended way of extending the PowerShell functionality from version 2 onwards is using modules. Modules are similar to snap-ins when it comes to extending functionality, but unlike snap-ins, modules can also add functions. Modules also support autoloading, which means the module can be loaded as soon as one of its cmdlets/functions/PSDrive are used.

Modules can be script-based or binary-based. A script module uses PowerShell code saved in a .psm1 file. A binary module is more similar to PSSnapin, where it references a .NET DLL.

Modules are the new school way of extending PowerShell, from version 2 onwards. Related cmdlets are listed as follows:

If you want to write PowerShell extensions, Microsoft recommends that you create modules instead of snap-ins.

A pipeline is defined in www.TheFreeDictionary.com as follows:

I think this definition is very fitting to a pipeline in PowerShell. Instead of crude oil or water, what PowerShell transports is pieces of information. PowerShell also has these pumps and valves for flow control—we will see more of these in the later chapters.

The pipe symbol in PowerShell is |, also called a bar. You can pipe multiple cmdlets together. When you pipe these cmdlets, the output of one cmdlet becomes the input of the next cmdlet:

When you are writing your scripts, you may want to add a new line after the pipe and continue typing the next cmdlet on the new line:

Many script authors also prefer to indent the succeeding lines a little bit to emphasize that these are all part of the same block.

Let's get a few syntax basics down. This section is not meant to be an exhaustive tutorial on PowerShell's syntax but should serve as a good, brief introduction.

Let's walk through the following script:

$currdate = (Get-Date -Format "yyyyMMdd hhmmtt")
$servers = @("ROGUE", "CEREBRO")

#save each server's running services into a file
$servers  |
ForEach-Object {

    $computername = $_
    Write-Host "`n`nProcessing $computername"

    $filename = "C:\Temp\$($computername) - $($currdate).csv"

    Get-Service -ComputerName $computername |
    Where-Object -Property Status -EQ "Running" |
    Select-Object Name, DisplayName |
    Export-Csv -Path $filename -NoTypeInformation

}

Even if you are not very familiar with PowerShell yet, you may already be able to tell what the preceding script is trying to accomplish. Simply put, the script iterates over the listed servers and saves the list of running services into a file that acts as a timestamp.

This line creates a variable called $currdate that gets the current system date in the "yyyyMMdd hhmmtt" format:

$currdate = (Get-Date -Format "yyyyMMdd hhmmtt")

The snippet with an at (@) sign, @("ROGUE", "CEREBRO"), creates an array, which is then stored in another variable called $servers:

$servers = @("ROGUE", "CEREBRO")

Since $servers contains multiple values, when you pipe it to the Foreach-Object cmdlet, each value is fed into the script block inside Foreach-Object:

#save each server's running services into a file
$servers  |
ForEach-Object {

}

You are also introduced to a few concepts inside the Foreach-Object block.

To get the current pipeline object, you can use $_. The $_, also referred to as $PSItem, is a special variable. It is part of what PowerShell calls automatic variables. This variable only exists and can only be used in the content of a pipeline. The $_ variable contains the current object in the pipeline, allowing you to perform specific actions on it during the iteration:

    $computername = $_

A backtick is an escape character, for example, to add a newline. It is also a line continuation character:

    Write-Host "`n`nProcessing $computername"

Note that the strings are enclosed in double quotes:

    Write-Host "`n`nProcessing $computername"

Strings in PowerShell can also be enclosed in single quotes. However, if you have variables you want to be evaluated within the string, as in the preceding example, you will have to use double quotes. Single quotes will simply output the variable name verbatim.

PowerShell has a subexpression operator, $(). This allows you to embed another variable or expression inside a string in double quotes, and PowerShell will still extract the variable value or evaluate the expression:

$filename = "C:\Temp\$($computername) - $($currdate).csv"

Here is another example that demonstrates when subexpressions will be useful. The expression to get the date that is 10 days from today is as follows:

(Get-Date).AddDays(10)

If we want to display the value this expression returns, you may be tempted to use:

Write-Host "10 days from now is (Get-Date).AddDays(10)"

However, this simply redisplays the expression; it doesn't evaluate it. One way to get around this without using a subexpression would be to create a new variable and then use it in the double-quoted string:

$currdate = (Get-Date).AddDays(10)
Write-Host "10 days from now is $currdate"

With the subexpression, you don't need to create the new variable:

Write-Host "10 days from now is $((Get-Date).AddDays(10))"

The example we walked through should give you a taste of simple scripting in PowerShell.

The following is a table that outlines some of these common scripting components and operators:

<#
  get the current
  date
#>

$name = "Hello `n world!"

Get-Service `
    -Name *SQL* `
    -ComputerName ROGUE

$dt = Get-Date

$name = 'sqlbelle'

$name = "sqlbelle"

$name = "sqlbelle"
$message = "Hello `n $name"

$name = "sqlbelle"
$message = "Hello " + $name

$dt.AddDays(10)

Write-Host "Date: $($dt.AddDays(10))"

@("ROGUE", "CEREBRO")

$servers = @("ROGUE", "CEREBRO")
$servers[0]

[datetime]$dt

$x = "@
Hello $name.
This is a multiline
string
"@

The table is not a comprehensive list of operators or syntax about PowerShell. As you learn more about PowerShell, you will find a lot of additional components and different variations from what has been presented here.

Once you've written your script, save your script in a file with a .ps1 extension. From the PowerShell console, you can run the script by specifying the full path to the script:

PS C:\> C:\Scripts\Get-RunningServices.ps1

Note that your scripts can also be parameterized so that it can take an incoming value when invoked. If this is the case, you can specify the parameter the same way you specify it in a regular cmdlet:

PS C:\> C:\Scripts\Get-RunningServices.ps1 -ComputerName ROGUE

If you are at the script directory, you don't have to specify the path. You can also use a dot-sourcing operator to run the script. Dot sourcing a script means that any of the variables and functions in the script are loaded into the current scope and available for use in the same console session:

PS C:\Scripts> .\Get-RunningServices.ps1
PS C:\Scripts> .\Get-RunningServices.ps1 -ComputerName ROGUE

Note that depending on your execution policy settings, the script may run or get access denied errors. If this is the case, you may either need to adjust your execution policy or sign your script.

PowerShell used to come bundled with help documentation. If you've worked with *nix systems, it's similar to the man page.

Starting with PowerShell v3, however, the help files/system were not installed with PowerShell. One of the chronic problems with a help system that comes bundled with an application is that the contents get outdated right away. Applications are continuously being patched, improved, and changed, and thus the documentation needs to be updated. You will need to consciously download and install the help files when you are ready.

Once ready, run PowerShell as an administrator and just type in the following command:

Update-Help

This will connect you to a Microsoft server to download the most recent version of PowerShell help:

When you need to look for syntaxes or examples from the help system, you can use Get-Help and then the cmdlet name. For example, if you want to get ChildItem, you can use the following command:

Get-Help Get-ChildItem

Other switches available for Get-Help that you might find useful are as follows:

Sometimes you may prefer to open the local help system in a different window, in which case you can use the following command:

Get-Help Get-ChildItem -ShowWindow

The result is shown in the following screenshot:

Having the help document in a different window allows you to do simultaneous tasks, that is, write your script and refer to the syntaxes and examples. The help window also allows for searching and highlighting keywords.

If what you prefer is to view the help online and get the most recent version to date, you can use the following command instead:

Get-Help Get-ChildItem -Online

This will open the corresponding Microsoft TechNet entry in your default browser:

Get-Command –Name "*Log*"

$message = "Hello World!"
$message | Get-Member

This chapter has provided a very basic introduction to PowerShell, from a brief history to environments, cmdlets, and pipelines. This should be enough to get you familiarized with PowerShell fundamentals, a skill you will need to work with the next chapters. It is also important to remember how you can learn more about PowerShell using cmdlets such as Get-Help, Get-Command, and Get-Member. The more comfortable you are looking for resources on your own, the faster and better it will be for you when it comes to learning PowerShell.

This chapter is not meant to be an exhaustive, one-stop shop for PowerShell. There are a number of excellent PowerShell books out there that dig deeper into PowerShell's technicalities, syntaxes, and advanced features.

In the next chapter, we will look at how PowerShell can be integrated with SQL Server.