What's New in SQL Server Agent for Microsoft SQL Server 2005

By Rob Walters

Summary: This document provides an overview of new features for the SQL Server Agent job scheduling service for Microsoft® SQL Server™ 2005.

On This Page

Introduction
Conclusion

Introduction

This paper summarizes the significant changes made to the SQL Server Agent feature in Microsoft® SQL Server™ 2005, and gives you a jump-start into the new features and capabilities in this release. For more information about the topics discussed in this paper, see SQL Server Books Online.

Security Improvements

After the initial installation of SQL Server, only the System Administrator (sysadmin) role has access to view, modify, create, and execute SQL Server Agent jobs. The Agent node in SQL Object Explorer is available only to users who are assigned the System Administrator role. To grant a user access to SQL Server Agent, you must add the user to one of the two new database roles in the msdb database.

New Roles in the msdb Database

SQL Server Agent in SQL Server 2005 adds three new roles to the msdb database:

  • SQLAgentUserRole   Users added to the SQLAgentUserRole role will have the same SQL Server Agent experience as they had in SQL Server 2000. These users can create jobs and manage only jobs that they created.

  • SQLAgentReaderRole   Users added to the SQLAgentReaderRole role will have the same privledges as those in the SQLAgentUserRole with the addition of the ability to enumerate and view the history of all jobs..

  • SQLAgentOperatorRole   Users added to the SQLAgentOperator role will have the same privledges as those in the SQLAgentReaderRole with the addition of the ability to execute local jobs that they do not own.

Multiple Proxy Accounts

In SQL Server 2000, when a user wanted to execute a SQL Server Integration Services (SSIS) package as a job step, the sysadmin had to enable the SQL Server Agent proxy account. After the account was enabled, any SQL Server Agent user could create job steps for certain subsystems, such as CmdExec, and run them by using this account. In SQL Server 2005, SQL Server Agent addresses this security concern by using multiple proxy accounts.

By default, in the new release of SQL Server Agent, only sysadmins can create job steps of the following types:

  • Active Scripting

  • SQL Server Integration Services Package

  • Analysis Command

  • Analysis Query

  • All replication subsystems

Transact-SQL subsystems will execute under the owner just as they did in SQL Server 2000.

To grant users other than sysadmins the right to create a job step for types other than Transact-SQL, a sysadmin must create at least one proxy account for the user. This proxy account is simply a credential with a friendly name.

Credentials must be created before Agent proxy accounts can be created. To create a credential, use the following script:

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = '<domain>\<user>', 
   secret = '<password>'

Note: Although the example stated in this paper use T-SQL to create credentials and proxy accounts, SQL Server Management Studio provides a series of dialogs to aid in the creation of these objects.

To add a proxy account, use the following script:

Sp_add_proxy @proxy_name='My Proxy', @credential_name='MyCredential'

After a proxy account is created, the sysadmin can assign one or more SQL logins, msdb roles, or server roles access to this account (Figure 1).

Figure 1   SQL login and msdb role assigned to My Proxy account

Figure 1   SQL login and msdb role assigned to My Proxy account

To assign a SQL login to the proxy account, use the following script:

Sp_grant_login_to_proxy @login_name='Jeff Price', @proxy_name='My Proxy'

Note: In this example, it is assumed that the SQL login 'Jeff Price' is also a database user in the msdb database and is a member of the SQLAgentUserRole role in msdb.

The sysadmin can then assign which subsystems will be accessible to this proxy account. In this example, after the sysadmin assigns the subsystems, Jeff Price and the users in the Maintenance User Group in msdb can create SQL Server Agent job steps of the SSIS type, to be executed by using the "My Proxy" proxy account (Figure 2).

Figure 2   Proxy account assigned to DTS subsystem

Figure 2   Proxy account assigned to SSIS subsystem

To assign a SSIS subsystem to the proxy account, use the following script:

Sp_grant_proxy_to_subsystem @proxy_name='My Proxy', @subsystem_name='SSIS'

Note: You can assign proxy accounts to zero or more subsystems. Sysadmins can revoke subsystems and users from proxy accounts by using sp_revoke_proxy_from_subsystem and sp_revoke_login_from_proxy, respectively.

If you upgrade to SQL Server 2005, the existing proxy account is created and all subsystems are assigned to this single proxy account. This enables your SQL Server Agent jobs to continue to function as they did in SQL Server 2000 after the upgrade. However, it is important that after the upgrade the sysadmin verifies the proxy account setup. The sysadmin should remove users who do not require access to the proxy account, and should create additional proxy accounts to limit access and remove the possibility of elevation of privilege.

Performance Improvements

Thread Life

