PowerShell Studio Review

Over the years I’ve noticed certain characteristics in the IT professionals I respect the most. These range from the rather obvious intellectual curiosity that goes beyond all matters IT to the more subtle religious devotion to particular code formatting styles. The germane attribute to this post is that they also recognize and adore good tools. They sense when their current toolset is deficient and spend time looking for better ones. When a good one is found they become passionate about them and share them with other people they respect.

When I first started out in PowerShell the ISE was quickly determined to be less than ideal. I rarely spend time in the shell itself, but I write quite a lot of scripts, functions, and modules. A couple of years ago there wasn’t much in the way of alternatives. I used PowerGUI for a while, which wasn’t (and isn’t) bad. It provided Intellisense and a heap explorer (for lack of a better term; it’s a variable list), but at the time there weren’t all that many cmdlets to keep track of anyway. When I began exploring multithreading with PowerShell I ran into the big problem that the console PowerGUI wraps executes in an STA thread. That’s death for a PowerShell ISE for me now. The next tool I tried was Idera’s PowerShell Plus. It was a step ahead of PowerGUI, but was a bit ‘clunky’. Idera makes some great tools and provide awesome support for the PowerShell community (they run powershell.com). Also, their former VP of Marketing gave a copy of PowerShell Plus to every attendee in one of my SQL Saturday sessions. I probably would have stuck with it if it wasn’t for discovering Sapien’s PrimalScript.

PrimalScript is, so far as I can tell, the gold standard of scripting tools. It’s got more bells and whistles than I could ever explore. That was really my only problem with the tool. When it comes to scripting I’m a PowerShell only kind of guy. Due to PrimalScript’s long history PowerShell really became a ‘tacked on’ addition to the tool. It works remarkably well, but there is an awful lot of clutter if all you do is PoSH. A while back I discovered that they made dramatic upgrades to the tool they once called PrimalForms, which I had tried and discarded as a one-trick-pony for the rather esoteric task of writing PowerShell driven GUI forms. Renamed PowerShell Studio, it looked like it took all the good elements of PrimalScript and left out all the non-PowerShell stuff that I really didn’t care about. For a couple of weeks I’ve been working with it exclusively and it’s quite good. There are quite a few features that separate it from the other ISEs out there:

Swap v2 and v3 engines

This is an inspired feature. Especially with some of the known compatibility issues experienced running v2 scripts in v3, having this easy switch is a great help.

Layouts

There are so many features and toolbars that you can go a little nuts trying to navigate them all. And when I want to code I just want a blank window to hammer out the text. Layouts give you the opportunity to configure the interface to your heart’s desire *for any given scenario.* When I’m just pounding the keyboard I want a particular set of toolbars, but when I’m debugging I want something different. Likewise, if I’m doing code modifications and regression testing, I have even different needs. It’s quite easy to configure the interface and save the configuration for instant recall later. Here’s what I look at most of the time (Click to enlarge).

layout

Object Browser

This is a killer feature. The object browser is a toolbar that presents all of the components in PowerShell, the .NET framework, WMI, and your filesystem in one (searchable) interface. I find myself spending a lot of time in BOL trying to figure out various esoteric SMO issues, find overloaded constructor definitions, and umpteen other time chewers that an only-kind-of-developer like me has to do. This is a great help to be able to quickly browse through what is available and, if I need more information, the right-click -> MSDN Help shortcut is just awesome.

object_browser

Contextual help

As you type cmdlets the Get-Help contents opens automagically in a dock window. This is particularly helpful now that v3 added all sorts of cmdlets and there’s no way you can be familiar with them all. And there’s always that ‘what was the parameter name’ moment that I have shamefully often. It’s very handy and shows that the developers of the tool really understand PowerShell scripting.

help

Script packager

