SQL Server Agent (2/12): Job Steps and subsystems

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

A SQL Server Agent job has a series of one or more job steps. A job step is assigned to a specific job subsystem to identify the kind of job step that will be performed. Each job step runs in its own security context, although each job has an owner who can decide who can modify the job. This article focuses on the job steps and subsystems that make up the SQL Server Agent job.

Quick review of assignments

The best way to think of SQL Server Agent jobs is to correlate a series of containers for the components that require tasks to be specified. The main components of a job are job steps, schedules, alerts, and notifications.

When the job is created, the owner is assigned to the job. As mentioned in the first article, the default owner would be the user who created the job (either by using T-SQL, by sp_add_job the system stored procedure, or by using SSMS). Most features of SQL Server Agent assume that you are a member of the sysadmin server role. If so, you or any other sysadmin server role member can modify the job once the job is created. If you want non-sysadmin server role members to be able to modify the job, then you should modify the user who is logged on by the job owner. Note the sysadmin member can modify any job, regardless of who owns the job.

Job Steps

as mentioned in the first article, the jobs in SQL Server Agent must have at least one job step. When most people want SQL Server Agent to do some work, what they really mean is the job step. The job steps are the types of actions you define that you want to make, including the operating subsystem that you can run:

• Activex
• Operating system (CMD run)
• PowerShell
• Various replication tasks
• SQL Server Analysis Services (SSAS) commands (for example, xml/a)
• SQL Server Analysis Services (SSAS) query (MDX)
• SQL Server Integration Services (SSIS) package (SQL Server 2000li de DTS package)
• T-SQL Script

Let's create a job that requires at least one job step to back up the Mater database. Create a new job, as illustrated in Figure 1, and I'll name it " backing up the master database ."

Create a new job with illustration

Now click on the "Steps" tab to view the job steps and click "New" to pop up the "New Job Step" dialog (Fig. 2).

Before we make a backup, I perform the integrity check of the master database in this job step. I can do all the work in one step, but here I want to show you the flow of work between the next steps. I fetch my job name is "Check the integrity of the Mater database" and the type is set to T-SQL. The database context is Mater, and I entered a simple command in the text box:

1 DBCC CHECKDB

Illustration 2--creating a new job step

Do some simple explanations for this dialog box. The drop-down box type is used to select the SQL Server Agent subsystem. The optional subsystems are explained below. There is no secure proxy account for the T-SQL job step, so the job step runs in the context of the job owner. The security agent account allows the job step to run in a different user security context, typically for jobs rather than members of the sysadmin group.

The job subsystem you select changes the rest of the dialog box. For T-SQL job steps, a simple text box will give you input to your T-SQL, as I did here. Each job step has a different subsystem associated with it.

Next, click "Advanced" in the job step. You will see the interface as illustrated in Figure 3. The steps to perform define what happens once the job step executes successfully. The default option is go to next, which means that if there are multiple job steps in the job, the next step is done. If you click the drop-down box, you'll see other options-including the existing job (a successful or failed reminder), or jump to another step. Note This last option, skip to another step, do not show, know you have a second step in the job.

Illustration 3--New job step advanced options

If the job fails for some reason, you can select the "Number of retries" for the job step. You can also specify the delay (in minutes) between each attempt to successfully complete a job step. The following are the failed actions (for example, the job could not be completed or ended in the error code). This option, like a successful operation, has a different default value (as you'd expect).

Because this is a T-SQL job step type, you can have command output from a T-SQL command record (just as with the-o parameter in the SQL command). You can also write the results to a table (the Sysjobsteps table in msdb).

Click "OK" and click "New" to add a 2nd job to the job. Assuming step 1 does not generate an error, this next job step backs up the master database. Here are the commands in my system:

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

Illustration 4--Step 2nd of backing up the master database

Because this is the end of the job, click "Advanced" and select "Action to perform successfully" as: exit the job that reported success. OK-You can click "OK" and the completed job steps are shown in Figure 5.

Illustration 5--the finished job.

Select Click OK to complete the job definition and run the job. When you click Run Job, note that because there are multiple steps, you will be asked from which step to start your ancestral. From the first step to run the job (as illustrated in Figure 6), onlookers under the job flow, the 1th step after the successful execution and then move to the 2nd step-the last step of the job.

Illustration 6--Job Start step selection (select 1th Step)

Once a successful completion, the job is marked as successful and exited (as illustrated in Figure 7)

Illustration 7--Job executed successfully

Job Subsystem

You will use 7 main operating subsystems. This number does not include the replication subsystem because they are special, and typically these job steps and jobs are created by the replication component rather than by the DBA. For the next article, we will delay the discussion of 3 analysis service subsystems because they have several unique considerations.

T-SQL script

The T-SQL subsystem is very straightforward and is the job step you create most often. It allows you to run T-SQL on a local SQL Server instance, and of course SQL Server Agent is also local and associated with it. Note Unlike the Analysis Services subsystem, you can only connect to a local SQL Server instance. For T-SQL, there is no proxy capability, so job steps for T-SQL are only run in the security context of the job owner.

ActiveX Scripts

The ActiveX subsystem allows you to run Vbscript,jscript, or other custom scripts (theoretically). The script is run by default in the security context of the SQL Server Agent service account. If you are good at Vbsccript, this is a handy subsystem, but this subsystem has been stripped from SQL Server 2008 and you should use PowerShell instead.

operating system (cmd command execution)

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

PowerShell

The PowerShell subsystem allows you to Windows PowerShell 1.0 or 2.0 compatible scripts. As with other scripting subsystems, toluene runs by default in the security context of the SQL Server Agent service account. PowerShell is very powerful and you should study and master it carefully. PowerShell allows you to connect to a remote system, so this is a limitation around the T-SQL subsystem that you can connect to a remote SQL Server instance.

Next Trailer

As you can see, the SQL Server Agent job step is at the heart of the job content. There are many different subsystems, each providing you with different abilities. In the next step of this series, I will check the agent capability of strengthening security on the job step, and the rest of the SQL Agent subsystem-the analysis service.

SQL Server Agent (2/12): Job steps and subsystems