Skip to content

posts🔗

Powershell Module Improvements for SQL Server in 2017

{{< admonition type="info" title="Updated: 2018-03-19" >}} I don't use these much, if any now. Check out dbatools which is a much better module with a full range of features to save you a ton of time. {{< /admonition >}}

simple setup

A major improvement that seems to have quietly slipped into the sql developers world is an improved SQLServer powershell module. The improved module is finally available in the powershell gallery, allowing a super quick setup on a server. No more installing SSMS to get them!

This is very promising, and great if you want to leverage some of the functionality on various build servers, or other machines that might not have SSMS installed.

Powershell Gallery - SqlServer

new cmdlets

In reviewing, I ran across a few new cmdlet's as well. For instance, you could easily right click on a table and output the results into a powershell object, json, csv, gridview, or anything else you want. This is great flexibility.

exploring-sql-path-provider

In versions of SQL Server (as of 2012 or earlier) I believe the version SQL Server was utilizing was out of date with the installed version. For instance, on Windows Server 2012 with Powershell ISE reporting PsVersion of 4.0, Sql Server reported version 2.0 being utilized.

In 2014 instances I had, the powershell invoked from SSMS shows the matching up to date version, which gives much better capability and functionality.

simple benefits for the inquiring mind

If you are not familar with the potentional benefits from being able to quickly invoke a powershell prompt and use SQL server cmdlets (prebuilt functionality that is easily called), I can give you a few use cases.

If you were asked to run a query, then export the results to a spreadsheet, it would be relatively simple as a cut and paste. However, if you needed to loop through every table in the database, and put each one to it's own excel workbook, powershell would allow you to quickly loop, convert the datatable returned into an excel worksheet, and either append into new worksheets, or create completely seperate new files. For automation possibilities, you've got a tremendous amount of potentional time savings if you can get comfortable with powershell.

In my case, I've found Powershell to be a great tool to help me understand more of the .NET framework as I use various cmdlets or .NET accelerators.

Running InfluxDB as a service in Windows

Run as a Service

As part of the process to setup some metrics collections for sql-server based on perfmon counters I've been utilizing InfluxDB. Part of getting started on this is ensuring InfluxDB runs as a service instead of requiring me to launch the exe manually. For more information on InfluxDb, see my other post: Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev

This of course, did not go without it's share of investigation since I'm working with a compiled executable that was originally built in GO. I had issues registering InfluxDB as a service. This is typically due to enviromental/path variables. In my powershell launch of InfluxD.exe I typically used a script like the following:

I investigated running as a service and found a great reminder on using NSSM for this: Running Go executables ... as windows services ' Ricard Clau I went and downloaded NSSM again and first setup and register of the service went without a hitch, unlike my attempt at running New-service -name 'InfluxDB' -BinaryPathName 'C:\Influx\influxdb\InfluxD.exe' -DisplayName 'InfluxDB' -StartupType Automatic -Credential (get-credential). I'm pretty sure the core issue was the PATH variables and other related enviromental paths were not setup with "working directory" being the InfluxDB which would be expected by it.

NSSM - Non-Sucking Service Manager

Using nssm install provided the GUI which I used in this case. Using the following command I was able to see the steps taken to install, which would allow reproducing the install from a .bat file very easily.

set-location C:\tools
.\nssm.exe dump InfluxDB

This resulted in the following output:

C:\tools\nssm.exe install InfluxDB C:\Influx\influxdb\influxd.exe
C:\tools\nssm.exe set InfluxDB AppDirectory C:\Influx\influxdb
C:\tools\nssm.exe set InfluxDB AppExit Default Restart
C:\tools\nssm.exe set InfluxDB AppEvents Start/Pre C:\Influx\influxdb\influx.exe
C:\tools\nssm.exe set InfluxDB AppEvents Start/Post C:\Influx\influxdb\influx.exe
C:\tools\nssm.exe set InfluxDB AppNoConsole 1
C:\tools\nssm.exe set InfluxDB AppRestartDelay 60000
C:\tools\nssm.exe set InfluxDB DisplayName InfluxDB
C:\tools\nssm.exe set InfluxDB ObjectName SERVICENAME "PASSWORD"
C:\tools\nssm.exe set InfluxDB Start SERVICE_AUTO_START
C:\tools\nssm.exe set InfluxDB Type SERVICE_WIN32_OWN_PROCESS

