The Surly Admin

SQL Queries in PowerShell

There are a whole bunch of blog posts and scripts out there to do SQL queries in PowerShell, so I’ve really hesitated about posting my own version of it.  But hey, it’s my blog and I can do what I want to.  Do what I want to.

Working in a Team vs Working on your own

At athena health we have quite a bit of data in SQL databases, and we very often want to query those databases for information.  In fact, we do it a lot.  One of my first big projects there was all about running SQL queries.  So I found myself going back to that script and copying out my SQL query code and pasting it into another script.  But then I would make a change to the code in the new script and think to myself “Self”–that’s how I address myself–“Self, this would be a great feature for my first script!”  And copy it back over.

Clearly I was in need of a function, and not only a function but one that I called externally so I only ever had to update it once and ALL my scripts would get the new functionality–which does introduce its own dangers but let’s not dwell on the negative.  This, for me, also highlighted the difference of working in a team versus working on your own.  For the longest time I’ve avoided scripts that depended on any kind of outside functions because I worked alone, and often published my scripts on Spiceworks where those outside sources simply aren’t available–so the script had to stand on its own two feet.  But now I work in a team, with a ton of resources and cutting corners to make script development go quicker is now a high priority.  Not to mention keeping things predictable for the next guy to read the code is important.

This was how athena health‘s first PowerShell module was born.  As with any journey, you have to take that first tentative step, and this was ours.  PowerShell is a young beast in our department with only 3 of us knowing it well enough to use every day.  But being able to use SQL queries, and even use them from a CLI is a huge boost and I’ve often used it without even creating a script.

Requirements

The requirements for my new function, which I call Invoke-SQLQuery, are fairly simple.  It has to be easy, fast and reliable.  That means the code is pretty clean, without going into massive scenario’s requiring hundreds of lines of code.  Get what you need, return the information and get out.

Later I did add the ability to “store” a query in a text file.  If you don’t specify a query yourself the script will call up Notepad and this text file and the last query you ran from Notepad will be there.  Imagine putting your top queries in there (commented out) and you could just swap things around with ease and run the query right on your shell.    Then came the SQL 2012 AlwaysOn Availability Groups and I needed a way to test that they were working so I added the ability to support them into the script.  Run a query–failover the database–run the query again.  Bang, proof of concept completed.

The Code

Let’s break down how this code works.

The Param section has a few things going on here.  First I wanted to support ParameterSets, so if you designate that you want to ListDatabases, you can’t use the Query or Database switches too.  Notice that $Instance, $Credential and $MultiSubnetFailer don’t have any decorators saying which ParameterSetName they’re a part of?  By not saying anything I’m basically saying that they are available to ALL parameter sets.  For the other parameters I designate which ParameterSet they’re a part of, which ones are mandatory and even designate that $Query can accept text from the pipeline.

Because it’s pipeline, we really need to use the Begin/Process/End blocks.  My Begin block is pretty basic, if you designate ListDatabases then I’ll provide the query and database for the script later.  And if $Query is left blank then launch Notepad with the text file (notice it’s saved in your temp folder) so you can designate something in there.

I skipped the Process block entirely on this script.  Why?  Pipeline feeds data to a script one object at a time, to be processed one at a time in the Process block, but in this case I don’t want that.  Get-Content would feed me the query one line at a time, which would be an epic fail as I’d be trying to execute partial queries.  Instead, I’ll gather ALL the input from the pipeline (using the built-in $Input variable) and then process the query as a whole.

Let the END begin!  First, I check if there was any data from the pipeline, which is stored in its entirety in the built-in $Input variable.  If there is, simply transfer it to the $Query variable.  Next, if there are credentials I need to break those down into plain text for the query.  And then add MultiSubnetFailover=yes to the connection string if we’re talking to an AlwaysOn Availability Group.  I also write a little warning in there, just in case someone tries to do this and it fails on them–good chance it will if you don’t have the latest SQL 2012 client installed.

Since the script supports multiple SQL servers with the same query (how cool is that?) I setup a loop, build the proper connection string–let me break out of my narrative a little here.  Right now the script only supports port 1433 for SQL, and supporting alternative ports would be easy enough to do here–just another string replacement, right?!–but I can’t recall ever seeing a SQL database with an alternative port.  Is this functionality you’d like to see?–and setup the objects needed to query the SQL database.

The last bit actually executes the query.  It will report any errors it receives as a warning.  I’ve debated having it throw an error at this point, which would require you to wrap calling the function in your own Try/Catch so you could capture the problem.  At this point I have not done that because the scripts that are using this function aren’t doing that.  Again, something that should be added in a future version?

Notice the Add-Member line there?  I’m actually adding a field to the query that designates the server/instance in the returned data.  This is so you can see what server returned what information.  You may end up filtering that out later in your script, or you may potentially use it.  One of the interesting bits of PowerShell coding is when you write tools for others to use you have to try to anticipate how they may use it.  I lean towards the more information is better camp.

Making it a Module

Eventually I’ll be renaming this module from Invoke-SQLQuery to something like SQL.Automation and putting other functions in it.  But for now Invoke-SQLQuery will do.  I saved the file on our central script folder and saved it as Invoke-SQLQuery.psm1.  Now I can use Import-Module \\server\share\functions\Invoke-SQLQuery to load it up and use it in my scripts.

If you’d like to use this function, you can find the full source code on Spiceworks:

Invoke-SQLQuery

December 1, 2014 - Posted by Martin9700 | PowerShell | Modules, queries, sql