Use PowerShell to Create Agent Jobs

Whenever I set up a new SQL server, there are a number of tasks that have to be done before it's ready for service, and the process can become really tedious if I have to step through the wizards in SQL Server Management Studio (SSMS) to get them done. The key thing is that using the graphical interface, while good for newcomers, can easily lead to inconsistent configurations, because it's easy to click the wrong checkbox or make inadvertent changes to your standard settings when stepping through the pages of the wizard.

After loading the SQLPS module, like I did in "Gather SQL Server Instance Information with PowerShell," I'll set a variable to equal the name of the target instance.

$inst = 'WS12SQL'

In my scripts, I'll normally pass this in via a command-line argument. Once I have the instance name, I can connect to that instance.

$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

The SQL Server Agent objects are part of the JobServer object, under the Server object in the SMO hierarchy. Within the JobServer object is defined collections for Jobs, Alerts, and Schedules. I can now create a new Agent job by creating a Job object, specifying the server's JobServer object as one parameter and the name of the job as the other.

$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($svr.JobServer, 'StartPerfCapt')

Once the Job object is created I can define its properties and create it.

$j.Description = 'Start Perfmon data capture'$j.OwnerLoginName = 'sa'$j.Create()

Since a job can't do anything without steps, I create a JobStep object using the Job and the name of the step as parameters, then define the properties for the step. The step I'm creating is going to use the PowerShell task type, so the Command property gets the PowerShell command line. This job is going to run a script that gathers perfmon data, and has three parameters:

Related:PowerShell Dialog Boxes: Using Button Click Actions and Menu Selections

Because I already have the target instance in a variable, I can use that variable in the command text by using double quotes (") when I define it. This is a feature of PowerShell—when you use double quotes to define a string, any embedded variables are replaced with their current value. If you use single quotes (') to define that variable, variable substitution isn't performed.

$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j, 'Step 01')$js.SubSystem = 'PowerShell'$js.Command = "c:Scriptsgetperf.ps1 '$inst' 60 '2199-12-31 23:59:59'"$js.OnSuccessAction = 'QuitWithSuccess'$js.OnFailAction = 'QuitWithFailure'$js.Create()

Once the JobStep object is created, I need to grab the ID of that step so I can set the job to start with that step. I also need to tell the job to run the job on my target server, so I set those properties and alter the job.

$jsid = $js.ID$j.ApplyToTargetServer($s.Name)$j.StartStepID = $jsid$j.Alter()

The final step is to create a JobSchedule object for the job, again using the Job and the schedule name for parameters. I set the FrequencyTypes property to AutoStart because I want this job to be started whenever SQL Server Agent starts up, and I set the ActiveStartDate property to the current date/time, so it'll be automatically run the next time SQL Server Agent is started.

$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j, 'Sched 01')$jsch.FrequencyTypes = 'AutoStart'$jsch.ActiveStartDate = get-date$jsch.Create()

By putting these steps into a script, I have an easy way to make sure that I'm always capturing my performance data on any server I set up automatically.

Check out more PowerShell articles by Allen White.