Pretty awesome! It's a nice change to have something perfectly the first time with no issues.

Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev

{{< admonition type="info" title="Updated: 2020-04-29" >}} broken image links removed {{< /admonition >}}

There are some beautiful ways to visualize time series data with the tools I'm going to go over. This post is purely focused on the initial setup and saving you some time there. In a future post, I'll show how some of these tools can help you visualize your server performance in a powerful way, including taking metrics from multiple types of servers that be working with SQL Server, and combining the metrics when appropriate to give a full picture of performance.

A beautiful way to visualize performance across a variety of machines

It's pretty epic to combine information across a variety of sources and be able to relate the metrics to the "big picture" that individual machine monitoring might fail to shed light on.

Downloading

I started by running this quick powershell script to download the stable toolkit.

Once extracted, I moved the influx extracted subfolder into the InfluxDB folder to keep it clean. Now all the binaries rested in C:\Influx\InfluxDB folder with no nesting folders. I referenced the documentation for getting started with InfluxDB.

Setup Local InfluxDb

Started up the local influxdb binary.

Initializing the new database was simple as documented: create database statty

{{< admonition type="warning" title="Case Sensitivity" >}} InfluxDB is case sensitive. Make sure to check your case if something isn't working, such as use "DatabaseName" instead of use "databasename" {{< /admonition >}}

Also, if you get an error with access to the file, try running as admin.

More Enviromental Variable Fun

A simple fix to errors related to paths and the HOME variable these tools often need, per a Github issue, was to ensure the current path was available as a variable. I did this quickly with a simple batch file to launch the consoles as well as one option, as well as updated the Start-Process script to include a statement to set the env variable for the processes being started. This eliminated the issue. For more details see github issues

SET HOME=%~dp0
start influxd.exe
start influx.exe

An additional snippet for launching the console version via a bat file:

set HOME=C:\influx
cmd /k influx.exe -host "MyInfluxDbHost" -database "statty" -precision "s" -format column

Quick Start for Telegraf

Once you have this running you can take the telegraf binaries and run them on any other server to start capturing some default preset metrics. I launched with the following script and placed this in C:\Influx directory to make it easy to access for future runs.

Edit the conf file to add some tags, change default sampling interval and more. I'll post another article about setting up telegraf to run as a service in the future so search for more info

You can also apply the same bat file in the startup directory such as:

@REM alternative is using variable
@REM set TELEGRAF_CONFIG_PATH=C:\telegraf\telegraf.conf

start %~dp0telegraf.exe -config %~dp0telegraf.conf

Run Chronograf

One these metrics began to run, I ran Chronograf. This is Influx's alternative to Grafana, another more mature product.

Upon loading and opening up the instance monitor, I found immediately that I was able to get some metrics from the defaults.

Get Grafana

My preferred visualization tool, this was far more robust and well documented than Chronograf which has promise, but is a relatively new project.

When starting Grafana, you can run the following script. It creates a copy of the default ini to copy for the user to edit if not already there.

Once you open the localhost page, if you don't see datasources in the left hand drop down, create an organization and ensure you are an admin, you'll then see the option to add datasources. I simple pointed the page to InfluxDB console running on the server I had setup previously.

summary

This is just a quick guide on getting started as I found a lot of little bumps in the road since the projects are written in GO and not an easily run .NET project. Getting through this will hopefully give you a way to get started. I'll blog a bit more soon on visualization of the metrics captured, some custom annotations to help make metrics come alive with real-time event notifications (like "load test started" and "build ended" etc). It's a really promising solution for those who want some really nice flexibility in using perfmon and related metrics to visualize Windows and SQL Server performance.

Automate Windows Updates for Development

I've run into the case where I wanted updates continually applied, while the machine still was part of the GPO that didn't automatically install updates. For this developer and test oriented machine I wanted every update applied.

I utilized a great module for this and created a script to setup the task and logging to make this an easy task.

If you experience an issue with the WindowsUpdate Vs Microsoft update as the configured update provider, then you can just change the switch in the script for -MicrosoftUpdate to -WindowsUpdate

This isn't something I'd run in production, but I've found it helpful to updating a development server with the latest SQL Server updates, as well as a development machine, allowing me to keep up with any latest changes with minimal effort.

