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):
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
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)
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
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})
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
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
The nice thing about these cmdlets is they're native to the latest version of the SQLServer PowerShell module.
µ