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.
1 | Get-Command -Module SQLServer -Name *Sql*Data |
A array of PowerShell hash tables have been stored in a variable named ConfigData:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | $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.
1 2 3 4 5 | $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:
1 2 3 | $Instance = 'sql01' $Database = 'PSConfigData' $Table = 'AppServer' |
The SQL Server database already exists, but not the table. The Force parameter creates the table automatically:
1 2 | $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:
1 2 3 4 5 | 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:
1 2 | 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.
µ
Great Post! Thank you!
How would you recreate the objects you stored in the db?
Awesome Mike, just what I was looking for, thanks.
Ryan.