In SQL Server 2000, an entry in the registry determined how many threads of a subsystem could be active at one time. This subsystem registry key located under the subtree HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\SQLServerAgent\Subsystems still exists; however, in SQL Server 2005, instead of terminating the thread at the end of every job step, the thread goes back to SQL Server Agent and determines if there is another job step of the same type waiting to be executed. If there is, the thread executes this job step. If not, the thread terminates itself. This change allows SQL Server Agent in SQL Server 2005 to execute more jobs more efficiently.

Performance Counters

SQL Server Agent in SQL Server 2005 offers four new performance counters:

  • SQLAgent:Alerts   Allows the user to monitor how many alerts have been raised since the start of the SQL Server Agent service, and how many alerts are raised each minute.

  • SQLAgent:Jobs   Allows the user to monitor information such as the job success rate and the number of active jobs. This counter also tells if the job was started on demand, by an alert, or by a schedule.

  • SQLAgent:JobSteps   Allows the user to monitor information such as how many job steps are waiting to be executed and how many time a specific step was been retried.

  • SQLAgent:Statistics   Allows the user to monitor how many times the Agent service restarted SQL Server service.

You can access these objects through the generic performance monitoring utility provided by Microsoft Windows®.

New Features

New SQL Server Agent Subsystems

SQL Server Agent in SQL Server 2005 has three new subsystems:

  • SQL Server Integration Services Package

  • Analysis Query

  • Analysis Command

The SQL Server Integration Services Subsystem

The SSIS subsystem allows users to create job steps that execute SSIS packages. Users can use all the parameters that are defined in the DTExec command prompt application as parameters in SSIS job steps.

Note: To execute an encrypted SSIS package, the user of the proxy account must have the same credentials as the Windows user who created the package. Packages that are not encrypted should execute without this limitation.

The Analysis Query Subsystem

The Analysis Query subsystem allows users to create job steps that submit MDX queries and stored procedure calls to an Analysis server computer.

The Analysis Command Subsystem

The Analysis Command subsystem allows users to create job steps that submit XML for Analysis statements to an Analysis server computer.

Note: Most management dialog boxes support scripting their actions as XML for Analysis.

Shared Schedules

SQL Server Agent in SQL Server 2005 allows the user to create a single schedule and assign one or more jobs to use this schedule.

Note: Jobs and schedules must be owned by the same user to be assigned to each other.

WMI Event Alerts

SQL Server 2005 supports raising WMI events for various actions that occur inside the database. In this release, you can configure SQL Server Agent to raise alerts after the occurrence of these WMI events. SQL Server Agent responds to SQL Server WMI events, as well as to WMI events raised by the operating system and other WMI providers.

Note: Microsoft does not support SQL Agent responses to remote WMI events. However, an experienced user can enable support for remote WMI events by adding the DWORD registry entry AlertAllowWMIRemoteEvents set to 1 under the HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent subkey, and then restarting the SQL Server Agent Service.

Caution   Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data.

Example of WMI Event Alert

A SQL Server Agent operator called "My Agent Operator" is defined on the system. My Agent Operator is configured to use the net send command to send messages to the workstation called "jeffprice-yukon" (Example 1).

Note: If you use net send as a notification method, verify that the Messenger service is running on the server that is hosting SQL Server Agent, or you will not receive the net send notification.

USE [msdb] 
GO 
EXEC msdb.dbo.sp_add_alert @name=N'Alert when database is created', 
   @enabled=1, 
   @delay_between_responses=0, 
   @include_event_description_in=4, 
   @wmi_namespace=N'\\.\root\Microsoft\SqlServer 
      \ServerEvents\MSSQLSERVER', 
   @wmi_query=N'SELECT * FROM CREATE_DATABASE""' 
  
GO 
USE [msdb] 
GO 
exec sp_add_notification @alert_name=N'Alert when database is created' 
   ,@operator_name=N'My Agent Operator' 
   ,@notification_method=4

In the previous code example, there are two new parameters under sp_add_alert: wmi_namespace and wmi_query. Wmi_namespace is the WMI namespace that SQL Server Agent uses to look for the event.

Note: SQL Server 2005 supports only WMI events that are raised on the local server.

Wmi_query is the WQL query that SQL Server Agent uses to identify the specific event that you want SQL Server Agent to respond to.

After you create the alert based on the preceding code, create a new database. Within 30 seconds, you will see a message that states that the "Alert when database is created" alert has been sent (Figure 3).

Figure 3   The "Alert when database is created" alert

Figure 3   The "Alert when database is created" alert

You can modify the time delay by changing the default value of 30 seconds in the SQL Server Agent registry key located under the subtree HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\SQLServerAgent.

The following keys affect WMI events in SQL Server Agent:

EventWMILimit

