Jobs are SQL Server 7.0's answer to the repetitive work that database administrators do to maintain their systems. The descendants of SQL Server 6.x tasks, jobs can do more than tasks. A job isn’t a single-linear process task as is a Transact-SQL (T-SQL) command or CmdExec batch file. It’s a series of decision steps, more like a program. You can use jobs to create repeatable procedures so you can schedule work how and when you need it.
Only a SQL Server system administrators (sysadmin) or database owner (db_owner) can create jobs. The SQL Server Agent uses SQL Server to manage, schedule, and run jobs. You can run jobs from stored procedures, by alerts, from Enterprise Manager, or by creating applications that use SQL DMO objects. You construct jobs from active scripting programs, T-SQL, CmdExec, or Replication commands.
Jobs are useful for administrators who manage only a few databases because they offer more capabilities than tasks did, and jobs are useful in multiserver environments. But jobs truly excel in large corporate or multisite environments.
To demonstrate the usefulness of SQL Server 7.0 jobs, I’ll use the example of PlanetBank, a large corporation with hundreds of branches, each with a SQL Server database. The main office uploads and manages data daily. PlanetBank offers a credit card, telephone banking, life insurance, and other products and services, which all run on SQL Server databases. A large development group creates the many applications PlanetBank runs, and this group has its own development servers.
CREATING A JOB
You can use one of several methods to create a job. I’ll show you how to create a basic job, then I’ll show you how to use the that job.
Using the Create Job Wizard
The Create Job Wizard uses the standard Microsoft wizard format. It’s useful for basic work, but it lets you have only one step per job. You can use the wizard to create jobs, then later you can use Enterprise Manager later to customize the jobs.
To use the Create Job Wizard, open Enterprise Manager and select a server. From the tool bar, choose the wizard icon (a magic wand). You’ll see several wizard groups you can choose from. Choose the Management wizard group to open the set of database management wizards, then select Create Job. Then, choose Transact-SQL, Operating-System Shell Command, or Active Script Steps. You won’t see Replication among the choices because the wizard will let you create only one step and Replication requires more than one step. If you use this Wizard, the SQL Server Agent will default to the manual start setting. To change this setting, you need to open Control Panel/Services and select the automatic start setting.
Using Enterprise Manager
You use Enterprise Manager to do most of your jobs work. Open Enterprise Manager, then choose a server. Select SQL Server Agent, right-click Jobs, then New Jobs. You’ll see the New Job Properties dialog box. Select the General tab, then enter a name for the new job. There are defaults that use the multiserver administration format, Target server and Source server. A Target server receives a copy of the task from the Source server. As this example is not running on a master server, the Source field defaults to the local server. From the Owner list, select the owner responsible for running the job. (The owner is the person who created the job.) In the Description box, enter comments about the job. Then click the Steps tab and select New. You need to create at least one step for each job or you won’t be able to save the job.
Using T-SQL
To create a job using T-SQL, run sp_add_job to create a job; execute sp_add_jobstep to create one or more job steps; and run sp_add_jobschedule to create the job schedule. You can use this method to create a basic job, then use Enterprise Manager to customize the job. You can also use scripts to create jobs, but you'll need to test the scripts. For ad hoc jobs, use Enterprise Manager.
JOBS IN DEPTH
In this section, I’ll describe the properties of jobs. You can construct jobs to be as simple or complex as you choose. One complex characteristic of jobs is the nesting property. Jobs have attributes, one of which is the job steps that do the work. Job steps also have attributes. For clarity, I’ll describe the job attributes, leaving the job steps until last. Then I’ll use the PlanetBank example to explain the job steps attributes.
Job Categories
You use job categories to organize jobs into a coherent structure. If you correctly use the job categories feature, you can reduce the time you spend on job maintenance. SQL Server 7.0’s 12 built-in job categories include Web Assistant, Database Maintenance, Full Text, and a set of Replication categories. You cannot delete built-in categories. The default category for local jobs is Local \[Uncategorized(local)\]. On master servers, the default category for multiserver administration jobs is Uncategorized(multiserver). The multiserver category is not available on a target server. (I'll discuss master and target servers in an upcoming article, but briefly multiserver administration means you can run a copy of the same job across many servers without having a local copy. This means there needs to be only one source directory and you have much tighter change control. SQL Server forces you to have one Source server where the job actually resides. A target server receives a copy of any job that is flagged as multiserver from that Source server.) You can delete or edit jobs only with Enterprise Manager, but you can revise the category listing only within T-SQL.
You can create your own job categories tailored to your working environment, departments, job types, and actions. You use Enterprise Manager or T-SQL to create user-defined job categories. Then you can place jobs in a file type structure and manage them according to how you work. PlanetBank created job categories by function and named the categories credit card, insurance, business accounts, accounts, loans, etc. PlanetBank chose this scheme because most of the jobs in the system manage data for these functional groups. Because the work and structure of the jobs were different among the groups, PlanetBank also grouped the jobs by function. The database administrators use the Database Maintenance category for most server maintenance, and they created their own Testjobs category for that purpose. To add categories, open Enterprise Manager, select SQL Server Agent, and right-click. Select Job Categories to display the categories, then select Add. You can also use the sp_add_category command in T-SQL to add categories.
Job Properties
You use Enterprise Manager to define a job. The only mandatory attributes are job name and a job step. I’ll use as an example a housekeeping job that checks the data in the Customers database and dumps it for backup. I'll call the job ChecknDump.
Name is a mandatory attribute and is limited to 128 characters. Description is a general comment/description field of up to 512 characters. The target local server property defines a job as local or multiserver. You use the source property to specify the server the job originated on (this property defaults to local). A systems administrators (sa) can use Owner to specify the job owner. Other properties such as last modified self-explanatory and are shown in the example below. Screen 1 shows the PlanetBank job at creation. This is a local job because I didn’t define the server as a master server. I chose the Database Maintenance job category.
Job Scheduling
You can schedule jobs more flexibly with SQL Server 7.0 jobs than you can with SQL Server 6.x tasks. You can run jobs when the CPU is idle, on a regular schedule, or at a specific date and time. Although this is a small change from the way you schedule tasks in SQL Server 6.5, the CPU idle option in SQL Server 7.0 is useful for busy servers.However, if a long job starts in a temporary CPU lull, conflicts might occur with other operations when competing processes fight for CPU resources.
Let’s continue the example. PlanetBank decided to run ChecknDump daily on a main office server. In choosing when to run a job, you need to consider the database and server usage. In this example, the server holds the main customer database, is a live server and the customer database is a repository rather than directly used by all sources. The server does not operate 24 x 7, and the main processing load occurs within office hours. PlanetBank scheduled the ChecknDump job at 8:00 p.m. daily and named the job ChecknDump Customers. Screen 2 shows the notification dialog boxes and how to define a new job schedule.
Specifying Actions
You can specify actions that will execute according to the outcome of the job operation. This choice depends on the importance of the server, the database, and the job, and on the availability of people to manage the problem. Jobs can initiate actions to notify people (either by email, paging, or a net send message, make an event log entry, or automatically delete the job itself. Choosing a course of action depends on your company and its attitude toward data problems. For example, PlanetBank believes that this server and database are critical and that the ChecknDump job’s success is vital to the health of the data and the company. Screen 3 shows that the job notification process for the Check Customers step is to notify the DBA, Will Robinson, by email if the step succeeds. If the step fails, the job will page Dr. Zachary Smith so he can repair the data before the next day.
JOB STEPS
The job step is a single unit of action within a job. Job steps have different types and each type has slightly different properties. Each step is self-contained and doesn’t accept parameters produced from within the rest of the job, including files.
Types of Job Steps
The five types of job steps are stored procedures and extended stored procedures, T-SQL, command executables, replication, and active scripting languages such as JavaScript and VBScript.
If you create a T-SQL or stored procedure job step, you need to select the database in which you want the job to execute, then write the T-SQL command in the window or enter the stored procedure call.
This action includes opening a file and inserting the contents as in the ISQL load function \[what file, what contents, and what is the ISQL load function?\].
You run CmdExec steps just as you do SQL Server CmdExec programs, using Windows NT and SQL Server security. The programs can use the .cmd or .exe extensions. You must include a process exit code, and you must specify the file path.
Active scripting steps are JavaScript or VBScript scripts that you can load from a file or copy when you create this type of job step.
If you create a job step with a replication type, the step creates a publication with replication. SQL Server automatically creates replication jobs for the required replication agents. The replication type determines which agent is created. The agents that produce job steps are Distribution, Log Reader, Merge, and Snapshot. The first two run continuously when the SQL Server Agent is started. You can run the other agents on command or schedule them to run.
Controlling Flow
If you create a job with more than one step, you need to determine the relationship between the steps by defining what action occurs when each step succeeds or fails. This action lets you control the step’s flow. For example, if a step succeeds, the step can quit the job and report success, quit the job and report failure, or go to the next step or to a specific step identified by step id or step name. If the step fails, the step can quit the job and report failure, quit the job and report success, go to the next step, go to a specific step, or retry the step. You can specify the number of retries and the intervals between retry attempts.
Some job steps don’t depend on the status of a previous step. For example, each dump in a job that schedules multiple database dumps does not depend on the success or failure of a previous dump. If the step does require structure, however, you can use the On Success/Failure attribute to define the job step.
Step Properties
When you create job steps, you need to enter step properties. Screen 4 shows the Check Customers step PlanetBank created. Step name labels each step. The limit for step name is 100 characters and the name needs to be unique within the job. Type defines the step type. If you define the type as active scripting, then you need to specify the scripting language as the language attribute. The database property lists all databases on the server and lets you choose which server to run the step on. The command property, which has a 4000-character limit, lets you enter the command or script to execute the step. Parse checks the active scripting text for correct syntax.
You’ll see a different dialog box for each step type. If you choose the advanced tab for the T-SQL step, as Screen 5 shows, you can direct the step’s results to Output File, which stores the results of the single step. You can use Append File to add the step’s results to an existing file so you can track the job rather than the step functionality.
Screen 6 shows the completed PlanetBank’s ChecknDump job steps. Note the On Success/On Failure control, they enable the job to navigate through the steps. The database will not be backed up unless it is error free. Step ID is an integer that identifies the step within the job. The Dump Database step is a .bat file that dumps the database to a file for copying to another server. As you can see from the tabs it is very obvious that the steps are an attribute of a job. Although you can run scripts from within jobs and you can use CmdExec files, you can’t transfer steps between jobs.
You can use SQL Server 7.0 jobs to streamline database administration. You can create a job with a few steps that will verify data in a main office database with T-SQL, run branch data uploads with stored procedures, sort data and bcp archives with CmdExec, replicate information to branch servers, and amend a Web page to inform users of the change with active script.
The power and flexibility of SQL Server 7.0 jobs might revolutionize your task management procedures. In upcoming articles, I’ll show you how to manage and maintain jobs.
