Steps for SQL Server Agent-level 2nd: Job Steps and sub-series

Richard WAYMIRE,2017/10/11 (First edition: 2011/02/17)

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

The series

This article is part of the "Stairway series: Steps for SQL Server Agent".

SQL Server Agent is the core of any real-time database system. Many uses of proxies are not always obvious, so system knowledge is always useful for developers and DBAs. Richard Waymire provides a simple explanation for its many uses. A SQL Server Agent job consists of a series of one or more job steps. A job step is assigned to a specific job subsystem that identifies the kind of job that the job step is to perform. Each job step runs in a separate security context, although each job also has an owner to 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 Work

Thinking The best way to do SQL Server Agent jobs is to associate a set of other components that are required to perform a given task. The main components of the work are work steps, timetables, alarms and notifications.

when the job is created, the owner is assigned to the job. As described in Level 1, by default, the owner will be the user who created the job (in Transact-SQL, by sp_add_job system stored procedures or by using SQL Server Management Studio). Most features of SQL Server Agent assume that you are a member of the system Administrator role. If you are, you or any other member of the system Administrator role can modify the job after it is created. If you want a member of a non-system administrator role to be able to modify the job, you should change the job owner to the user's login name. Note that members of the system administrator can change any job, regardless of job ownership. Work Steps

as described in Level 1, SQL Server Agent jobs consist of at least one job step. When most people think about the work of a job, their true meaning to SQL Server is a work step. The job step is defined by the type of action you want to perform, and each job step is executed by one of the following job subsystems:? ActiveX? operating system (CMDEXEC)? Power housing? Various replication tasks? SQL Server Analysis Services (SSAS) command (that is, xml/a)? SQL Server Analysis Services (SSAS) query (MDX)? SQL Server Integration Services (SSIS) package (DTS package in SQL Server 2000)? The Transact-SQL script (T-SQL) lets us create a job and the necessary job steps to back up the primary database. Create a new job that I called my "Backup Master", shown in 1.

Figure 1-Create a new job

Now click Steps tab, review the job steps, then click New ... Open the New Job Step dialog box (Figure 2).

Figure 2-Create a new work step

I have completed the steps in this job to check the master database for integrity before starting the backup. I can do all the work in one step, but I want to show you the workflow between steps. I have given the name "Check master database Integrity" and set the type to Transact-SQL. The database context is primary. I enter a simple command in the text box: DBCC CHECKDB has several explanations for this conversation. the Type drop-down list is where you select the SQL Server Agent subsystem. The possible subsystems are described below. The Transact-SQL job step does not have a security proxy account, so the job step runs in the context of the job owner. The security proxy account enables job steps to run with different user security contexts and is typically used for jobs that are not members of the sysadmin role. the job subsystem that you select changes the contents of the rest of the dialog box. For Transact-SQL job steps, a simple text box is provided for you to type Transact-SQL, as I did here. Each work 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. A success action defines what happens when a 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, and this step is valid, then do the next step. If you click on the drop-down menu, you will see other options-including exiting the job (via a success or failure notification) or jumping to another step. Note that the last option jumps to another step until you are at least in the second step of the job before it is displayed.

Figure 3-New job step advanced options

If for some reason a step fails, you can choose the number of times that you can try to retry the job 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 job step could not be completed or ended with an error code). the option is the same as success, using a different default value (as you would expect). Because this is a Transact-SQL job step type, any output from the Transact-SQL command can be logged to the 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 a second step to the job. Assuming step 1 does not produce an error, the next job backs up the primary database. Here is the command of my system (4 shown):

BACKUP DATABASE [master] to DISK =

N ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\backup\master.bak '

With INIT

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

because this is the end of the job, click the Advanced tab, and then select the success action to exit job report succeeded. That's it-you should be able to click OK and view the completed job steps, 5.

Figure 5-work done

Now click OK to finish defining the job and run the job. when you click Run Job, note that because you are now asked multiple steps, which step do you want to start the job from. start the job (shown in 6) from the first step and observe the job flow when the first step runs successfully, then go to the second and last step of the job

Figure 6-Start the job in step (select Step 1)

once completed successfully, the job is marked as successful and exits (7).

Figure 7-Job has run successfully

Job Subsystem There are seven main working subsystems that you will use. This number does not include the replication subsystem because they are special cases, usually these job steps and jobs are created by the replication component, rather than by theCreated by the DBA. We will defer the discussion of the three Analysis Services subsystems because they have several unique considerations.Transact-SQL scripts (T-SQL)The Transact-SQL subsystem is very simple and may be the most common type of job you create. It allows you to run Transact-SQL (but not the SQLCMD extension of Transact-SQL) against a SQL Server local instance that is attached to SQL Server Agent. Note that unlike the Analysis Services subsystem, you can only connect to a local instance of SQL Server. Also note that Transact-SQL does not have an agent capability, so the job step for T-SQL always runs in the security context of the job owner.ActiveX ScriptsThe ActiveX subsystem allows you to run Vbscript,jscript or other custom scripting languages (theoretically). 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 handy subsystem, but starting with SQL Server 2008, this subsystem has been deprecated and you should use the PowerShell subsystem. Operating System (CmdExec)The CmdExec subsystem allows you to run operating system commands (just as you would open a command prompt). These commands will run in the security context of the SQL Server Agent service account. The key to remember here (and in the ActiveX subsystem) is that no user can tap or accept any prompts, so make sure the script runs without user intervention.Power HousingThe PowerShell subsystem allows you to run scripts that are compatible with Windows PowerShell 1.0 or 2.0. As with other scripting subsystems, the script will run by default in the security context of the SQL Server Agent service account. PowerShell is very powerful and you should carefully study the knowledge of the growth PowerShell. PowerShell will allow you to connect to the remote system, so this is a way to resolve the Transact-SQL subsystem's connection to the remote instance of the server.Next timeas you can see,The SQL Server Agent job step is the core of the job content. Many different subsystems are available and each subsystem has different capabilities. In the next step in this phase, I'll look at the agent capabilities to enhance the security of the job steps, as well as the other SQL Agent subsystems of Analysis Services.

Steps for SQL Server Agent-level 2nd: Job Steps and sub-series