High-Level Concept

This PowerShell script will be shared between numerous SQL servers. If the server's name is in the JSON file, it will then run something similar to Set-Service -Name SQLEngine_InstanceA -StartType Manual using SQL service names from the JSON file and change their starttype to either "disabled", "on-demand", "manual", or "automatic". It will then start those services listed in the JSON file.

Goal

The JSON file in a specific format, and the script needs to act upon parameters specified in the JSON file. The script will check if the executing server's name exists in the JSON file then update the services starttype based on the JSON file parameters. It will also check against each parameter in the JSON file to know if it should or shouldn't act on it.

Basics of what I need to accomplish:

1. Server runs this PowerShell script regularly (every 15 mins) to know if the SQL services will be brought down for maintenance within that window.
2. Script runs against a JSON file StartServices.json which contains:
   • serverName
   • startAt
   • Services
     • serviceName
     • Order
     • startMode
3. If the ServerName matches $env:COMPUTERNAME and the startAt >= $Current_Time it continues to next step.
4. It should iterate through each service matching the computer name, specifically in the "Order" specified, and run Start-Service -Name $ServiceName -StartType $StartMode. Order is important because we require starting/stopping certain services before others.

I'm stuck on step #4. Here's a simplified example of the script I'm using to access the parameters. I'm unable to cleanly reference the Services section because PowerShell creates an array at that level when its ingested via ConvertFrom-JSON. The problem with an array is I want to avoid hardcoding indexes since there might be only 3 services to act on or more than 5.

I would like to access this element ideally by something like $content.Server_Name["ServerABC"].Services or similar Object based approach.

Example PowerShell Script

# Declare Variables
$InputFile = 'C:\temp\StartServices.json'
$ParsedRaw = Get-Content -Raw -Path $InputFile | ConvertFrom-Json
$vPSObject = $ParsedRaw
$serverName = $vPSObject.serverName
$services = $vPSObject.services #this just lists ALL service names, order, and startMode

# Check if JSON file exists
if (Test-Path -Path $InputFile -PathType Leaf) {
  Write-Host "JSON File Exists"

# Check if Server name is in list
  if ($serverName -contains $env:COMPUTERNAME) {
    $currentServerIndex = $serverName.IndexOf($env:COMPUTERNAME)
    Write-Host "The current index of $env:COMPUTERNAME is $currentServerIndex"
  
# Check if StartAt time in JSON is after the current time
    $DateTimeNow = Get-Date
    $DateTimeEvent = [DateTime]::ParseExact($vPSObject.startAt[$currentServerIndex], 'yyy-MM-dd HH:mm:ss', $null) # this format needed to match JSON time formatting
    if ($DateTimeEvent -gt $DateTimeNow.DateTime) {
      Write-Host "This will run since startAt is in the future"

# Area I'm stuck - Getting Service Start Mode & Status without using Indexes
    $StartTypeEngine = Get-Service -Name $vPSObject.serverName[$currentServerIndex].services.serviceName[0] | Select -Property starttype -ExpandProperty starttype
    $StartTypeBrowser = Get-Service -Name $vPSObject.serverName[$currentServerIndex].services.serviceName[1] | Select -Property starttype -ExpandProperty starttype
    $StartTypeAgent = Get-Service -Name $vPSObject.serverName[$currentServerIndex].services.serviceName[2] | Select -Property starttype -ExpandProperty starttype    

# If Variables are more dynamic the rest of the code would be as simple as:
    ForEach ($service in $services){
    Set-Service -Name $service.serviceName -StartType $service.StartupMode
    Start-Service -Name $service.serviceName 
    Write-Host "The service $service.serviceName has started and it's startup mode is set to $service.StartMode"
}
    }  

  }

}

Example JSON

[
 {
  "serverName": "Main_SQL_Server",
  "startAt" : "2021-10-14 10:00:00",
  "services": [
    { 
    "serviceName": "MSSQL$Cluster",
    "order": 1,
    "startupMode": "manual"
    },
    {
    "serviceName": "MsDtsServer",
    "order": 2,
    "startupMode": "manual"
    },
    {
    "serviceName": "SQLBrowser$Cluster",
    "order": 3,
    "startupMode": "manual"
    },
    {
    "serviceName": "SQLAgent$Cluster",
    "order": 4,
    "startupMode": "automatic"
    }
  ]
 },
{
  "serverName": "Other_SQL_Server",
  "startAt" : "2021-10-14 11:00:00",
  "services": [
    { 
    "serviceName": "MSSQL$Backup",
    "order": 1,
    "startupMode": "manual"
    },
    {
    "serviceName": "MsDtsServer",
    "order": 2,
    "startupMode": "auto"
    },
    {
    "serviceName": "SQLBrowser$Backup",
    "order": 3,
    "startupMode": "auto"
    },
    {
    "serviceName": "SQLAgent$Backup",
    "order": 4,
    "startupMode": "manual"
    }
   ]
  }
]

CodePudding user response：

This isn't hard! So you have an array of services for each server, right?

{
  "serverName": "Main_SQL_Server",
  "startAt" : "2021-10-14 10:00:00",
  "services": [
    { 
    "serviceName": "MSSQL$Cluster",
    "order": 1,
    "startupMode": "manual"
    },
    {
    "serviceName": "MsDtsServer",
    "order": 2,
    "startupMode": "manual"
    },
    {
    "serviceName": "SQLBrowser$Cluster",
    "order": 3,
    "startupMode": "manual"
    },
    {
    "serviceName": "SQLAgent$Cluster",
    "order": 4,
    "startupMode": "automatic"
    }
  ]
 }

We can load it like so:

$js = get-content c:\temp\stack.json

We can then pick just a specific server like this:

$server = $js | where serverName -eq Main_SQL_Server

You can then just iterate through the servers using a foreach loop.

forEach ($service in ($server.services | sort order)){
   Set-Service -Name $service.ServiceName -StartupType $service.StartupMode 
}