Change the reboot parameter to your preferred option in the script. I left as autoreboot for the purpose of a low priority dev server being updated.

Programming Fonts For The Newb

the camps

Once you get into coding fonts, you'll find that there are two primary camps.

  1. Don't give a crap about it. "I'll use defaults for everything and probably wouldn't care if I was coding in Arial". If this is you, then this post is definitely not for you. Please continue to enjoy Comic Sans with my pity. :-)
  2. Font aficionados "Your world will change forever once you use this specific font! It will increase your productivity 300%"

Inside the font afficiando realm, you have various subcultures.

  • Fixed Font Only
  • Elastic Tabstops are the future, why can't anyone get with the program? (Elastic tabtop fonts allow proportional fonts with better alignment )
  • Ligature Fonts changed my world

cool resource

One really cool resource for exploring these various types of fonts is Programming Fonts - Test Drive. This is a pretty cool resource to preview various fonts and find links and resources for them.

monospaced

Monospaced fonts ensure that every character take up the same amount of space regardless. This means a period takes up the same space as any other letter of the alphabet.

The goal in recommending this for code editing has to do with the purpose of what's being written and read. In reading your eyes flow over words, and punctuation, while important, supports the words. It doesn't need to take up the same space. In code, every punctuation character is just as important as every single letter written. If you have a bunch of nested formulas for example, reading

('....Total time to process: {0:g}' -f [timespan]::fromseconds(((Get-Date)-$StartTime).Totalseconds).ToString('hh\:mm\:ss'))

becomes harder than ensuring all the punctuation and special characters are easily readable like this:

('....Total time to process: {0:g}' -f [timespan]::fromseconds(((Get-Date)-$StartTime).Totalseconds).ToString('hh\:mm\:ss'))

Visual Studio, SSMS, and other editors by default choose a monospaced font in code editing. However, there are additional options besides the built in fonts.

some i've explored

  1. Bitstream Vera Sans Mono: My go to for a long time. It's aesthetically nice, and has a bit of the Ubuntu styling with some rounder edges.
  2. Fira Code Retina: Very nice with ligature support. This has become my current favorite due to the very nice style with the added perk of the ligatures. That's a nice little typography enhancement that really makes special combinations of characters stand out for readability. This is just a rendering feature that doesn't impact the underlying text per documentation:

    This is just a font rendering feature: underlying code remains ASCII-compatible. This helps to read and understand code faster. FiraCode Github

what to what to look for

As you dive into the world of exploring fonts, here's a couple things I'd look for.

  1. Characters that can hide problems are easily identified such as a period, or dash, most monospaced fonts are great for this, but some have smaller symbols that might make them a little less readable.
  2. Resizes well for your target zoom. I've tried some fonts that don't seem to look right once you change your zoom level or the size of the font. I looked up some details on this and apparently some fonts are bitmapped, and some vector images. If you are using bitmapped fonts, then the target size is ideal, while adjusting zoom level can cause blurriness or fuzzy quality as it's not going to rescale like a vector based font would. This isn't bad if you are ok with the normal font size levels.

{{< fancybox-gallery "fontgallery" "testcaption" "fira-code-mono.png" "source-code-pro.png" "bitstream-vera-sans-mono.png"

}}

So far my personal favorite is Fira Code, so check that one out if you are looking for something interesting to try.

FiraCode Github

TFS Custom Task - Service Actions (for TFS 2015 Update 2.1 or before)

{{< admonition type="info" title="Updated: 2020-04-29" >}} broken image links removed {{< /admonition >}}

Apparently, boolean values for custom VSTS tasks for versions prior to TFS 2015 Update 3) require some special handling as they don't pass the checkbox values as actual powershell $true or $false. Instead the task passes this information along as true or false. To properly handle this you'll need to pass in the value as a string then convert to boolean.