Bundle your script as an .exe? I didn’t get it at first. But, I tried it out and I’ll definitely have use for it. I’m constantly wanting to share something cool with people, but I can’t teach everyone PowerShell. Now I can just package it up and send them an .exe. As an added benefit, they can’t edit or see the code so it prevents changes that may break your code as well as perhaps protecting some secret sauce you don’t want to share (but, don’t be that guy!).

Compare files

I work in a scenario where I have to go through very clearly defined steps of testing and elevating PowerShell code between environments. Being able to click a button and view the differences between two different files is a big time saver.

There really are too many great features to list here and many of them I have yet to really dig into. Here’s a quick (but certainly not exhaustive) look at a few more.

  • Remoting – Set up your quick access list and open a remote PowerShell console without leaving the tool.
  • Embeded console that actually works – I don’t have to have a separate PowerShell window open. I can do everything within the tool.
  • Snippets – Other tools have snippets, but I find Powershell Studio to have the superior implementation. They are easy to use with the shortcut + tab pattern and it’s simple to create your own.
  • Auto-module import – Try to use a function from a module you haven’t loaded and PowerShell Studio will insert the Import line.
  • Projects – Being able to create a project is very handy for working with modules or multiple scripts/functions that are generally modified together.
  • Function Explorer – When you’re working with modules it’s very helpful to be able to see a list of all the functions within and navigate to their place in the module file.

Wish List

  • Better configuration – PowerShell Studio is sufficiently configurable. Most of the things that would annoy me I’ve been able to turn off or alter. But, as you’ve probably guessed I’m rather picky about my development environments. One of the first things I do when I try out a new IDE is set the colors. I think I’ve come up with the absolute best, objectively speaking, color scheme of all time that I’ve spent hours perfecting and I’m UNABLE TO USE it in PowerShell Studio because you can’t enter your own RGB values. Travesty! Also, I can’t configure line number coloring at all! The horror! As you can see here, what I came up with isn’t too bad, but not nearly as beautiful as my PrimalScript settings.

    colors

  • Disable tabs grabbing focus – I don’t care to know every time an auto-save occurs. I don’t need the output tab to pop up every time.
  • Code formatting – The more configurable the better!
  • License and embed Sublime Text – I greatly admire text editors. It’s an area most people ignore, but there are so many awesome things you can do with a good one. Sublime Text is my current fave (thanks to one of those guys I respect who shared it with me). Of course, a vi style editor would also be fantastic. Free me from the mouse!
  • Better integration with source control – I understand Sapien has their own source control product, but I can’t use it. I have TFS and I simply wasn’t able to get them to play well together.

Conclusion

I really wish I could return PrimalScript and go with PowerShell Studio. I’m a PrimalScript fan and will happily continue to use it, but when it comes time to upgrade I’m going with its PowerShell specific sister. If you spend a lot of time writing PowerShell code there’s just no better ISE out there.

psasync Module: Multithreaded PowerShell

The series I did on Concurrency in PowerShell contains by far the most popular posts on my blog (getting tens of views every day!), particularly the one on using runspaces. The code demonstrated in the post was an early attempt at using the method. Not long after I refactored the functions I was using into something more modular and flexible. Thus, the psasync module was born. It has proved to be a very simple way for me to kick off background work in runspaces in scripts or from the shell. It still has room for improvement (the functions don’t process pipeline input, for instance), but it’s a start. Since I hope to be updating it as suggestions come in or as I find ways to make it more robust, I’ve started a Codeplex project (my first, so be gentle). If you would like to be a contributor feel free to send me something and, if it’s good, I’ll give you credit on the project.

The module contains the following functions:

Get-RunspacePool

This should have, perhaps, been named Create-RunspacePool. As its name suggests, it returns a pool of runspaces that can be used (and reused).

Invoke-Async

This is a much improved version of the function introduced in the initial post. The big improvement was the addition of the AsyncPipeline class definition, which allows the creation of a simple object to keep track of both the pipeline and the AsyncResult handle which is returned by BeginInvoke(). This allows the process of looking at statuses of running processes and consuming results to be much simpler. The function also allows passing an array of parameters for script blocks with multiple arguments.

