Powershell Function: Invoke-SQLQuery and Ping-Computer

In this posting, I am going to (briefly) discuss Invoke-SQLQuery, which will run a SQL Server query against a computer or a group of computers and then return the results as a Powershell object. This object can be fed to another element in a pipeline, where it can be sliced, diced and stored using Powershell's intrinsic capabilities.

About a year ago, I was looking for a way to return a result set to Powershell. It seemed that I was forever logging into a SQL Server instance, running one query, then logging out and repeating the whole thing on a different instance.

One day, I read a posting on Powershell and SMO by Muthusamy Anantha Kumar. I noticed some ado.net code towards the bottom of the page and I same the proverbial light: Powershell is to .Net objects what VBScript is to COM objects. Armed with that, I started working on a function that ultimately evolved into Invoke-SQLQuery.

I'd like to point out the following facets of Invoke-SQLQuery:

• This function directly accepts computer names from pipeline input. This makes it trivial to run a query on a large number of SQL Servers.
  
• If you prefer, you can use the -computer parameter to specify one computer at a time. Often, this syntax is more terse than specifying a whole pipeline.
  
• The command provided with -commandText is executed on each computer, in turn.
  
• The output from each execution is aggregated into a single "mega result set".
  
• It is usually important to be able to determine which rows came from which computer because there is only one result set. As a matter of habit, I stick a @@SERVERNAME at the start of the SELECT clause of my query.
• The default query provides a quick overview of the server and includes version information, memory information, instance start time, etc. I use this as a connectivity test or to remind myself of what software is installed on a particular computer.
• The query that is sent to the server can be quite complex. The query can be multiline, but not multibatch. The query can create and use temporary tables. If you can run your query in SSMS, as long as you are not using the "GO" keyword, you should be able to run your query with Invoke-SQLQuery.
  
• The function can read a query from a file passed in with -commandFile. I have added this functionality only in the last few weeks.
  
• Invoke-SQLQuery uses Ping-Computer to ensure that the remote computer is at least booted before trying to connect to a SQL Server instance. This cuts down on the time it takes to fail when a remote computer is down or unreachable for whatever reason. Ping-Computer is a very close cousin of Brandon Shell's Ping-Server, having only few simple changes to match my scripting style. I'm presenting it here for your conveniance, but you should definitely check out Bradon's blog. I have learned much from it.
  

Some drawbacks of Invoke-SQLQuery:

• Invoke-SQLQuery runs the given query on each computer, serially, one execution at a time. Clearly this will not scale to hundreds of servers, but I've been happily running this at a site with about 20 servers dozens of times a day for about a year now.
  
• This does not support multiple returned result sets. In other words, you can't issue two selects in your query and get two different result sets in a collection of some sort. Again, this has not hindered me.
  
• UPDATE, INSERT and DELETE are not supported. I have other scripts that can handle other sorts of DML.
  
• When loading queries from a file, you will get runtime errors if the file contains the "GO" keyword. I have not built in an fail-safe/sanity check for this yet.
  

Here are a few examples of how I use this function. Please watch out for word wrap.

To call a SQL Server system stored procedure on a remote computer and show only some of the returned columns:

Invoke-SQLQuery -computer "server_a" -commandtext "sp_helpdb" | ft -auto Name,Status

Run pretty much any query that you'd like on a single computer:

$cmdText = "select @@servername 'Computer', count(*) 'ConnectionCount' from master.dbo.sysprocesses"

$result = Invoke-SQLQuery -computer:"server_a" -commandtext:$cmdText

$result | ft -auto

Run the same query, but this time run it against two computers. The names of the computers are placed in an array. That array is then fed to a pipeline. It would be trivial to add additional computers to the array, which will cause the command to be run on those computers as well. It is also easy to load an array of computer names from a simple text file.

$computers = ("server_a", "server_b")

$result = $computers | Invoke-SQLQuery -commandtext:$cmdText

$result | ft -auto Computer,ConnectionCount

The output of that last command might look like this:

Computer ConnectionCount
-------- ---------------
SERVER_A 35
SERVER_B 82

Note that the contents of $result are a real Powershell object. That means that you can go and run something like this:

$result | sort ConnectionCount -desc | ft -auto Computer,ConnectionCount

Computer ConnectionCount
-------- ---------------
SERVER_B 82
SERVER_A 35


You can add additional pipeline items as you wish. For example, instead of (or in addition to) applying a sort, you could apply a where clause. This would be silly in my two-server example, but imagine that you have a hundred servers and you would like to know the top five servers, by number of connections. After running the queries and placeing the results in $result, you could run something like this:

$result | sort ConnectionCount -desc | select -first 5

You could stick your results in an Excel file:

$result | sort ConnectionCount -desc | select -first 5 | Out-Excel

You are only limited by your imagination.