I found a great start on working on this solution in a blog post by Rene which has more detail, so check it out. In addition, some reading on promiscuous types with powershell can be helpful to understand why special handling is needed with conversion. For example, in the task.json file you'll have:

    "inputs": [
            {
                "defaultValue": "MyServiceName*",
                "label": "ServiceName",
                "name": "ServiceName",
                "required": true,
                "type": "string"
            },
            {
                "defaultValue": "true",
                "helpMarkDown": "issue restart command",
                "label": "ChangeCredentials",
                "name": "ChangeCredentials",
                "required": true,
                "type": "boolean"
            }

This boolean value provides a checkbox on the custom task window.

To properly work with the boolean value, you have to bring it in as a script then convert it to a boolean value.

    param(
             [string]$ServiceName
            ,[string]$ServiceAccount
            ,[string]$RestartService
            ,[string]$StartService
            ,[string]$StopService
            ,[string]$ChangeCredentials
    )

once you have the parameters, use .NET convert functionality to

[bool]$_RestartService    = [System.Convert]::ToBoolean($RestartService)
[bool]$_StartService      = [System.Convert]::ToBoolean($StartService)
[bool]$_StopService       = [System.Convert]::ToBoolean($StopService)
[bool]$_ChangeCredentials = [System.Convert]::ToBoolean($ChangeCredentials)

Below I've included a custom TFS Task for basic start/stop/restart/change credentials with a custom tfs task. It's not super refined, but it's a good start to get you on your way.

Red Gate SQL Clone (1) - Initial Setup

Note this was during earlier beta usage, so some of the UI and other features will have been updated more. I plan on writing more on this promising tool as I get a chance to dive into it more, especially the powershell cmdlets for database cloning automation. In the meantime, I believe the permissions issue is still relevant, so I'll post this as a reminder in case someone is working through the initial setup.

It seems like a real promising toolkit for testing and reducing storage requirements for testing database automated deployment pipelines.

Clone Setup Getting Started

Clone Setup Creating Clone

Add to local admin on machine

Error starting service

The Redgate SQL Clone service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

I wasn't using for a while due to error message I couldn't figure out. I then read through the help documentation again and found that the permissions required for the service account should be a local admin. Once I added the service account to local admins, it correctly allowed the service to start.

Error if you don

Life Hack: When you need a mouse pad

My Logitech Master mouse went on the fritz... Requiring me to use my trusty Microsoft mouse. This is an optical based mouse based mouse that started driving me insane with the lack of precision. This exhibited random skips and overall lack of agreeability. I ran across some posts mentioning optical mice have issues with certain surfaces making it hard to detect precise movements and suggested using a surface with a pattern or irregularity, even a piece of paper being scribbled on. I was using a white desk, very little pattern to work with. The MX Master worked perfectly, but it had spoiled me.

After searching the office and failing to find a usable mousepad (seriously!)... I googled diy mousepad.

Lo and beheld, a fantastic website provided the World's original cheapest Mousepad. Well worth visiting despite its self disparaging remarks. Thank you for your contribution to the Internet's vast amalgamation of priceless documents.

After feeling like an idiot, I printed, taped to my desk and the magical precision returned. Life hack!

Implicit Transactions

Never messed around with this setting in the server configuration, so I was unfamilar with the impact it would have.If I ran a statement with something like the following:

insert into foo
select bar
insert into foo
select bar

I know that if the first had an aborting error, such as text was too long, the second statement would not complete as the batch would have failed. If you instead did

insert into foo
select bar
GO
insert into foo
select bar

and had the same error, the second would be completed, since the first would throw an error, but the GO separates the second statement explicitly into another batch, and therefore another transaction. Interestingly, the Implicit Transactions option changes the behavior to making each statement act as if it was encapsulated by begin transaction --- commit transaction instead of requiring this to be defined. So if you set implicit transactions on and ran the statement below with no go statement:

insert into foo
select bar
insert into foo
select bar

It is really operating as if:

begin transaction
insert into foo
select bar
commit transaction
GO
begin transaction
insert into foo
select bar
commit transaction

MSDN - Implicit Conversions is a resource that further documents the behavior, indicating that a rollback for the particular transaction is handled automatically. This means that since that each statement is treated as a transaction that it will not abort the second statement and terminate execution if the first experinces the error, since by "implicit conversions" this would be handled separately. MSDN article with example code to walk through it https://technet.microsoft.com/en-us/library/ms190230(v=sql.105).aspx

/*******************************************************
STEP 1: SETUP
*******************************************************/
use tempdb;
set nocount on;
set xact_abort off;
set implicit_transactions off;
if object_id('dbo.TestImplicitTrans','U') is not null
begin
print 'Dropped dbo.TestImplicitTrans per existed';
drop table dbo.TestImplicitTrans;
end;
print 'create table dbo.TestImplicitTrans';
create table dbo.TestImplicitTrans
(
test_k int primary key
identity(1,1)
not null
,random_text varchar(5) not null
);
go
/*******************************************************
TEST 1:
xact_abort off
set implicit_transactions off
Results in:
- first transaction fails
- second transaction succeeds (this is due to xact_abort off not being activated)
test_k  random_text
2           12345
*******************************************************/
use tempdb;
go
set nocount on;
set xact_abort off;
set implicit_transactions off;
truncate table dbo.TestImplicitTrans;
print 'Statement 1 START';
insert into dbo.TestImplicitTrans (random_text) values ('00001x');
print 'Current trancount: ' + cast(@@trancount as varchar(100));
insert into dbo.TestImplicitTrans (random_text) values ('00002');
print 'Current trancount: ' + cast(@@trancount as varchar(100));
insert into dbo.TestImplicitTrans (random_text) values ('00003');
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 1 END';
print char(13) + char(13) + 'Statement 2 START';
insert  into dbo.TestImplicitTrans
(random_text)
values
('12345'  -- random_text - varchar(5)
);
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 2 END';
select
*
from
dbo.TestImplicitTrans as TIT;
go
/*******************************************************
TEST 2:
xact_abort on
set implicit_transactions off
Results in:
- first transaction fails
- second transaction doesn't execute due to xact abort being set on
test_k  random_text
NONE
*******************************************************/
use tempdb;
go
set nocount on;
set xact_abort on;
set implicit_transactions off;
truncate table dbo.TestImplicitTrans;
print 'Statement 1 START';
insert  into dbo.TestImplicitTrans
(random_text)
values
('12345x'  -- random_text - varchar(5)  ONE CHARACTER TOO LARGE
);
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 1 END';
print char(13) + char(13) + 'Statement 2 START';
insert  into dbo.TestImplicitTrans
(random_text)
values
('12345'  -- random_text - varchar(5)
);
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 2 END';
select
*
from
dbo.TestImplicitTrans as TIT;
go
/*******************************************************
TEST 2:
xact_abort off
set implicit_transactions off
Results in:
- first transaction fails
- second transaction doesn't execute due to xact abort being set on
test_k  random_text
NONE
*******************************************************/
use tempdb;
go
set nocount on;
set xact_abort on;
set implicit_transactions on;
truncate table dbo.TestImplicitTrans;
print 'Statement 1 START';
insert  into dbo.TestImplicitTrans
(random_text)
values
('12345x'  -- random_text - varchar(5)  ONE CHARACTER TOO LARGE
);
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 1 END';
print char(13) + char(13) + 'Statement 2 START';
insert  into dbo.TestImplicitTrans
(random_text)
values
('12345'  -- random_text - varchar(5)
);
print 'Successfully inserted: ' + cast(@@rowcount as varchar(10));
print 'Statement 2 END';
select
*
from
dbo.TestImplicitTrans as TIT;
go

Quick Way to Run Powershell Tasks in Parallel

Running tasks in parallel can be a bit difficult in powershell. However, there are a few projects out there that optimize the performance and provide a better experience of running tasks in parallel with less effort.#cool uses A few cool uses of this might be running parallel sql queries across multiple servers or databases while maintaining a throttled limit to avoid saturation of the target environment. Additionally, long running queries might benefit in running in parallel if running on multiple objects in the same database or in different databases.

module magic

I've utilized two main modules to advance this. PSParallel and PoshRSJobs. Both are fantastic options. The Invoke-Parallel is not steadily maintained, so I try to use PoshRSJob when possible. However, for ease of use the Invoke-Parallel option is pretty awesome as it automatically imports variables, functions, and modules into the block to allow for less work in defining parameters, having to use the $using:variablename clause, etc.

lots of gotchas

However, be prepared to deal with some complications in doing this with powershell. For instance, write-host, write-verbose, write-error, at this time can throw errors in PoshRSJob or not provide any output, as these streams are not incorporated the same as your local ISE session. In fact, at the time of this post, for output to stream from the PoshRSJob module, I had to change my output from:

write-host 'I know a kitten dies every time writehost is used, but I just cannot stop myself'

to

"I know a kitten dies every time writehost is used, but I just cannot stop myself"

Yes... no write-host/write-error/write-verbose is used here, just quotes for it. The developer and github community is looking to improve this, but at this time, don't expect logging or error messages to come through the same way.

Be prepared to deal with some complications on error handling when dealing with runspaces, as even though they are more performant, there is a lot of issues with scope to deal with in those isolated runspaces. Once you start increasing the size of the script blocks things can get hard to debug.

I think the simpler the task to pass into the parallel tasks, the better.

However, for some basic tasks that would benefit in parallel, you can definitely give it a shot. This task focused on iterating through a directory recursively and cleaning up each of the files by stripping out comments and blank lines. The following results were a simple example and interesting to compare.

    -------- Summary with PoshRSJobs--------
    File Size:   9.59 MB
    Total Count: 4,600.00
    Filepath: C:\temp\MyCleanedUpZip.zip
    Total Original Lines: 1221673
    Total Lines: 1,201,746.00
    Total Lines Saved:  21,959.00
    TOTAL TIME TO RUN: 08:43

    -------- Summary with Invoke-Parallel --------
    File Size:   6.69 MB
    Total Count: 4,447.00
    Filepath: C:\temp\MyCleanedUpZip.zip
    Total Original Lines: 1221436
    Total Lines: 854,375.00
    Total Lines Saved:  360,045.00
    TOTAL TIME TO RUN: 05:22

PoshRSJobs seemed to approach creating the job list first, which took a long time, and then processed the output very quickly. Overall, this took longer for this type of task. Invoke-Parallel gave an almost instant response showing the progress bar with estimated time remaining, so for this type of job it actually ran faster.

    -------- Summary - Native ForEach --------
    File Size:   6.69 MB
    Total Count: 4,621.00
    Filepath: C:\temp\MyCleanedUpZip.zip
    Total Original Lines: 1227408
    Total Lines: 861,600.00
    Total Lines Saved:  365,808.00
    TOTAL TIME TO RUN: 04:52

Surprising to me, the native foreach which was single threaded was faster. I believe in this case, the overhead of setting up the jobs was not worth parallel task processing. Since the task was a lot of small tasks, this probably wasn't a good candidate for parallel tasks. Based on this small test case, I'd venture to look into parallel tasks when longer run times are involved, such as perhaps copying large files that aren't oversaturating your IO. In this case, slow long copies would probably benefit from parallel tasks, while small text file copies as I showed wouldn't. A simple example of the difference in syntax for using PSParallel would be just counting lines in files in a directory.

$Folder = 'C:\Temp'
$startTime = get-date
[int]$TotalManualCount = 0
Get-ChildItem -Path $Folder -Recurse -Force ' where { ! $_.PSIsContainer } ' % { $TotalManualCount += (Get-Content -Path ($_.FullName) -Force ' Measure-Object -Line).Lines}
write-host ('Total Lines: {0:N2}' -f $TotalManualCount)
Write-host ('FOREACH: Total time to process: {0}' -f [timespan]::fromseconds(((Get-Date)-$StartTime).Totalseconds).ToString('mm\:ss'))
#Using Invoke-Parallel#
$ManualCount = [hashtable]::Synchronized(@{})
$ManualCount = @{
TotalCount     = 0
}
$Folder = 'C:\Temp'
$startTime = get-date
Get-ChildItem -Path $Folder -Recurse -Force ' where { ! $_.PSIsContainer } ' Start-RsJob -Throttle 4 -ArgumentList $ManualCount -ScriptBlock {
[cmdletbinding()]
param($ManualCount)
$ManualCount.TotalCount += (Get-Content -Path ($_.FullName) -Force ' Measure-Object -Line).Lines
}
write-host ('Total Lines: {0:N2}' -f $ManualCount.TotalCount)
Write-host ('INVOKE-PARALLEL: Total time to process: {0}' -f [timespan]::fromseconds(((Get-Date)-$StartTime).Totalseconds).ToString('mm\:ss'))

Note that this simple code example might have had some issues with counts due to locking with the synchronized hash table usage. Based on a few searches, it looks like you need to implement a lock on the hash table which ensures that particular thread is able to safely update. I didn't find clear proof that the synchronized hash table was working or failing, but it's something to be aware of. There are some active efforts on improving in PoshRSJob github issues. Hopefully you'll have a few new ideas on working with Parallel tasks in powershell now, and think about leveraging it for some tedious tasks that might benefit with SQL server or other administrative jobs.