Safely Restart SQL Agent with Powershell

I recently had to enable tokens in SQL Agent across about 50 instances. Unfortunately, enabling tokens requires a SQL Agent restart.

After weighing the options – look at Job Activity monitor for each instance, verify no running jobs, then restart the SQL Agent service or write a cool new Powershell script – I decided to write the Powershell. Growing up, it was explained to me that work isn’t supposed to be fun. However, scripts like this continue to prove that one can indeed get paid to have fun.

Using the power of SMO and Powershell, I was able to loop over all the instances in a particular CMS group and determine if any jobs were currently running. If no jobs were running, tokens were enabled and the SQL Agent cluster resource reset. In this case, all the instances were on Server 2008R2 clusters, but this could be modified to use the WMI Win32_Service class to restart the remote service on a standalone instance or cluster.exe for a cluster on an older OS.

$q = "`""

#timeout in seconds
#If query does not return in this interval
#timeout will fire and move to next object
#prevents locks and blocking over this interval
#set to zero to wait indefinitely
$QueryTimeout = 120

#The instance name of the CMS
$CMSInstance = "CMSInstanceName"
   
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


#Connect to the CMS instance
$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
#The Registered Server store on the CMS
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

#Define the group and sub group that we want to loop over
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]

#Get the cmdlets to manage 2008R2 clusters
Import-Module FailoverClusters

#Loop over each registered server and use SMO to interrogate the Agent
foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
{
      $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;

      #the SMO JobServer class represents the SQL Server Agent associated with the instance
      $SQLAgent = $srv.JobServer

      #If tokens are not enabled, try to enable them
      if($SQLAgent.ReplaceAlertTokensEnabled -eq $false)
      {
            $JobsExecuting = 0
            foreach($Job in $SQLAgent.Jobs)
            {
                  if($Job.CurrentRunStatus.ToString() -ne "Idle")
                  {
                        #Set the variable to 1 to respresent at least one job is running
                        #Break out of the loop
                        $JobsExecuting = 1
                        break
                  }
            }
            if($JobsExecuting -eq 0)
            {
                  #Only if no jobs are running
                  write-host "No Jobs Running on $($srv.Name)"
                  #Enable tokens and save the change
                  $SQLAgent.ReplaceAlertTokensEnabled = $true
                  $SQLAgent.Alter()
                  Write-Host "Alerts Token Enabled on $($srv.Name)"
                  #Use the cluster cmdlets to stop and start the resource
                  get-cluster $srv.ComputerNamePhysicalNetBIOS | get-clusterresource | 
                  ?{$_.ResourceType.Name -eq "SQL Server Agent" -and $_.Name -eq "SQL Server Agent ($($srv.InstanceName))"} | 
                  stop-clusterresource | start-clusterresource -ErrorAction Stop
            }
            else
            {
                  #If there are jobs running, alert the client with what instance they were running - try again later
                  Write-Host "$($srv.Name) has running jobs, try again later" -ForegroundColor Red
            }

      }
}