Jobs

SQL Server Agent has a robust scheduling system that can execute programs, SQL, and ActiveX scripts. You can execute items as often as every minute of every day,
or have events execute a single time. For instance, jobs are perfect for rebuilding an index during off-peak hours so you don’t have to worry about performance levels.

SQL Server can only execute one instance of the job at a time. If multiple requests are made to execute the job, only the first request is taken. Any additional attempts to start a job that is already started also result in the following SQLServerAgent error:

Error 22022: SQLServerAgent Error : Request to run job  JobName (from User bknight) refused because the job  is already running from a request by User swest.

Creating Jobs

You can view the jobs on your server in Enterprise Manager under the Management group by navigating to SQL Server Agent and Jobs. Only users who are signed into Enterprise Manager as sysadmins can view and update all the jobs. Otherwise, users can only view and modify the jobs they created. We’ll talk about how to lock down your job system later in this chapter.

To create a new job, right-click the Jobs group and select New Job. This opens the New Job Properties dialog box shown in Figure 4-2.

click to expand
Figure 4-2: Use the New Job Properties dialog box to configure a job.

General Tab of the Jobs Properties Dialog Box

The job name must be unique. You can use the Category option to assign your job a category for logical grouping, which makes it easier to find the job if you have a heavily utilized job system. (See the section “Job Categories” later in this chapter for more information.)

The number-one error that people make when they’re creating a job is to misconfigure the owner. The owner is an extremely important component of the job equation. When the job is executed, it uses the security context of the owner. (There are exceptions to this, which I cover later in this section.) You are only allowed to “impersonate” a different user if you are a member of the sysadmin role. The Target Local Server option is covered in the section “Centralized Administration” later in this chapter.

Tip 

I prefer to keep the owner of the job SA or a Windows NT group name like DOMAIN\DBAs, which has sysadmin rights. I try to keep this as generic as possible due to employee turnover.

Steps Tab of the Jobs Properties Dialog Box

A step contains the actual commands the job executes. You must have at least one step per job. Select the New button to add a new step to your job. The steps you add manually (replication automatically adds others) can be any of the following:

  • ActiveX script A script written in any Windows scripting language installed on the server. By default, you can run any VBScript or JScript. You can also run additional mail components here, such as CDONTS.

  • Operating system command Any command-line program or OS command. SQL Server uses xp_cmdshell to execute these programs. Make sure you always fully qualify all paths, even if a path is set at the system level.

  • T-SQL script Any T-SQL script.

Keep in mind that the ActiveX Script and Operating System Command options will fail if the user is not a sysadmin. If you want to allow non-sysadmin users access to execute these, you can go to the Job System tab in the SQL Server Agent Properties and deselect the restricting option. If the restricting option prevails when a non- sysadmin tries to execute an operating system command, the job fails with the following error being placed in the job history:

Non-sysadmins have been denied permission to run CmdExec  job steps. The step failed.

Each step option has its own advanced option that can be viewed on the Advanced tab. For example, with the T-SQL option, you can request that the results of the query be output to a text file. You can also specify what occurs when the step succeeds or fails. After a step completes, you can set the job to:

  • Proceed to the next step

  • Quit the job and report success

  • Quit the job and report failure

After you finish adding the steps, you are brought back to the Steps tab where you can use the Start Step drop-down box to set which of the job’s steps will execute first.

Schedules Tab of the Jobs Properties Dialog Box

In the Schedules tab, you can specify how often you want the job to execute. You can create different levels of schedules. For example, you can create one schedule for the weekend and one for weekdays. You can also set the job to execute in response to an alert being triggered. (More information about alerts appears in Chapter 5.)

Note 

SQL Server Agent’s job system does not recognize holidays and exceptions. If you want to create scheduling exceptions, you need an enterprise scheduling solution for Windows.

Notifications Tab of the Jobs Properties Dialog Box

In the Notifications tab, you can specify which operator receives a message on completion, failure, or success of the job’s execution. You can also set an event to be written to the Windows NT Application Log whenever the job executes. Lastly, you can configure the job so it is deleted whenever it executes. This is nice when a server is too busy to run a large query or process during production hours. In that case, you could schedule the job to run during off-peak hours, and on success, delete itself. You can specify if the job will delete itself only on failure, success, or completion.

Job Categories

Job categories are a handy way of grouping jobs into logical divisions. To access a list of categories in Enterprise Manager, right-click the Jobs group in the left pane, and select All Tasks | Manage Job Categories.

Note 

Only members of the sysadmin group can manage categories.

Add a new category by clicking the Add button and specifying a name. If you want to assign jobs to your new category, simply click the Show All Jobs button and check the jobs you want to assign.

You can also reassign jobs after they’re created by highlighting the category and selecting Properties. Move the job to the new category using the same method as adding a new category. You can delete a category by selecting it and clicking the Delete button.

You can only delete user-defined categories. Once deleted, any jobs that were in the category are reassigned to the Uncategorized (local) category.

Tip 

In case you need to script this for a disaster recovery (DR) environment, you can use the sp_add_category stored procedure.

Debugging Jobs

You have several ways to debug a job when it fails. The best way to debug a problem with a job is through job history. You can access the history of a job after its first execution by right-clicking the job and selecting View Job History.

