PowerShell Snippet: Verify Minimum Version of .NET

Occasionally it’s useful to verify the minimum version of .NET installed on a box. This PowerShell does exactly that.

$Netver = (Get-ItemProperty ‘HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full’ -Name Release).Release
write-host ".NET Version : $netver"

ElseIf ($Netver -le 394271)
throw "At least .NET Framework 4.6.1 needs to be installed on this tentacle to use Microsoft.Data.Tools.Msbuild."

PowerShell Snippet #7: Retrieve SQL Server Licence Key

Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!


function GetSqlServerProductKey {
## function to retrieve the license key of a SQL 2014 Server.
param ($targets = ".")
$hklm = 2147483650
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\Setup"
$regValue1 = "DigitalProductId"
$regValue2 = "PatchLevel"
$regValue3 = "Edition"
Foreach ($target in $targets) {
$productKey = $null
$win32os = $null
$wmi = [WMIClass]"\\$target\root\default:stdRegProv"
$data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)
[string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
[string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue
$binArray = ($data.uValue)[0..16]
$charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
## decrypt base24 encoded binary data
For ($i = 24; $i -ge 0; $i--) {
$k = 0
For ($j = 14; $j -ge 0; $j--) {
$k = $k * 256 -bxor $binArray[$j]
$binArray[$j] = [math]::truncate($k / 24)
$k = $k % 24
$productKey = $charsArray[$k] + $productKey
If (($i % 5 -eq 0) -and ($i -ne 0)) {
$productKey = "-" + $productKey
$win32os = Get-WmiObject Win32_OperatingSystem -computer $target
$obj = New-Object Object
$obj | Add-Member Noteproperty Computer -value $target
$obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
$obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
$obj | Add-Member Noteproperty SQLver -value $SQLver
$obj | Add-Member Noteproperty SQLedition -value $SQLedition
$obj | Add-Member Noteproperty ProductKey -value $productkey


PowerShell Snippet #6

Recently I needed to run Invoke-Sqlcmd that would use a connection string of a variety of parameters. Below is the script I used to accept whatever parameters would be passed in to the “Invoke-sqlcmd”. The input file contained the same text as the $query parameter.


$ServerInstance = ".\instance"
$database =  "Northgale"
$uname = "user"
$pword = "password123"
$query = "'SELECT GETDATE() AS TimeOfQuery;'"
$inputFile = "C:\Users\Richard\Documents\inputfile.sql"

$connectionString = "-ServerInstance $ServerInstance -UserName $uname -Password $pword -Database $database -verbose"
$connectionString  = $connectionString -split '(?=prg=PowerShell-)'
$InvokeSqlcmd  = "Invoke-Sqlcmd -inputfile $inputFile $connectionstring"
write-host $InvokeSqlcmd -verbose
Powershell.exe -command "$invokeSqlCmd"

VSTS Hosted Build Agent Specs

I was interested to know just what the hardware specifications of the hosted build agent is. So I added some PowerShell to read out the info below:


2016-06-29T09:23:31.3935358Z systemname      Name                                      DeviceID NumberOfCores NumberOfLogicalProcessors Addresswidth
2016-06-29T09:23:31.3935358Z ----------      ----                                      -------- ------------- ------------------------- ------------
2016-06-29T09:23:31.3935358Z TASKAGENT5-0010 Intel(R) Xeon(R) CPU E5-2673 v3 @ 2.40GHz CPU0                 2                         2           64
2016-06-29T09:23:31.4095356Z Total memory:  7167.55078125

What piqued my interest greater was that this is the exact same spec for a D2 v2 box that is available via Azure. Clearly, Microsoft have a build agent template which is built, stored in a pool, and provisioned whenever a build takes place. One must assume that it is disposed of after a single use to prevent and nefarious activity by other people trying to steal code….


Deploying SSDT Scripts (With My Good Eye Closed)

(If anyone actually gets the reference: kudos. If not, then it’s just that you’re not a Soundgarden fan)

So, there’s something that has been troubling me about deploying to databases with SSDT for some time. Actually, it’s not a SSDT-exclusive problem per se, it’s more of a database/release management process.

What it pertains to is that most people like to create the deployment script for review prior to deploying to production. This makes sense if you are new to the Continuous Deployment process, and that maybe sometimes SSDT creates scripts that are not quite expected. You may skip this because you feel rather more confident in your deployments, or the database sufficiently simple enough to just deploy without having to review. But generating the script as a deploy artefact is still a good idea, because auditing.

At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?

The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.
Continue reading “Deploying SSDT Scripts (With My Good Eye Closed)”

PowerShell Snippet#5: Running MSBuild

Script on running MSBuild via PowerShell. This ties up neatly with my previous post.

$msbuild = "C:\Windows\Microsoft.Net\Framework\v4.0.30319\MSBuild.exe"
$MsBuilExists = Test-Path $msbuild
If ($MsBuilExists -ne $true) {write-host "msbuild does not exist at this location. Install Visual Studio 2015 (Community Edition should be adequate)"}
$buildFile = $PSScriptRoot+"\BuildAllDBProjects.targets.xml"
& $msbuild $buildFile

MSBuild Snippet#1 : Ignore Solutions

How do we ignore solutions in a build? We could specify only the ones we want to build, but depending on your weighting of include/exclude solutions, this could be costly. So here’s how to ignore solutions using MSBuild:


<Project ToolsVersion="4.0"

<ProjectReference Include=".\**\AV2014.sln" Exclude=".\**\DBSolutionToIgnore.sln"/>

<Target Name="Default">
<MSBuild Projects="@(ProjectReference)" Targets="Rebuild"/>


Turbo LogShip 1.0 Released

Earlier this year, I wrote a long post about log shipping. One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I’ll save you the effort (you child of the internet you) and tell you it’s because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of all the pages that have uncommitted transactions saved to them. The checking, creation, applying and re-applying of this can take some time where there is a significant amount of uncommitted transactions within a log backup. Continue reading “Turbo LogShip 1.0 Released”

PowerShell Snippet #4



Below is a function that will search in  a string and replace a character in a variable. Simple enough. It generally saves time in calling this from a helper file in source control for tidying up variables that are passed in to a Powershell function that aren’t quite formatted as required. A good example of this is upstream build variables with spaces in TeamCity.

Function FormatReplaceVariable

Write-Host "Replacing characters $find with $substitute in $param" -Verbose
$param = $param -replace "$find", "$substitute"
Write-Host "Now parameter looks like $param" -Verbose
return $param

$bob = FormatReplaceVariable -param "as'lfjksaj'JASD;oasdjf'asd aasd asd; ghas'fkasdf;aksd" -find ";" -substitute "!!!!"
write-host $bob

PowerShell Snippet #3


Today’s snippet is a two-for-one type deal: connecting to SQL Server and running a query to get datetime. Really, it’s just useful for checking connectivity issues.

$dataSource = “[serverInstance]”

$database = “[database]”
$connectionString = “Server=$dataSource;Integrated Security=True;Database=$database;”
write-host $connectionString -Verbose

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString


$query = “SELECT GETDATE() AS TimeOfQuery;”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”

Alternative to use InvokeSqlCmd:

Import-Module sqlps
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "[serverInstance]; Trusted_Connection=True; Initial Catalog=[database]"