Receive-AsyncResults

This function wraps the code for pulling results (or errors, as the case may be) off the pipelines in the runspace pool utilizing the AsyncPipeline objects output from Invoke-Async.

Receive-AsyncStatus

A handy function for working with runspaces from the shell, Receive-AsyncStatus simply returns information about the status of the operations running in the pipelines you have invoked. Since Receive-AsyncResults is synchronous, this allows you to continue to work until your last process completes or selectively use Receive-AsyncResults on those that have completed.

Example Code

To demonstrate the use of the module, consider the same scenario presented in the Concurrency series: You have a series of Excel documents that you need to load into an SQL Server database. As before, set up the script block that will execute the real work.

Import-Module psasync

$AsyncPipelines = @()

$ScriptBlock = `
{
    Param($File)
    
    . <your_path>\Import-ExcelToSQLServer.ps1
    
    Import-ExcelToSQLServer -ServerName 'localhost' -DatabaseName 'SQLSaturday' -SheetName "SQLSaturday_1" `
        -TableName $($File.BaseName) -FilePath $($File.FullName)
}

# Create a pool of 3 runspaces
$pool = Get-RunspacePool 3

$files = Get-ChildItem <path-to-files> 

foreach($file in $files)
{
	 $AsyncPipelines += Invoke-Async -RunspacePool $pool -ScriptBlock $ScriptBlock -Parameters $file
}

Receive-AsyncStatus -Pipelines $AsyncPipelines

Receive-AsyncResults -Pipelines $AsyncPipelines -ShowProgress

You’ll notice there is nothing particularly complex here in the code. But, all the warnings from the runspace post apply. Multithreading is awesome and powerful, but use it with care.

Concurrency in PowerShell: Background jobs

This is part one of a series.
Download code for this post.

There are three methods to achieve asynchronous processing in PowerShell: background jobs, System.Management.Automation namespace classes, and (coming soon in PoSH v3!) workflows. Workflows actually share characteristics of the other two, but they are different enough to be a new method. I suppose you could also use the System.Threading .NET namespace, but then you’re writing straight .NET code and that’s just cheating. Over the next three weeks, I’ll be posting information about all three of these methods. There will be a lot of information, but it will all be useful and necessary. If you can avoid some of the problems I ran into it will be worth while. I’ll try not to be long-winded, but no promises there.

Before you get started you should know that there are a lot of ‘gotchas.’ Like a lot of PowerShell you’ll come across behavior that isn’t particularly intuitive. There are also a few ways you can get yourself into real trouble. You should familiarize yourself with the concept of thread safety, for example.

The scenario for this series is a common one: I have 50 Excel worksheets that need to be loaded into an SQL Server database as a staging phase for an ETL process. Of course, I could load these one at a time, but depending on how large they are it could take a significant amount of time. Most importantly, I am not fully utilizing the computing resources at my disposal. I want to be to control the amount of resources I use so I can ramp it up for speed if I so desire. One way to do attempt this is through the concurrent processing pattern. Backup jobs are enabled through the use of the *-Job cmdlets and operate very similarly to the forking concurrent process model. Using these cmdlets is straightforward and generally protects you from doing anything particularly harmful, although it isn’t hard to make your computer unusable for a while as I’ll demonstrate shortly.

To get started, I need a data structure for the Excel documents that I’ll be working with as well as a script block to define the work. I’m using a version of some code I posted a while back for this data load task that I have wrapped into a function called Import-ExcelToSQLServer. Note also that this series is based on a presentation I gave at SQL Saturday #111 so you’ll see references throughout this series.

$files = Get-ChildItem '<path>' 

$ScriptBlock = `
{
    Param($File)
   
    Import-ExcelToSQLServer -ServerName 'localhost' -DatabaseName 'SQLSaturday' -SheetName "SQLSaturday_1" `
            -TableName $($File.Name.Replace('.xlsx','')) -FilePath $($File.FullName) -EA Stop
}

