Recently I had an issue come up where I needed to run about 15 SQL Agent Jobs in a specific order, bouncing back and forth across multiple SQL Servers, ensuring that each job completed successfully before starting the next job in the series. On top of that, I was going to have to perform this action multiple times a day, randomly, for several days. After doing this manually a couple of times, I realized that I would be much better off creating a PowerShell script to do this for me.
To lay out the scenario of what I was trying to accomplish, I had 15 jobs spread across 3 SQL Servers that needed to be called as follows:
- Server 1 – Job 1, Job 2, Job 3, Job 4, Job 5
- Server 2 – Job 6
- Server 1 – Job 7, Job 8
- Server 3 – Job 9, Job 10
- Server 1 – Job 11, Job 12, Job 13, Job 14, Job 15
To accomplish this, I wrote the following quick and dirty PowerShell script. The script allows you to pass in a server name and an array of job names, and it will connect to the specified SQL server and run the jobs in the order they are listed, waiting for each job to complete successfully before starting the next job. In the event a job fails (or completes with any status other than “succeeded”), the script will stop processing and not run any further jobs.
|
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 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Import-Module SQLPS -DisableNameChecking # create a function that takes a SQL Server name and array of SQL Jobs to be run on that server function invoke-sqljobs([string]$ServerName, [array]$JobList) { # Load SMO and instantiate the server object [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null; [Microsoft.SqlServer.Management.Smo.Server]$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName; #loop through the list of jobs foreach($i in $JobList) { #get the specified job [Microsoft.SqlServer.Management.Smo.Agent.Job]$job = ($sqlServer.JobServer.Jobs | ? { $_.Name -eq $i }); $job.Start(); $jobStartTime = Get-Date #wait a second to give the job time to get started; otherwise PowerShell will check the job status before it has chanaged Start-Sleep -s 1 Write-Host "$Job Started: $jobStartTime" $jobStatus = $job.CurrentRunStatus #start a loop that keeps refreshing and checking the job status until it becomes "idle"; once the satus becomes "idle" the job has completed #this ensures that each job completes before the next job is started. while($jobStatus -ne 'Idle') { $job.Refresh() #refresh the job connection $jobStatus = $job.CurrentRunStatus #check the job's status } $jobEndTime = Get-Date #check to see if the job succeeded $jobResult = $job.LastRunOutcome Write-Host "$Job Completed with a Status of $jobResult : $jobEndTime" #if the result is not "Succeeded" break out of the loop and do not process any more jobs if ($jobResult -ne 'Succeeded') {break} } } #call the jobs on each server, creating the JobList in the order the jobs need to run. invoke-sqljobs -ServerName 'Server1' -JobList @('Job 1','Job 2','Job 3','Job 4','Job 5') invoke-sqljobs -ServerName 'Server2' -JobList @('Job 6') invoke-sqljobs -ServerName 'Server1' -JobList @('Job 7','Job 8') invoke-sqljobs -ServerName 'Server3' -JobList @('Job 9','Job 10') invoke-sqljobs -ServerName 'Server1' -JobList @('Job 11','Job 12','Job 13','Job 14','Job 15') |
This script can probably be refined a little bit, with some error handling thrown in, but it is only being used temporarily and does what I need so I’m happy with it. I’m just running it locally from within my IDE so I just pull it up, click “go”, and come back in a half hour to check on it. Now there’s no need to have to call each job manually and wait for it to complete!
Thank you