|
|
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.
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.
Figure 4-2: Use the New Job Properties dialog box to configure a job.
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. |
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.
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. |
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 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. |
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.”
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.
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.
|
|