By default, the Job History screen in Enterprise Manager only shows the history of the job. Select the Show Step Detail checkbox to view the history of the individual steps. By viewing the individual steps, you can determine where in the process the job actually failed and received an error message.

Another debug tactic is to add logging to your steps. With the T-SQL and operating system command step types, you can output the results of the step to a log file. You can also add steps in your job to include RAISERROR T-SQL to report the status if a step fails.

If you need to debug an ActiveX script, you can use DTS to execute an ActiveX script’s task to receive a more detailed error. DTS and its logging mechanisms are covered later in this chapter in the section “Administering DTS.”

Scripting Jobs

As with operators, you can reverse-engineer a job to a T-SQL script by right-clicking the job and selecting All Tasks | Generate SQL Script. You also have the ability to script out all the jobs by clicking on the Jobs group in Enterprise Manager, and selecting All Tasks | Generate SQL Script.

Locking Down Jobs

When I worked for an Internet service provider, we had several leveraged SQL Servers for all of our clients to use and we didn’t allow our clients to create jobs unless they had explicit rights to do so. By default in SQL Server, however, anyone can create a job. To accomplish this type of lockdown of the job system, we had to revoke a lot of the default access to SQL Server. We then created a role called JobUsers in the msdb database that had explicit rights to create and see jobs.

Caution 

Test this lockdown script in all of your environments before deploying. You never know what types of dependencies you’ll have in your application.

The following code shows you what rights you can revoke from the Public role to accomplish this:

Print 'Revoking access to create and read jobs' USE MSDB REVOKE EXECUTE on  sp_help_job to Public REVOKE EXECUTE on  sp_help_jobstep to Public REVOKE EXECUTE on  sp_help_jobschedule to Public REVOKE EXECUTE on  sp_add_jobserver to Public REVOKE EXECUTE on  sp_add_job to Public REVOKE EXECUTE on  sp_update_job to Public REVOKE EXECUTE on  sp_add_jobstep to Public REVOKE EXECUTE on  sp_delete_job to Public REVOKE EXECUTE on  sp_purge_jobhistory to Public REVOKE EXECUTE on  sp_help_jobhistory to Public REVOKE EXECUTE on  sp_delete_jobserver to Public REVOKE EXECUTE on  sp_help_jobserver to Public REVOKE EXECUTE on  sp_get_jobstep_db_username to Public REVOKE EXECUTE on  sp_update_jobstep to Public REVOKE EXECUTE on  sp_delete_jobstep to Public REVOKE EXECUTE on  sp_add_jobschedule to Public REVOKE EXECUTE on  sp_update_jobschedule to Public REVOKE EXECUTE on  sp_delete_jobschedule to Public REVOKE EXECUTE on  sp_get_job_alerts to Public REVOKE EXECUTE on  sp_start_job to Public REVOKE EXECUTE on  sp_stop_job to Public REVOKE EXECUTE on  sp_check_for_owned_jobs to Public REVOKE EXECUTE on  sp_check_for_owned_jobsteps to Public

You will then want to create a role called JobUsers in the msdb database and assign it the rights that you just removed from the Public role as shown here:

USE MSDB IF (SELECT COUNT(*) FROM SYSUSERS WHERE NAME = ' JobUsers ') = 0 BEGIN       EXEC SP_ADDROLE 'JobUsers'       Print 'Adding JobUsers Role' END GO GRANT EXECUTE on  sp_help_job to JobUsers GRANT EXECUTE on  sp_help_jobstep to JobUsers GRANT EXECUTE on  sp_help_jobschedule to JobUsers GRANT EXECUTE on  sp_add_jobserver to JobUsers GRANT EXECUTE on  sp_add_job to JobUsers GRANT EXECUTE on  sp_update_job to JobUsers GRANT EXECUTE on  sp_add_jobstep to JobUsers GRANT EXECUTE on  sp_delete_job to JobUsers GRANT EXECUTE on  sp_purge_jobhistory to JobUsers GRANT EXECUTE on  sp_help_jobhistory to JobUsers GRANT EXECUTE on  sp_delete_jobserver to JobUsers GRANT EXECUTE on  sp_help_jobserver to JobUsers GRANT EXECUTE on  sp_get_jobstep_db_username to JobUsers GRANT EXECUTE on  sp_update_jobstep to JobUsers GRANT EXECUTE on  sp_delete_jobstep to JobUsers GRANT EXECUTE on  sp_add_jobschedule to JobUsers GRANT EXECUTE on  sp_update_jobschedule to JobUsers GRANT EXECUTE on  sp_delete_jobschedule to JobUsers GRANT EXECUTE on  sp_get_job_alerts to JobUsers GRANT EXECUTE on  sp_start_job to JobUsers GRANT EXECUTE on  sp_stop_job to JobUsers GRANT EXECUTE on  sp_check_for_owned_jobs to JobUsers GRANT EXECUTE on  sp_check_for_owned_jobsteps to JobUsers Print 'Lockdown complete!'

Now that you’ve locked down the job system, this will prevent users from seeing jobs in Enterprise Manager or by running T-SQL. If there are any users or groups you want to be able to create jobs, you would need to add them into the msdb database and grant them rights to the JobUsers role.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net