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.
Introduction
Conclusion
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.
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.
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.
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
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 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.
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.
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®.
SQL Server Agent in SQL Server 2005 has three new subsystems:
SQL Server Integration Services Package
Analysis Query
Analysis Command
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 allows users to create job steps that submit MDX queries and stored procedure calls to an Analysis server computer.
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.
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.
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.
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
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.
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.
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.
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.
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.
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. |
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.
159 KB
Microsoft Word file