REG_DWORD

Default=5

EventWMIPeekInterval

REG_DWORD

Default=30

EventWMIPeekInterval tells SQL Server Agent how often to look for WMI events. EventWMILimit tells SQL Server Agent how many events to consume every time it checks the WMI event queue.

Tokens

In earlier versions of SQL Server, SQL Server Agent had the capability to parse and replace strings at run time in job step commands, output files, and alerts by using tokens. SQL Server Agent in SQL Server 2005 has changed the syntax used in tokens from "[X]" to "$(X)" where X is the token name. As a result, token names no longer conflict with existing database objects. In addition to the syntax change, SQL Server Agent added the WMI token, which allows users to send the information from a WMI event.

Example WMI Event Alert

The following code creates a SQL Server Agent alert based on the CREATE_DATABASE WMI event that SQL Server raises. This alert runs a job that dynamically creates a new job that backs up the newly created database.

First, create a SQL Server Agent job called "Automatically create a backup job." This job:

  • Creates a new job whose name is based on the source database property supplied by the WMI event.

  • Adds a single Transact-SQL step to back up the database to a predefined backup device; in this example, "MyBackups."

    Note: You must create a backup device called "MyBackups" for this script to work successfully.

    use msdb 
    
    exec sp_add_job @job_name=N'Automatically create a backup job' 
    GO 
    exec sp_add_jobstep @job_name=N'Automatically create a backup job', 
       @subsystem=N'TSQL', 
       @database_name=N'MSDB', 
       @on_success_action=3, 
       @step_name=N'Create Backup Job', 
       @command=N'sp_add_job @job_name=N''Backup $(WMI(DatabaseName))''' 
    GO 
    exec sp_add_jobstep @job_name=N'Automatically create a backup job', 
       @subsystem=N'TSQL', 
       @database_name=N'MSDB', 
       @on_success_action=3, 
       @step_name=N'Add Backup Step', 
       @command=N'sp_add_jobstep @job_name=N''Backup 
    $(WMI(DatabaseName))'' 
       ,@step_name=N''Issue Backup Command'' 
       ,@database_name=N''MSDB'' 
       ,@command=N''BACKUP DATABASE $(WMI(DatabaseName)) TO MyBackups''' 
    GO 
    exec sp_add_jobstep @job_name=N'Automatically create a backup job', 
       @subsystem=N'TSQL', 
       @step_name=N'Give job a jobserver', 
       @database_name=N'MSDB', 
       @command=N'sp_add_jobserver @job_name=N''Backup $(WMI(DatabaseName))''' 
    GO 
    
    exec sp_add_jobserver @job_name=N'Automatically create a backup job' 
    GO
    

Next, add an alert based on the CREATE_DATABASE WMI event raised by the SQL Server Service Manager.

EXEC msdb.dbo.sp_add_alert @name=N'Alert when database is created', 
   @job_name=N'Automatically create a backup job', 
   @wmi_namespace=N'\\.\root\Microsoft\SqlServer 
      \ServerEvents\MSSQLSERVER', 
   @wmi_query=N'SELECT * FROM CREATE_DATABASE ' 
GO

After this script runs, create a new database called "HR_Dev." Within the default WMI Peek interval (30 seconds), you should see a new SQL Server Agent job called "Backup HR_Dev." Backup HR_Dev can now back up the HR_Dev database. Another option is to automatically add a schedule to Backup HR_Dev.

SQL Server Agent Sessions

Each time the SQL Server Agent Service starts, it creates a new session that is assigned a session number. This session number is an incremental integer and is stored in the syssessions table in msdb. Performing a query (select * from msdb..syssessions) on the syssessions table reveals the results shown in Table 1.

Table 1   Syssessions table enumeration

Session_id

Agent_start_date

1

2004-03-16 15:51:04.897

2

2004-03-18 16:12:52.770

3

2004-03-19 15:01:22.677

Table 1 shows that the SQL Server Agent Service was started three times, most recently on March 19, 2004 at 15:01.

SQL Server Agent also provides a new stored procedure for enumerating real-time job activity based on the current session. Performing the query sp_help_jobactivity reveals a list of all active SQL Server Agent jobs (Table 2).

Note: The jobs shown in Table 2 are scheduled to run everyday at 15:00.

Table 2   Partial enumeration of sp_help_jobactivity

session_id

job_name

next_scheduled_run_date

...

...

...

3

Full Backup HR DB

2004-03-20 15:00:00

 

 

 

3

OLAP DB Prep

2004-03-20 15:00:00

 

 

 

This new stored procedure for enumerating real-time job activity obtains some data from the sysjobactivity table in msdb. The sysjobactivity table is where SQL Server Agent stores job status information for the current session and all previous sessions of SQL Server Agent.

