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.

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

sqlserver164-7a.png

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

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

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.

1$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_})}) |
2Format-Table
3
4$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_})}) |
5Get-Member

sqlserver164-3a.png

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

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

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

1$ConfigData.ForEach({$_.ForEach({[PSCustomObject]$_}) |
2Write-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:

1Read-SqlTableData -ServerInstance $Instance -TableName $Table -DatabaseName $Database -SchemaName dbo |
2Format-Table -AutoSize
3
4Read-SqlTableData -ServerInstance $Instance -TableName $Table -DatabaseName $Database -SchemaName dbo |
5Get-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:

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

sqlserver164-6a.png

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

µ