Useful way to edit the ServiceConfig.json file in bulk using PowerShell

[chadbaldwin]

I needed to update 90 instance configurations to add TableSize collection settings. So I threw together this script and thought I would share.

$config = Get-Content .\ServiceConfig.json | ConvertFrom-Json

$sched = [pscustomobject]@{ Schedule = '0 0 */12 ? * *'; RunOnServiceStart = $true }

$config.SourceConnections | ForEach-Object {
    $_ | Add-Member 'TableSizeCollectionThresholdMB' 10 -Force
    $_ | Add-Member 'TableSizeMaxDatabaseThreshold' 500 -Force
    $_ | Add-Member 'TableSizeDatabases' 'FOO_*,BAR_*,-FOO_QUX' -Force
    $_ | Add-Member 'TableSizeMaxTableThreshold' 4000 -Force
    $_.CollectionSchedules | Add-Member 'TableSize' $sched -Force
}

$config | ConvertTo-Json -Depth 100 | Set-Clipboard # pipe to clipboard, I like to do this so I can paste into a diff tool before overwriting the file

By using Add-Member ... -Force it will add or overwrite the member if it exists.

At the end, the contents is copied to the clipboard because I like to paste the contents into WinMerge to compare the current file with the new contents. Fortunately, PowerShell seems to format the JSON in the same way that the DBADashConfigTool does, so it's a pretty clean compare.

And unfortunately, -Depth 100 is necessary because PowerShell is weird and flattens objects in JSON if you don't specify that.