Note the properties of the $File object that are being used. Astute PoSHers will note that I’m having to jump through some hoops to get the parameters formatted, which shouldn’t be necessary with System.IO.FileInfo objects. The problem is that though there are System.IO.FileInfo objects in the $Files array the objects only exist in the memory space of the dispatcher process. This means that the new process created by the Start-Job cmdlet has no access to the object across the process context barrier. As a result, the only mechanism available to pass information to the spawned process is through serialization. PowerShell converts the object to XML and then deserializes the XML into an object of type [Deserialized.System.IO.FileInfo] that has a modified set of parameters and only the ToString() method. This is quite frustrating if you are hoping to utilize methods of a FileInfo object, which are no longer available. Not only that, but the overhead of serialization and deserialization is non-trivial, not to mention the overhead of instantiating another FileInfo object if required. On the other hand, the upside is that issues of concurrency control are eliminated and thread safety is guaranteed.

The intention is now to execute the script block for every file in the $files array and I do not want to wait for one to complete before the other begins. So, I can use a foreach loop to execute Start-Job.

Foreach($file in $files)
{
    Start-Job -ScriptBlock $ScriptBlock -ArgumentList $file `
         -InitializationScript {. Import-ExcelToSQLServer.ps1}
}

Note the need to include the file containing the function code as an initialization script, even if it is loaded in the host process, due to the process barrier. The new process will, however, execute the PowerShell profile if you have one defined. If you have task manager opened after executing this you’ll see something like this.

Start-Job Task Manager results

A process has been created for each file. The Start-Job cmdlet has also registered handles in the session. These handles are the connection from the host session to the pipeline running in the forked process.

Once the loop exits, control is returned to the host. This is the real intention of the *-Job cmdlets. They allow you to execute work and continue to utilize the shell. The jobs are available to check at the PoSHer’s leisure. If desired, you could use the Wait-Job cmdlet to enter a synchronous state and concede control until the specified job(s) is/are completed.

When the time comes, check to see if there are any jobs still running like so.

Get-Job -State "Running"

The Receive-Job cmdlet utilizes the handles registered by the Start-Job cmdlet to access the external process pipelines. Of course, you could provide a single job number or array to Receive-Job. To receive the results of all jobs at one time simply use the pipeline.

Get-Job | Receive-Job

When completed, remember to clear the job-handles.

Remove-Job *

Should I Use Background Jobs?

Fifty running PowerShell processes at ~20MB of memory each (or more, depending on the work), plus the required CPU time (compounded by the need to manage schedulers, etc.) makes for a huge amount of overhead. This results in a run-time that is more than three times longer than executing the tasks synchronously. The individual units of work (one script block execution) needs to be sufficiently long to overcome the overhead of the forked process model.

Significant improvement can be made by throttling the number of processes that are allowed to run at one time. To do so, modify the Start-Job loop like so:

Foreach($file in $files)
{
    Start-Job -ScriptBlock $ScriptBlock -ArgumentList $file `
         -InitializationScript {. Import-ExcelToSQLServer.ps1}

    While((Get-Job -State 'Running').Count -ge 2)
    {
        Start-Sleep -Milliseconds 10
    }
} 

The addition of the while loop forces a wait if there are two or more running jobs. This dramatically improves the performance of the process by reducing the amount of overhead at the cost of causing the jobs to essentially run synchronously relative to the dispatching process. Unfortunately, it is still slower than running the jobs synchronously.

Next week I will look at a much more effective method. I am also currently working on a module that will (hopefully) provide a replacement for the Job cmdlets that will operate in essentially the same manner, just much more efficiently. However, it is still important to understand the Job cmdlets if for no other reason than they are on every computer with PowerShell. Additionally, they are simple and perfectly effective in some use cases, such as kicking off a long running process while maintaining control of the shell.