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.
Get-Command -Module SQLServer -Name *Sql*Data
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
}
)
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
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})
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
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
The nice thing about these cmdlets is they’re native to the latest version of the SQLServer PowerShell module.
µ