PowerShell and SQL - SQLPSX

2019 update: SQLPSX was great seven years ago but nobody is actively working on it. If you are interested in using PowerShell with SQL, check out http://dbatools.io this is an active project on GitHub that just released version 1.0

This is part two in my PowerShell and SQL series. If you missed part one, you can use the PowerShell link under labels on the right to get to it. Last week I went over PowerShell Basics, what it is and how to use it. I included some examples to show you how you can use PowerShell to get SQL related event logs and store them in a file. This week I am going to introduce the SQL Server PowerShell Extensions.

You can download the modules for SQLPSX from the codeplex website: http://sqlpsx.codeplex.com/

SQLPSX is a library of modules that can be used to directly talk to SQL server. You can get server and database information, read from and write to a SQL database and even perform most tasks a DBA performs on a regular basis. Now you can do this without using SQLPSX by creating your own connections to a SQL server but I am going to show you why you shouldn't bother reinventing the wheel.

Installation

Once you download the file and run it, the SQLPSX modules will be added to your modules sub-directory of your Windows PowerShell folder (usually located under My Documents). In order to use the modules you have to import them into PowerShell.

NOTE: loaded modules are session based, this means that when you close the PowerShell window and get back in, the modules will not be loaded anymore. You can have them auto-load when PowerShell opens by adding an entry to your PowerShell profile. If you are interested here is an article at How-to-Geek: http://www.howtogeek.com/50236/customizing-your-powershell-profile/

You load the module into powershell using the import-module cmdlet, but wait, do you know what the names of the modules are? After you have installed SQLPSX open up powershell and type this command:

Get-Module -ListAvailable

You will get a result that looks like this:

There are thirteen modules in the SQLPSX installation, you can see the listing on the SQLPSX website under documentation.

Now lets import one of the modules into our PowerShell Session. To do this we use the Import-Module cmdlet followed by the name of the module we want to import.

Import-Module SQLMaint

Next let's see what functions are available in this module. We can do this using the Get-Command cmdlet and passing it the -Module parameter with the name of the module we just imported.

Get-Command -Module SQLMaint

You should now get a result that looks like this:

The best thing to do next is to pick a function that you like and see what it does and what parameters it takes. Let's look at the Get-SqlData function using our buddy the Get-Help cmdlet

Get-Help Get-SqlData

This returns the name of the function, a synopsis, syntax and a description. Notice the last section, Remarks:

Look at that! You can get a more detailed description and even examples. This is pretty cool, right!

Play around by importing the other modules and checking out the functions. I like to use the SQLMaint and  Agent Modules but check out the other ones to see what is available. If you find something cool, post it in the comments!

Don't Reinvent The Wheel

Like I said before you can connect to SQL just fine without using SQLPSX and do everything you can with it but you will end up writing more code. Let's take a look at two examples. The first example will connect to my server and return a result set from a query:

Now lets do the same thing using SQLPSX:

Fourteen lines of code vs five lines of code. Pretty easy decision in my head. I have enough to do during the day without having to remember all that code just to run a query. If you used method one you would probably end up writing your own module and importing it in all your scripts but why bother when the resource is already available. I want to point out that SQLSPX isn't doing anything different, the functions that they give you use the same code that you can hand write, they have just already done all the leg work for you. If you want to see what is going on "behind the scenes" you can open up the SQLServer.psm1 file in your PowerShell modules directory in the SQLServer folder.

Links and References

Download SQLPSX from Codeplex
Learn More About SMO and PowerShell

Sum Up

That's it for this week. This lesson is important because I am going to use the SQLPSX modules in all of my later examples. Download it and play around and check in next week and we will get started on putting this all together to help simplify your life as a DBA

Hasta Luego,
RutherfordTX

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.

Cmdlets

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'

Pipes

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'

Scripts

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:

http://www.microsoft.com/en-us/download/details.aspx?id=34595

Functions

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'

}

get-MyEventlog("MyServer")

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'

}

get-MyEventlog("MyServer")

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. 

Modules

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: 

http://msdn.microsoft.com/en-us/library/windows/desktop/dd901839%28v=vs.85%29.aspx

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:
  http://www.powershellpro.com/powershell-tutorial-introduction/powershell-tutorial-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"
  '$myvar'
  "$myvar"
  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,

RutherfordTX