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:

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


Create a SQL Server database named PSConfigData:


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:


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:


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


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


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.

Simply pipe the previous command to the ConvertTo-MrHashTable function to turn the results into a hash 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:


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.


1 Comment

  1. crshnbrn66

    Mike just curious for your setup you speak of here is there a reason you haven’t looked at something like: VSTFS, Jenkins, TeamCity, OctopusDeploy or other continuous integration products that are very good at storing environment configs?


Leave a Reply

%d bloggers like this: