Store and Retrieve PowerShell Hash Tables in a SQL Server Database with Write-SqlTableData and Read-SqlTableData

In my blog article from last week, I demonstrated using several older open source PowerShell functions to store the environmental portion of the code from operational validation tests in a SQL Server database and then later retrieve it and re-hydrate it back into a PowerShell hash table.

Earlier this week, a new release of the SQLServer PowerShell module was released as part of SSMS (SQL Server Management Studio):

sqlserver164-1a.png

It includes three new cmdlets, two of which can be used to store and retrieve data in a SQL Server database from PowerShell instead of the older open source ones that I demonstrated in the previously referenced blog article from last week.

Get-Command -Module SQLServer -Name *Sql*Data

sqlserver164-7a.png

A array of PowerShell hash tables have been stored in a variable named ConfigData:

$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
        }
)

sqlserver164-2a.png

I prefer to convert the hash tables to PSCustomObjects before storing the information in the database because the data seems more natural in the database and the results of a simple database query are cleaner.

Several of the examples shown in this blog article require PowerShell version 4 or higher since I’ve chosen to use the ForEach method.

$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_})}) |
Format-Table

$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_})}) |
Get-Member

sqlserver164-3a.png

Store a few things in variables so they don’t have to be continually reference statically:

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

The SQL Server database already exists, but not the table. The Force parameter creates the table automatically:

$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_}) |
Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName dbo -TableName $Table -Force})

sqlserver164-4a.png

Read the data from SQL Server to verify that it was indeed written to the database:

Read-SqlTableData -ServerInstance $Instance -TableName $Table -DatabaseName $Database -SchemaName dbo |
Format-Table -AutoSize

Read-SqlTableData -ServerInstance $Instance -TableName $Table -DatabaseName $Database -SchemaName dbo |
Get-Member

sqlserver164-5b.png

Use my ConvertTo-MrHashTable function (which can be downloaded from my PowerShell repository on GitHub) to convert the data retrieved from SQL Server back into a hash table:

Read-SqlTableData -ServerInstance $Instance -TableName $Table -DatabaseName $Database -SchemaName dbo |
ConvertTo-MrHashTable

sqlserver164-6a.png

The nice thing about these cmdlets is they’re native to the latest version of the SQLServer PowerShell module.

µ