SQL Server Agent jobs are made up of a series of one or more Job Steps.  A job step is assigned to a specific job subsystem, which identifies the kind of work the job step is going to perform.  Each job step runs in a separate security context, although each job also has an owner that determines who can modify the job.    This article will focus on the job steps and subsystems that make up a SQL Server Agent job.

A Quick Review of Jobs

The best way to think of a SQL Server Agent job is as a container that associates a set of other components that are needed to perform a given task.  The primary components of a job are job steps, schedules, alerts, and notifications.

When a job is created, an owner is assigned to the job.  As mentioned in Level 1, by default the owner will be the user that is creating the job (either in Transact-SQL, via the sp_add_job system stored procedure or using the SQL Server Management Studio).  Most of the facilities of SQL Server Agent assume that you are a sysadmin server role member.  If you are, then you or any other sysadmin role member can modify the job once it’s created.  If you want a non-sysadmin role member to be able to modify the job, then you should change the job owner to be that user’s login.  Note that a sysadmin member can change any job, regardless of job ownership.

Job Steps

As mentioned in Level 1, a SQL Server Agent job is made up of at least one job step. When most people think of a job performing some work, what they really mean with SQL Server is a job step. A job step is defined by the type of action you wish to perform, and each job step will be executed by one of the following job subsystems:

•         ActiveX
•         Operating System (CMDExec)
•         Powershell
•         A variety of replication tasks
•         SQL Server Analysis Services (SSAS) Command (i.e. XML/A)
•         SQL Server Analysis Services (SSAS) Query (MDX)
•         SQL Server Integration Services (SSIS) Package (DTS Package in SQL Server 2000)
•         Transact-SQL Script (T-SQL)

Let’s create a job, and the necessary job steps, to back up the Master database.  Create a new job, I called mine “Backup Master” as shown in Figure 1.

Figure 1 – Creating a new job

Now click on the Steps tab to view the job steps, and click New… to bring up the New Job Step dialog (Figure 2).

Figure 2 – creating a new job step

I’ve filled out the steps in this job to perform an integrity check of the master database before we begin a backup.  I could have done everything in one step, but want to show you job workflow between steps.  I’ve given my step the name, “Check DB Integrity of Master”, and set the type to Transact-SQL.  The database context is master.  I put a simple command in the text box:

DBCC CHECKDB

A few explanations are in order for this dialog.  The Type drop-down is where the SQL Server Agent subsystem is selected.  The possible subsystems will be described below.  There are no security proxy accounts for Transact-SQL job steps, so the job step will run in the context of the job owner.  Security proxy accounts enable a job step to run with a different user’s security context, and are typically used for jobs not owned by sysadmin role members.

The job subsystem that you select will change the contents of the rest of the dialog.  For a Transact-SQL job step, a simple text box is supplied for you to type in your Transact-SQL, as I’ve done here.  Each job step may have a different subsystem associated with it.

Next, click on the Advanced tab for the new job step.  You’ll see the screen shown in Figure 3.  The On success action defines what happens once the job step is completed successfully.  The default option is to Go to the next step, meaning if there are multiple steps in a job, and this one worked, execute the next step.  If you click on the drop-down, you’ll see the other options – including exiting the job (with either a success or failure notification), or to jump around to another step.  Note that this last option, jumping to another step, won’t show up until you’re working on at least your second step in a job.

Figure 3 – New Job Step Advanced Options

If a step fails for some reason, you can choose the number of attempts that can be made to retry the job step.  You can also specify a delay (in minutes) between each attempt to successfully complete the job step. Below that is the failure actions (i.e. the job step is unable to be completed or ends in an error code).  The options are the same as on success, with a different default (as you might expect).

Because this is a Transact-SQL job step type, you can have any output from the Transact-SQL commands be logged to an output file  (just like the –o option in sqlcmd).  You can also log the results to a table (the sysjobstepslogs table in MSDB).

Click OK, and click New to add a second step to the job.  Assuming step 1 did not generate an error, this next job step back up the master database.  Here’s the command from my system (as shown in Figure 4):

BACKUP DATABASE [master] TO  DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\master.bak'
WITH INIT

Figure 4 – The second job step to backup the master database

Since this is the end of the job, click on the Advanced tab and select the On success action as “Quit the job reporting success.” That’s it – you should be able to click OK, and see the completed job steps as shown in Figure 5.

Figure 5 – the completed job

Now click OK to finish defining the job, and run the job.  When you click to run the job, notice because there are multiple steps you are now asked which step you’d like to start the job with.  Run the job from the first step (as shown in Figure 6), and watch the flow of the job as the first step runs successfully and then moves to the second and final step of the job.

Figure 6 – Start Job at Step (select step 1)

Once that completes successfully, the job is marked successful and exits (as shown in Figure 7).

Figure 7 – The job has run successfully

Job Subsystems

There are seven primary job subsystems that you will use.  This number does not include the replication subsystems as they’re a special case and typically these job steps and jobs are created by the replication components rather than by the DBA.  We’ll defer the discussion of the three Analysis Services subsystems for the next level, as they have several unique considerations.

Transact-SQL Script (T-SQL)

The Transact-SQL subsystem is pretty straight-forward and will likely be the most common type of job step you’ll create.  It allows you to run Transact-SQL (but NOT sqlcmd extensions to Transact-SQL) against the local instance of SQL Server that this instance of SQL Server Agent is affiliated with.  Note that unlike the Analysis Services subsystems, you can only connect to the local SQL Server instance.  Also note that there is no proxy capability for Transact-SQL, so the job steps for T-SQL always run in the security context of the job owner.

ActiveX Script

The ActiveX subsystem allows you to run VBScript, Jscript, or other custom scripting languages (in theory). The script will by default be run in the security context of the SQL Server Agent service account.  If you are well versed in VBScript this could be a handy subsystem, but the subsystem has been deprecated as of SQL Server 2008 and you should use the PowerShell subsystem instead.

Operating System (CmdExec)

The CmdExec subsystem allows you to run operating system commands (as if you had a command prompt open). The commands will be run in the security context of the SQL Server Agent service account.  The key thing to keep in mind here (and in the ActiveX subsystem) is that there is no user able to click or accept any prompts, so make sure your script will run with no user intervention.

PowerShell

The PowerShell subsystem allows you to run Windows PowerShell 1.0 or 2.0 compatible scripts.  As with the other script subsystems, the script will by default be run in the security context of the SQL Server Agent service account.  PowerShell is incredibly powerful and you should seriously investigate growing your knowledge of PowerShell.  PowerShell will allow you to connect to remote systems, so this is one way around the restrictions of the Transact-SQL subsystem to connect to remote instances of SQL Server.

Next Time

As you can see, SQL Server Agent job steps are the heart of the matter for job content.  A number of different subsystems are available, each giving you different capabilities.  In the next step in this Stairway, I’ll examine the proxy capabilities to enhance security on job steps, as well as the rest of the SQL Agent subsystems for Analysis Services.