Translation (14)--Ladder to SQL Server Agent-level two: job steps and subsystems

Translation (14)--Ladder to SQL Server Agent-level two: job steps and Subsystems

Richard WAYMIRE,2017/10/11 (first published: 2011/02/17)

Links: http://www.sqlservercentral.com/articles/SQL+Agent/Job+Steps+and+Subsystems/72268/

The series

This article is part of the Stair series: SQL Server Agent Ladder

SQL Server Agent is the core of any real-time database system. Proxies have many less obvious uses, so the knowledge of the system is always useful for developers and DBAs. Richard Waymire provides a simple explanation.

A SQL Server Agent job is made up of one or more job steps. A job step is assigned to a specific work subsystem, which determines the type of work that the work step will 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 focuses on the job steps and subsystems that make up the SQL Server Agent job.

Take a quick look back at work

The best way to think of SQL Server Agent jobs is to associate a container with a collection of other components that are required to perform a given task. The main components of the work are work steps, progress, alarms and notifications.

When a task is created, an owner is assigned to the task. As mentioned in level 1th, by default, the owner will be the user who created the work (through Transact-SQL, through the sp_add_job system stored procedure, or by using SQL Server Management Studio). Most features of SQL Server Agent assume that you are a member of the sysadmin server role. If you are, you or any other member of the sysadmin role can modify the work after the work is created. If you want a member of a non-sysadmin role to be able to modify the job, you should change the user's login name. Note that the sysadmin member can change any work, regardless of work ownership.

Steps to Work

As described in Level 1th, the SQL Server Agent job consists of at least one job step. When most people think of a job, what they really mean to SQL Server is the work steps. Job steps are defined by the type of action you want to perform, and each job step is executed by one of the following working subsystems:

Activex

Operating System (CMDEXEC)

Powershell

Various replication tasks

SQL Server Analysis Services (SSAS) commands (that is, xml/a)

SQL Server Analysis Services (SSAS) query (MDX)

SQL Server Integration Services (SSIS) package (DTS package in SQL Server 2000)

Transact-SQL scripts (T-SQL)

Let's create a job and the necessary job steps to back up the primary database. Create a new job, I'll call it "Backup Master", as shown in 1.

Figure 1-Create a new job

Now click the Steps tab to view the work steps and click New ... The new Job Step dialog box pops up (Figure 2).

Figure 2-Create a new work step

Before starting the backup, I have filled out the steps for this task to perform the integrity check of the primary database. I could have done all the work step-by-step, but I wanted you to know the workflow between the steps. I have given my step name, "Check the integrity of the primary database", and set the type to Transact-SQL. The database context is master. I added a simple command to the text box:

DBCC CHECKDB

For this dialogue, there are several explanations. The Type drop-down is where you select the SQL Server Agent subsystem. The possible subsystems are described below. There is no security proxy account for the Transact-SQL job step, so the job step runs in the context of the job owner. The security proxy account allows job steps to be run with a different user's security context and is typically used for jobs that are not members of the sysadmin role.

The job subsystem you select will change the contents of the rest of the dialog box. For Transact-SQL job steps, a simple text box is provided for you to enter Transact-SQL, as I did here. Each job step may have a different subsystem associated with it.

Next, click the Advanced tab of the new job step. You will see the screen shown in 3. The On success action defines what happens after the successful completion of the work step. The default option is to go to the next step, meaning if there are multiple steps in a job, and this works, perform the next step. If you click the drop-down menu, you'll see other options-including quitting work (getting a success or failure notification), or jumping to another step. Note the last option, skip to another step until you have at least the second step in the work to appear.

Figure 3-Advanced options for new work steps

If a step fails for any reason, you can choose how many times you can retry the work step. You can also specify a delay (in minutes) between each attempt to successfully complete a job step. The following is a failed operation (that is, the work step could not be completed or ended with an error code). The options are the same as the successful option, with different defaults (as you would expect).

Because this is a Transact-SQL job step type, you can record any output of a Transact-SQL command 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 then click New to add the 2nd step to the job. Assuming that the 1th step does not produce an error, the next job will return to the primary database. Here are the commands from my system (4):

BACKUP DATABASE [master] to  DISK =n ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\ Master.bak ' with INIT

Figure 4-second work step for backing up a primary database

Because this is the end of the job, click on the Advanced tab and select "Success action" as "Quit work report success". That's it--you should be able to click OK and see the complete job step, as shown in 5.

Figure 5-Work done

Now click OK to finish defining the job and run the job. When you click to run the job, be aware that because there are multiple steps, you can now ask which step you want to start working on. Run from the first step (shown in 6) and observe the process of the work, because the first step runs successfully, then moves to the second and last step of the job.

Figure 6-Starting work (select Step 1)

Upon successful completion, the job marks the success and exits (7).

Figure 7-Job has run successfully

Working subsystem

You will use 7 major working subsystems. This number does not include the replication subsystem because they are a special case, usually these work steps and jobs are created by the replication component, not by the DBA. We will defer the discussion of the three Analysis service subsystems to the next level, as they have several unique considerations.

Transact-SQL script (T-SQL)

The Transact-SQL subsystem is very simple and is probably the most common work step you will create. It allows you to run Transact-SQL (but not SQLCMD extended to Transact-SQL) against the local instance of SQL Server Agent. Note that, unlike the Analysis Services subsystem, you can only connect to a local SQL Server instance. Also note that Transact-SQL does not have a proxy capability, so T-SQL job steps are 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). By default, the script runs in the security context of the SQL Server Agent service account. If you are proficient in VBScript, which may be a handy subsystem, but the subsystem has been deprecated as SQL Server 2008, you should use the PowerShell subsystem.

Operating System (CMDEXEC)

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

PowerShell

The PowerShell subsystem allows you to run Windows PowerShell 1.0 or 2.0 compatible scripts. As with other scripting subsystems, the script runs in the security context of the SQL Server Agent service account by default. PowerShell is very powerful and you should take a hard look at what you know about PowerShell. PowerShell will allow you to connect to a remote system, so this is a way to bypass the Transact-SQL subsystem to connect to a remote instance of the server.

Next

As you can see, the SQL Server Agent job step is at the heart of the work content. There are many different subsystems to use, each of which provides different functions. In the next step of this staircase, I will check the agent functionality to enhance the security of the job steps and other SQL Agent subsystems for the profiling service.

Translation (14)--Ladder to SQL Server Agent-level two: job steps and subsystems