Store Environmental Code in a SQL Server Database for PowerShell Operational Validation Tests

I’ve previously published articles on separating environmental code from structural code for both DSC (Desired State Configuration) and Operational Validation or Readiness Tests. This article picks up where I left off last week in Separating Environmental Code from Structural Code in PowerShell Operational Validation Tests.

As many existing open source PowerShell functions as possible have been used in the examples shown in this blog article instead of re-inventing the wheel and rewriting everything from scratch. All of these open-source functions have been added to my SQL repository on GitHub. Also note that several of the examples shown in this blog article require PowerShell version 4 or higher since I’ve chosen to use the ForEach method.

I’ll start out with the environmental code that I used in the previously referenced blog article from last week:

$ConfigData = @(

            Environment = 'Production'
            RtServiceName = '*Runtime Server'
            RtPort = 9750
            RtSSLPort = 9760
            AdmServiceName = '*System Admin Server'
            AdmPort = 1234
            AdmSSLPort = 1235
            Environment = 'Training'
            RtServiceName = '*Runtime Server - Train'
            RtPort = 9850
            RtSSLPort = 9860
            AdmServiceName = '*System Admin Server - Train'
            AdmPort = 4234
            AdmSSLPort = 4235
            Environment = 'Test'
            RtServiceName = '*Runtime Server - Test'
            RtPort = 9950
            RtSSLPort = 9960
            AdmServiceName = '*System Admin Server - Test'
            AdmPort = 3234
            AdmSSLPort = 3235
            Environment = 'Report'
            RtServiceName = '*Runtime Server - Report'
            RtPort = 9650
            RtSSLPort = 9660
            AdmServiceName = '*System Admin Server - Report'
            AdmPort = 2234
            AdmSSLPort = 2235


Store a few things in variables that I don’t want to have to continually reference statically:

$Instance = 'sql01'
$Database = 'PSConfigData'
$Table = 'AppServer'


Create a SQL Server database named PSConfigData:

Invoke-Sqlcmd2 -ServerInstance $Instance -Database master -QueryTimeout 10 -Query "
    Create Database $Database


The next command is a little tricky because $ConfigData contains an array of hash tables. Create an array of data tables from the hash tables stored in the $ConfigData variable:

$DataTable = $ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_}) | Out-DataTable})


Create a table named AppServer in the SQL Server database. Since the $DataTable variable contains an array of data tables, only one of them should be specified when creating the actual sql table:

Add-SqlTable -ServerInstance $Instance -Database $Database -TableName $Table -DataTable $DataTable[0]


Write the data from each of the data tables stored in the $DataTable variable to the newly created SQL Server database table:

$DataTable.ForEach({Write-DataTable -ServerInstance $Instance -Database $Database -TableName $Table -Data $_ -QueryTimeout 10})


Query the previously inserted data from the SQL Server database to verify that it does indeed exist in the table:

Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query "SELECT * FROM $Table" | Format-Table -AutoSize


None of this would be useful unless the environmental data previously stored in the SQL Server database could be re-hydrated and turned back into a PowerShell hash table.

I’ve created a reusable function named ConvertTo-MrHashTable to turn objects into a hash table. This function can be downloaded from my PowerShell repository on GitHub.

function ConvertTo-MrHashTable {
    param (
        foreach ($o in $Object) {
            $hashtable = @{}

            foreach ($p in Get-Member -InputObject $o -MemberType Property) {
                $hashtable.($p.Name) = $o.($p.Name)

            Write-Output $hashtable

Simply pipe the previous command to the ConvertTo-MrHashTable function to turn the results into a hash table:

Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query "SELECT * FROM $Table" |


I’ll use the same operational validation test from the previous referenced blog article from last week where I separated the environmental code from the structural code except this time I’ll retrieve the environmental portion of the code from the SQL Server database:

Test-MrAppServer -ComputerName Server01, Server02 -ConfigurationData (
    Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query "SELECT * FROM $Table" |


Since I haven’t seen anyone else take the approach of storing their environmental code in a database, I’d like to know what your thoughts are and if you can think of any problems that I might not have considered.