Friday, September 21, 2012

PowerShell and SQL - Intro

Over the last couple of months I have been learning PowerShell. I picked up the language pretty quickly because of my past experience with Linux and Perl but it isn't a very complicated language and anyone should be able to learn it. In addition, starting in 2009 Microsoft has added PowerShell to it's CEC (common engineering criteria). The CEC is a set of engineering requirements that all Microsoft Server products are required to comply with. This means that all future versions of Microsoft Server Products will use PowerShell as the standard for automation. Any admin task you can do in the GUI (or in T-SQL) will be able to be done (and automated) using PowerShell. This makes the language very powerful for a DBA and I can foresee a day when it becomes a required skill on a DBA job description.

In this first post in my PowerShell series I am going to go over some PowerShell basics and in the next posts I will get into how to use it with SQL Server.

What is PowerShell?

PowerShell is a task automation framework. This means it is a way to perform tasks on a server (add a login, add a database, etc) and more importantly it is a way to automate them. PowerShell is both a command line and a scripting language. You can use the PowerShell CLI to perform tasks one at a time or you can write a script that will perform multiple tasks sequentially. You can even use Task Scheduler or and Agent job to kick off a PowerShell Script at a set time. Pretty Cool, Right?

Microsoft has learned from it's past mistakes (vbscript) so PowerShell is also secure. It uses a concept called Execution Policy to determine what scripts can run. At the strictest setting PowerShell will not run any scripts, only commands entered at the CLI. The next level down requires that all scripts be digitally signed either using a third-party certificate or a self-signed certificate. Here are the four Execution Policies

  • Restricted - No scripts can run. Only commands from the CLI
  • All Signed - All scripts must be digitally signed
  • RemoteSigned - Local unsigned scripts can run but any remote scripts must be digitally signed
  • Unrestricted - All scripts can be run. No digital signatures required

Working with PowerShell

PowerShell is installed by default on Windows 7 and later but it isn't in your Start Menu. To find it, use the 'Search programs and files' bar in the Start Menu, just search for powershell.exe for the CLI and powershell_ise.exe for the integrated scripting environment.


The basic commands used when working in PowerShell are called cmdlets. Cmdlets take the form [verb]-[noun]. Examples:
  • Get-Help - can be used with any other cmdlet to get information (like -help in cmd)
  • Get-Command - Returns a list of all cmdlets, functions and aliases available
  • Out-File - can be used to reroute output from the screen to a file
  • Get-SQLData - takes a server, db, and a query as parameters and returns the result
Most cmdlets take one or more parameters. You can use Get-Help to show you what parameters are available and which ones are required. Here is how I would check the Application Event Logs on my local machine and how I would check it on a remote machine (make sure you have the necessary permissions on the remote machine)
  • Get-Eventlog -logname 'appliation'
  • Get-Eventlog -logname 'application' -computername 'MyServer'


You can string cmdlets together using the | operator (it's called a pipe) to take the output from one cmdlet and feed it to the next. This allows you to string cmdlets together to do what you want to do. Here we are taking the Get-EventLog cmdlet and writing the output to a file
  • Get-EventLog -logname 'application' | Out-File 'C:\temp\applog.txt'


After working with cmdlets for a while you will find the need to combine a series of cmdlets together. The is where a script comes in. PowerShell scripts use the .ps1 extension. You can create it in your favorite text editor or use the built in Microsoft Scripting Environment, PowerShell ISE. The ISE in PowerShell v3 includes intellisense so I would definitely suggest upgrading if you haven't already. You can get it here:


Once you have written a few scripts you will notice that some scripts have the same blocks of code. Instead of rewriting it every time you can create a function. A function is like a mini-CmdLet, you can create it to take input parameters and specify default ones if they aren't given. Let's take our previous example and turn it into a function that takes the computer name as a parameter.
function get-MyEventlog($computername) {
    Get-EventLog -logname 'application' -computername "$computername" | Out-File 'C:\temp\applog.txt'


To create a function you first define it with the word 'function' then you give it a name and then specify input parameters if needed. Notice the entire function is enclosed in curly brackets, everything between the curly brackets is part of the function. Then once the function is written we can call it to get the Event Logs written to file. Now imagine that we fancied up the function so it just pulled Event Logs where the source was MSSQLSERVER.

function get-MyEventlog($computername) {
    Get-EventLog -logname 'application' -computername "$computername" | 
             where-object { $_.source = 'MSSQLSERVER' } | 
             Out-File 'C:\temp\applog.txt'


Now you can start to see some of the power of PowerShell. You could have it iterate through a series of computer names, get the SQL Event Logs and write them to a file. You could schedule the script to run nightly and dump the file to your desktop so that when you log in in the morning you can check the logs for all your servers from the previous day. You could even have the script write to a database and use SSRS to deliver a daily error report to your entire DBA team. 


The next level after functions is modules. You can create a module of various useful functions and make it available to multiple scripts. Module creation is a bit outside the scope of this PowerShell intro but if you are interested here is a link to a MSDN article: 

Some Common Gotchas

Here is a quick list of some common gotchas I found when I started working with PowerShell
  • Assignment vs Comparison Operators - In PowerShell '=' is an assignment operator, you do not use it to compare to values. '$var = 1' sets the value of the variable $var to 1, it does not check to see if $var is equal to one. To compare values you use -eq for equal so: '$var -eq 1' will check to see if the value contained in the variable $var is equal to one. Here is a link to an article that discusses the comparison operators and goes into some conditional logic:
  • Double vs Single Quotes - In PowerShell variable interpolation (or variable substitution) only happens if the variable is enclosed in double quotes. If the variable is enclosed in single quotes Powershell treats it as a string literal. For example look at this code:
    $myvar = "RutherfordTX"

    The first call (the one in single quotes) will return the string $myvar while the second one (double quotes) will return the value RutherfordTX

Links and References

PowerShell v3 download
Module Creation
Comparison Operators and Conditional Logic

Sum Up

Wow, that was a lot more than I thought I was going to write. I have quite a few ideas for the next couple of posts so expect me to get pretty regular for a while. Also I will be presenting at our local SQL User group, CACTUSS, here in Austin on October 15th and 16th on this topic so if you are in Austin drop on by.

Hasta Luego,

No comments:

Post a Comment