Second SQL Server Agent job steps and subsystems

This article is the second of the SQL Server Agent series, please refer to the original text for more information.

The

SQL Server Agent job consists of a series of one or more job steps. A job step is assigned to a specific job subsystem (the work that determines the job step to complete). Each job step runs in a separate security context, although each job has an owner to determine who can modify the job. This article focuses on the job steps and subsystems that comprise SQL Server Agent.
Quick Review Job
The best way to understand SQL Server Agent jobs is to put the associated components that need to complete a given task in one container. The most important components of a job are job steps, schedules, warnings, and notifications.
When a job is created, an owner is assigned to the job. As mentioned in the first article, the user who creates the job defaults to the owner of the job, whether created through the sp_add_job system stored procedure or through SSMS. In most cases, SQL Server Agent assumes that you are a member of the sysadmin server role. If you are, then you or any other sysadmin role member can modify the job. If you want a member of a non-sysadmin role to be able to modify the job, then you should change the owner of the job into the corresponding user. Note System Administrator members can change any job, regardless of the job owner.
job step
A SQL Server Agent job contains at least one step, as described in the first article. When most people say that homework can do some work, it actually means a job step. The job steps are defined according to the requirements action, and each job step is performed by the following subsystems:
->activex script
, operating system (CMDEXEC)
->powershell
, copy series Tasks
- >sql Server Analysis Services (SSAS) command
->sql Server Analysis Services (SSAS) query
->sql Server integration S Ervices (SSIS) package
->transact-sql script (T-SQL)
Let's create a job, plus the job step used to back up the master database. Create a new job as shown in-backup master,2.1.
Figure 2.1 Create a new job
Click New in steps, see the New Job Step dialog box (Figure 2.2)

Figure 2.2 Creating a job step
I have filled out the steps for this job to perform an integrity check before we back up the master database. I could have done everything in one step, but to tell you the workflow between the steps. I named this step "check master Database Integrity" and set the type to T-SQL, the database is Mater, the command text box DBCC CHECKDB
Some explanations for this dialog box. In the Type drop-down list, you can select SQL Server Agent subsystem. The subsystem is described below. Job steps that choose a Transact-SQL type do not have a security proxy account, so the job step runs in the context of the job owner. The security proxy account enables a job step to run a different user's security context, typically for a job owner that is a non-sysadmin role member.
The operating subsystem that you choose will change the rest of the conversation. For Transact-SQL job steps, a simple text box provides the type of SQL statement you want. Each job step may have a different subsystem associated with it.
Next, in the job step properties, click the Advanced tab. You will see the dialog box shown in Figure 2.3. The action to perform upon success (defines what happens when the job step completes successfully). The default option is to go to the next step, which means that if there are multiple steps in the job, this is done and the next steps are performed. If you click on the drop-down menu, you will see other options – including exiting the job (regardless of success or failure notification), or jumping to another step. Note that the last option jumps to another step and does not appear until there are at least two steps in your job.

Figure 2.3 Job step advanced options
If a step fails for any reason, you can set the number of job step retries. You can also specify each retry interval (in minutes). The following are the actions to take when a failure occurs (for example, a work step cannot be completed or an error code appears). The drop-down options are the same as when they were successful, but the default options are different (as you would expect).
Because this is an optional Transact-SQL type of job step, you can output an output file (just like in the Sqlcmd-o option) with one of the commands. You can also record the results to a table (sysjobstepslogs in msdb).
Click OK, and then click Add a second step. If no error is generated in step 1, the next job step backs up the master database. This is my backup command (shown in 2.4):
Figure 2.4 A second job step
Because this is the end of the job, click the Advanced tab and select the successful action quit report successful job. You can click OK to see the completed work shown in step 2.5
Figure 2.5 Completing the job
Now click OK to finish creating the job and run the job. When you click Run job, because the job has multiple steps, a dialog box will pop up to let you choose which step to start the job from. Starting the job from the first step (shown in 2.6), observe that the workflow moves to the second/last step of the job after the first step is successful.
Figure 2.6 Starting a job (select Step 1)
Once successfully completed, the job is marked as successful and then exited.
Job Subsystem
There are seven main operating subsystems that we will use. This number does not include the replication subsystem as a special case for them, typically these jobs are created by the replication component rather than by the DBA. We will postpone the next section to discuss three Analysis Services subsystems because they have some unique issues.
Transact-SQL scripts (T-SQL)
The Transact-SQL subsystem is very straightforward and may be the most common type in a job step. It allows you to run the Transact-SQL (but not sqlcmd extension) processing on the local instance associated with this SQL Server Agent instance. It is important to note that unlike the Analysis Services subsystem, you can only connect to a local SQL Server instance. Also note that T-SQL does not have a proxy capability, so the T-SQL job step always runs in the security context of the job owner.
ActiveX Scripts
The ActiveX subsystem allows you to run Vbscript,jscript, or other custom scripting languages (in theory). The script will run by default in the security context of the SQL Server Agent service account. If you are proficient in VBScript, this may be a useful subsystem, but in SQL Server 2008 it is no longer recommended, you should use the PowerShell subsystem instead.
operating System (CMDEXEC)
The CmdExec subsystem can execute operating system commands (if you have a command prompt open). The command will run in the security context of the SQL Server Agent service account. The key points to note here (including the ActiveX subsystem) is that no user can tap or accept any prompts, so make sure your script will be in no user intervention.
PowerShell
The PowerShell subsystem allows you to run Windows PowerShell 1.0 or 2.0 compatible scripts. As with other scripting subsystems, scripts run by default in the security context of the SQL Server Agent service account. PowerShell is incredibly powerful and you should seriously study to grow your PowerShell knowledge. PowerShell will allow you to connect to the remote system, so this is a way to circumvent the restrictions on the Transact-SQL subsystem connecting to a remote instance of the server.
Next Article
As you can see, the SQL Server Agent job step is the core of the job. Many different subsystems are available, each giving you different functions. In the next article, I'll check the agent capability on the job step to improve security, as well as the remaining Analysis Services subsystem.

Second SQL Server Agent job steps and subsystems