If the SQL Server Agent Service fails unexpectedly, users can determine which SQL Server Agent jobs were in the middle of being executed by looking that the sysjobactivity table for the previous session. Table 3 shows some of the fields in the sysjobactivity table.

Table 3   Partial enumeration of the sysjobactivity table

session_id

job_name

run_requested_date

start_execution_date

stop_execution_date

next_scheduled_run_date

1

Full Backup HR DB

2004-03-16 15:00:04

2004-03-16 15:00:07

2004-03-16 15:04:23

2004-03-16 15:00:00

1

OLAP DB Prep

2004-03-16 15:00:02

2004-03-16 15:00:09

2004-03-16 15:04:01

2004-03-16 15:00:00

2

Full Backup HR DB

2004-03-19 15:00:03

2004-03-19 15:00:07

NULL

2004-03-19 15:00:00

2

OLAP DB Prep

2004-03-19 15:00:02

2004-03-19 15:00:06

NULL

2004-03-19 15:00:00

3

Full Backup HR DB

NULL

NULL

NULL

2004-03-20 15:00:00

3

OLAP DB Prep

NULL

NULL

NULL

2004-03-20 15:00:00

According to Table 3, the Full Backup HR DB and OLAP DB Prep jobs failed to complete because there are no entries in the stop_execution_date column.

The run_requested date states the time that the SQL Server Agent Service was notified by its scheduler that it is time to run the job.

The start_execution_date states the time that the SQL Server Agent Service started to run the job.

The stop_execution_date states that time that the SQL Server Agent Service completed running the job.

The next_scheduled_run_date is calculated after the job is run.

Database Mail Support

SQL Server Agent in SQL Server 2005 uses the new SMTP-based Database Mail. To enable Agent to use Database Mail, run the Database Mail Configuration. After you complete this wizard, go to the Alerts tab in SQL Server Agent properties to define the Database Mail profile that SQL Server Agent will use when sending mail. There is no need to define a MAPI profile when using Database Mail.

Note: Changing the mail system setting in SQL Server Agent requires starting the service for changes to become effective.

Stored Procedure Changes

Most of the new stored procedures in SQL Server Agent address the new shared schedule feature. Table 4 briefly describes each new stored procedure.

Table 4   New stored procedures

Stored procedure

Description

sp_add_schedule

Creates a schedule that can be used by any number of jobs.

sp_update_schedule

Changes the settings for a SQL Server Agent schedule.

sp_delete_schedule

Deletes a schedule.

sp_attach_schedule

Sets a schedule for a job.

sp_detach_schedule

Removes an association between a schedule and a job.

sp_help_schedule

Lists information about schedules.

Sp_help_jobactivity

Lists information about the run-time state of SQL Server Agent jobs.

Sp_notify_operator

Sends an e-mail message to an operator by using Database Mail.

Sp_add_proxy

Adds the specified SQL Server Agent proxy.

Sp_delete_proxy

Removes the specified proxy.

Sp_update_proxy

Changes the properties of an existing proxy.

Sp_help_proxy

Lists information for one or more proxies.

Sp_grant_proxy_to_subsystem

Grants a proxy access to a subsystem.

Sp_grant_login_to_proxy

Grants a security principal access to a proxy.

Sp_revoke_login_from_proxy

Removes access to a proxy from a security principal.

Sp_revoke_proxy_from_subsystem

Revokes access to a subsystem from a proxy.

Sp_enum_proxy_for_subsystem

Lists proxy accounts with access to specified subsystem.

Sp_enum_login_for_proxy

Lists logins with access to specified proxy account.

Sp_help_jobcount

Provides the number of jobs that a schedule is attached to.

Sp_help_jobs_in_schedule

Returns information about the jobs that a particular schedule is attached to.

Sp_cycle_agent_errorlog

Closes the current SQL Server Agent error log file and cycles the SQL Server Agent error log extension numbers (produces the same results as restarting the server).

Sp_purge_jobhistory

Removes job history.

The @oldest_date parameter was added to this stored procedure. Now you can delete all of the SQL Server Agent history for a particular SQL Server Agent job, as well as call the stored procedure to delete all of the history for that job if it's older than the date specified.

Conclusion

SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service. In addition, performance enhancements allow more job throughput than any other version of SQL Server Agent. SQL Server Agent now natively supports job steps that use other SQL Server features like Analysis Services and SQL Server Integration Services. It is important to note that this paper has only touched on the key enhancements to SQL Server Agent for SQL Server 2005. There are additional features and functionality that are not documented in this paper.

Download

Download

Whats New In SQL Agent.doc

159 KB
Microsoft Word file

Get Office File Viewers