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:

 1$ConfigData = @(
 2
 3        @{
 4            Environment = 'Production'
 5            RtServiceName = '*Runtime Server'
 6            RtPort = 9750
 7            RtSSLPort = 9760
 8            AdmServiceName = '*System Admin Server'
 9            AdmPort = 1234
10            AdmSSLPort = 1235
11        }
12        @{
13            Environment = 'Training'
14            RtServiceName = '*Runtime Server - Train'
15            RtPort = 9850
16            RtSSLPort = 9860
17            AdmServiceName = '*System Admin Server - Train'
18            AdmPort = 4234
19            AdmSSLPort = 4235
20        }
21        @{
22            Environment = 'Test'
23            RtServiceName = '*Runtime Server - Test'
24            RtPort = 9950
25            RtSSLPort = 9960
26            AdmServiceName = '*System Admin Server - Test'
27            AdmPort = 3234
28            AdmSSLPort = 3235
29        }
30        @{
31            Environment = 'Report'
32            RtServiceName = '*Runtime Server - Report'
33            RtPort = 9650
34            RtSSLPort = 9660
35            AdmServiceName = '*System Admin Server - Report'
36            AdmPort = 2234
37            AdmSSLPort = 2235
38        }
39
40)

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

1$Instance = 'sql01'
2$Database = 'PSConfigData'
3$Table = 'AppServer'

envcode-sql1a.png

Create a SQL Server database named PSConfigData:

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

envcode-sql2a.png

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:

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

envcode-sql3a.png

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:

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

envcode-sql4a.png

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

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

envcode-sql5a.png

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

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

envcode-sql6a.png

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.

 1function ConvertTo-MrHashTable {
 2    [CmdletBinding()]
 3    param (
 4        [Parameter(Mandatory,
 5                   ValueFromPipeline)]
 6        [PSObject[]]$Object
 7    )
 8    PROCESS {
 9        foreach ($o in $Object) {
10            $hashtable = @{}
11
12            foreach ($p in Get-Member -InputObject $o -MemberType Property) {
13                $hashtable.($p.Name) = $o.($p.Name)
14            }
15
16            Write-Output $hashtable
17        }
18    }
19}

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

1Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query "SELECT * FROM $Table" |
2ConvertTo-MrHashtable

envcode-sql7a.png

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:

1Test-MrAppServer -ComputerName Server01, Server02 -ConfigurationData (
2    Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query "SELECT * FROM $Table" |
3    ConvertTo-MrHashtable
4)

envcode-sql8a.png

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.

µ