SQL Server Agent - SQL Server 2008

Picture this scenario: you’ve been recently hired by a startup company as the only database administrator (DBA). After helping out the developers with their table schema designs, you retreat to your desk and contemplate how to maintain the database in the long term. Naturally, the need for a backup solution pops into your mind, and you decide to perform a full database backup at midnight, followed by a differential backup 12 hours later at noon. Since the database is mostly read-only, you imagine this solution to be best. But you then ask yourself, “How can a backup database command be issued at midnight?” Although this is a startup, and hopefully one day you’ll be relaxing on aMaui beach with a nice cold glass of fermented wheat and hops in your hand, you realize that sitting around until midnight every night isn’t that appealing. Here is where SQL Server Agent might help you.

SQL Server Agent is aWindows service that runs continuously. Through defining and scheduling actions called jobs, Agent can automatically perform work against your SQL Server system. SQL Server Agent can also be used for a variety of other purposes, including to alert you of any performance events, such as the occurrence of any deadlocks.

As an example, let’s walk through how you would create a backup job and schedule it to run at midnight. You can create SQL Server Agent jobs via stored procedures or by using SQL Server Management Studio. For this example, we will use Management Studio.

Enabling SQL Server Agent

Once you connect to the SQL Server database engine, you’ll notice one of three different states of the SQL Server Agent node in Object Explorer. First, if you don’t see the Agent node there at all, you probably are not a member of the sysadmin role, nor are you granted any specific access to Agent. See the “Permissions for Executing Agent Jobs” section later in this chapter for more information about how to gain access to SQL Server Agent. The second state that Agent could be in is the off state. If you perform a default installation of SQL Server and don’t explicitly tell the setup to start SQL Server Agent, you’ll see “Agent XPs disabled” next to the SQL Server Agent node in Object Explorer.

In SQL Server, a large effort was placed on restricting the surface area for security-related attacks. One of the features that was implemented was the ability to disable execution of extended stored procedures (XPs) like xp_cmdshell, xp_sendmail, and in this case, xp_sqlagent_notify. These XPs are logically grouped inside SQL Server.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE

The result set returns about 60 different global configuration settings. Although most of these settings aren’t related to the enabling and disabling of XPs, if you look through this list, you’ll see “Agent XPs” listed. When the value is 1, Agent-specific XPs, like xp_sqlagent_notify, as well as other procedures, like those found in Server Management Objects (SMO) calls, will be enabled.

At this point, you might be wondering whether you need to manually go to the Query Editor and issue a call to enable Agent XPs when you want your SQL Agent to work. Thankfully, this isn’t the case. When the SQL Server Agent service is started, it will automatically enable the Agent XPs, and when it’s stopped, it will automatically disable the Agent XPs. Note that this is the only time these XP groupings will be automatically enabled and disabled. For normal use of SQL Agent, you’ll never need to worry about manually changing this setting.

If you see “Agent XPs disabled” in Object Explorer, right-click it and select Start. The Agent service will automatically enable the Agent XPs, and you can now use SQL Server Agent. When SQL Server Agent is started and the Agent XPs group is enabled, Object Explorer will show the Agent node as enabled. At this point, you’re ready to create your Agent job.

Creating the Agent Job

Once you’re connected to SQL Server and have started the SQL Server Agent service (if necessary), expand the SQL Server Agent node. Your screen should appear as shown in Figure.

SQL Server Agent node in Object Explorer

SQL Server Agent node in Object Explorer

To create a job, right-click the Jobs node and select New Job. This will launch the New Job dialog box, as shown in Figure.

General tab in the New Job dialog box

General tab in the New Job dialog box

The General tab allows you to enter some metadata about the job, such as its name, its description, and the owner of the job.

Referring to our original backup scenario, let’s give it an appropriate name like FullDBBackup. Once you’ve given the job a name, you can proceed to add steps to this job. Jobs can have one or more steps to them, and each step can be of one or more of the following types: T-SQL, ActiveX Script, Operating System (CmdExec), Replication (there are actually five replication subsystems, but for the most part, these are configured using wizards and dialog boxes, and users usually don’t manually create replication job steps), SQL Server Analysis Services Command and Query, and SQL Server Integration Services (SSIS) Package. Since jobs don’t need to contain the same job step types, it’s possible to have a job that first executes some T-SQL against SQL Server, then runs an SSIS package, and finally processes an Analysis Services cube. In this example, all we need is a single T-SQL job step. When you click the Steps tab, you’re presented with a grid listing the steps within the job.

This tab allows you to add, remove, and edit job steps as well as define which job step will be executed first. Since our example has only one job step—the backup of the database itself—the first step will be the starting step. When you add or edit a step, you’re presented with the New Job Step dialog box.

Steps tab in the New Job dialog box

Steps tab in the New Job dialog box

General tab in New Job Step dialog box

General tab in New Job Step dialog box

Every job step requires a unique name. Once we give this particular step a name, we can add the T-SQL script for backing up the database as the command.

After you’ve added the job step(s), it’s time to define the schedule. In our example, we want the full database backup to occur every day at midnight. In the New Job dialog box, click the Schedules tab. You’ll see another grid that lists all the schedules that will execute your job. Figure shows how the grid looks for our database backup job. Once we’ve defined a schedule and clicked OK on the New Job dialog box, our database backup job is now ready for automatic execution.

Grid displayed on Schedules tab in New Job dialog box

Grid displayed on Schedules tab in New Job dialog box

As you can see from this example, SQL Server Agent provides an easy-to-use solution for routine maintenance and monitoring of SQL Server. The following sections will dive into the key features in SQL Server 2008 and provide you with potential issues related to the design and limitations to consider.

Permissions for Executing Agent Jobs

SQL Server database administrators will be pleased to note that on a default installation of SQL Server 2008, only members of the sysadmin role can create, manage, or execute SQL Server Agent jobs. Ordinary SQL users won’t even see the SQL Server Agent node in the Object Explorer tree when connected to a SQL Server instance. If a user isn’t a sysadmin, in order to use SQL Server Agent, that user will need to be added to one of three predefined database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

Each of these roles provides additional privileges within SQL Server Agent, starting with the most restrictive role, SQLAgentUserRole, followed by the SQLAgentReaderRole, and finally the least restrictive role, SQLAgent OperatorRole. The following tables list these new roles and the actions they can perform on Agent objects, starting with Table, which describes user role access for alert objects.

SQL Server Agent Role Access for Alert Objects

SQL Server Agent Role Access for Alert Objects

SQL Server Agent alerts read events generated by SQL Server and from the Windows application log. Alerts can be configured so that, based on certain criteria being met, an action will be automatically performed. These actions can be either starting an Agent job or notifying an Operator. An Operator is a SQL Agent-specific object that is basically an arbitrary name, such as BryanTheDBA, and has at least one of the following pieces of information defined: e-mail address, pager address, or net send address. An Operator can be defined on a job to be notified of job failure, or, in the case of an alert, when the alert criteria are met.

Table lists user role access for Operator objects.

SQL Server Agent Role Access for Operator Objects

SQL Server Agent Role Access for Operator Objects

Jobs are what define the work to do in SQL Server Agent. Jobs can be run locally on the same server where SQL Server Agent is installed or remotely. When a job is set to run remotely, the sysadmin must configure one server to be the master (SQL Server Books Online documentation refers to this as the MSX server) and source of the jobs, and the rest of the servers will be targets (also known as TSX servers). Tables list role access based on local or remote jobs.

SQL Server Agent Role Access for Job Objects (Local Jobs Specifically)

SQL Server Agent Role Access for Job Objects (Local Jobs Specifically)

SQL Server Agent Role Access for Job Objects (Local Jobs Specifically)

SQL Server Agent Role Access for Job Objects (Multiserver Jobs Specifically)

SQL Server Agent Role Access for Job Objects (Multiserver Jobs Specifically)

As mentioned previously, schedules can be shared among jobs that are owned by the same user. Even sysadmins can’t mix and match schedules from different users; all job and schedule “pairs” must have the same owner. Table 8-5 lists the role access for schedule objects.

SQL Server Agent User Role Access for Schedule Objects

SQL Server Agent User Role Access for Schedule Objects

Proxy Accounts

When creating a SQL Server Agent job (as described previously), as a sysadmin, you’re free to add job steps of any type. For all job step types except T-SQL, when the job step is run, it’s executed under the context of the SQL Server Agent service account. T-SQL job steps are a special case: they are always run under the job owner. So given this information, what if you wanted your developers who aren’t sysadmins on your server to be able to schedule and execute SSIS packages? In that case, you would want to create a proxy account in SQL Server Agent for the developers.

A proxy account is a friendly name and aWindows credential that is stored in SQL Server. (A proxy account in SQL Server Agent contains a valid Windows credential.) Table lists the user role access for proxy objects.

SQL Server Agent Role Access for Proxy Objects

SQL Server Agent Role Access for Proxy Objects

The sysadmin can explicitly grant permission to use a particular proxy account to individual SQL Server logins, msdb database roles, and/or system roles. Also, this proxy can be assigned to one or more job-step types (sometimes referred to as Agent subsystems). Additionally, the sysadmin can define multiple proxy accounts. Each one of these proxy accounts can be used in one or more subsystems.

For example, say the administrator creates a proxy account called SSISDevProxy. The administrator then assigns this new proxy account to the SSIS subsystem. He grants the SQL login, Tammie, the right to use this new proxy account. At this point, the next time Tammie logs in to SQL Server, she will be able to create a SQL Agent job with a job step of type SQL Server Integration Services Package. Under the Run As combo box, she will have the option to select SSISDevProxy as the proxy account to use when SQL Agent executes this SSIS package.

When this Agent job is executed, the Agent service will impersonate the credentials of SSISDevProxy and proceed to execute the package. In this example, there was a one-to-one-to-one relationship between user, proxy, and subsystem. However, SQL Server Agent supports a manyto- many-to-many relationship; that is, the administrator can set up many logins to many proxies to many subsystems. Figure 8-6 depicts the possibilities of defining proxy accounts. If we set up SQL Server Agent as represented in this figure, the experiences for the three SQL logins Tammie, Gary, and George.

  • When Tammie creates an Agent job with a SQL Server Integration Services Package job-step type, she will be able to select only SSISDevProxy as a proxy to use.
  • When Gary creates an Agent job with a SQL Server Integration Services Package job-step type, he can select between SSISDevProxy and SSISTestProxy because the system administrator has given him access to both of these proxies assigned to the SSIS subsystem.
  • Proxy accounts themselves don’t need to be assigned to just one subsystem. When George creates an Agent job with an Operating System (CmdExec) job-step type, he can select the LimitedPrivilegedProxy for use as the proxy account. However, if he created a job step of type ActiveX Script, he could also select this proxy because the system administrator had assigned this proxy to both the ActiveX Script and Operating System (CmdExec) subsystems.

Multiple proxy example

Multiple proxy example

The intent of the multiple proxy account feature in SQL Server Agent is to provide flexible, secure access to the Agent features.

System administrators can create proxy accounts through Object Explorer in SQL Server ManagementStudio or through T-SQL code. Here, we’ll walk through a T-SQL example of creating a proxy account. However, if you prefer using a GUI, remember that Management Studio includes a tool that makes iteasier to create and manage proxy accounts.

First, create a credential in the SQL Server secure store. This credential is the actual Windows identity that the SQL Server Agent service will impersonate before it executes the job step.

CREATE CREDENTIAL [DevCredential]
WITH IDENTITY='<<DomainUsername>>',
SECRET='<<password>>'

Next, create the Agent proxy account that references this credential:

USE MSDB
GO
sp_add_proxy @proxy_name = 'SSISDevProxy',
@enabled = 1,
@description = 'proxy account used by developers to test their SSIS packages',
@credential_name = 'DevCredential'

At this point, the proxy account is neither assigned to any subsystems nor accessible by anyone except members of the sysadmin role. Assign the proxy account to the SSIS subsystem:

sp_grant_proxy_to_subsystem
@proxy_name = N'SSISDevProxy',
@subsystem_name = N'SSIS'

Finally, grant the SQL Server login Tammie the ability to use this new proxy account,:

sp_grant_login_to_proxy
@login_name = N'Tammie',
@proxy_name = N'SSISDevProxy'

Now SQL Server login Tammie will be able to create a new job step of type SQL Server Integration Services Package and select SSISDevProxy as the proxy account under which to run when her package is executed.

Job Schedule Sharing

SQL Server Agent job schedules can be shared among jobs that are owned by the same user. For example, suppose you have a series of jobs that you would like to run every day at midnight. In previous versions of SQL Server, you would create a job and then create a separate schedule for each job, each executing at the same schedule time. In SQL Server 2008, you can create a single schedule (in this case, occurring every day at midnight), and attach it to one or more jobs, provided you are the owner of the jobs.

suppose that, using Management Studio, Greg creates a job, Job1, with a schedule, Schedule1. Since he was told to run jobs only starting at the time defined in Schedule1 by his system administrator, Greg wants to create his second job, called Job2, with this same schedule. Greg launches the New Job dialog box in Management Studio and, after supplying the name, clicks the Schedules tab and is presented with the dialog box.

Schedules tab of the New Job dialog box

Schedules tab of the New Job dialog box

If Greg wanted to create a separate schedule for this job, he would simply click the New button below the grid and be presented with the New Job Schedule dialog box. However, since he wants to reuse the schedule he already created for Job1, he clicks the Pick button and is presented with the dialog box shown in Figure.

Picking an existing schedule through the Pick Schedule for Job dialog box

Picking an existing schedule through the Pick Schedule for Job dialog box

At this point, Greg can view all the jobs that are assigned to a schedule by clicking the number in the Jobs in Schedule column. Clicking this link pops up the dialog box .

Dialog box showing jobs that are referencing a particular schedule

Dialog box showing jobs that are referencing a particular schedule

Greg can now confirm that this is the schedule he wants to reuse, as well as view the other jobs that also reference this schedule.

SQL Server Management Studio also provides a separate dialog box launched from the Jobs container node called Manage Schedules.

Manage Schedules dialog box

Manage Schedules dialog box

From this dialog box, users can see all the schedules they created and find out which other jobs reference these schedules. Users will be able to see only schedules and jobs that they own. System administrators, however, will be able to see all schedules and jobs that exist in SQL Server Agent.

Logging Agent Job-Step Output

SQL Server Agent prohibits users who are not sysadmins from logging their job-step output to the file system. However, sysadmin users can continue to use this feature. As a replacement, both nonsysadmin and sysadmin users can log job-step output to a well-known table, sysjobstepslogs, in the msdb database. Logging job-step output is available only on these specific job step types: T-SQL, CmdExec, Analysis Services Command, and Analysis Services Query.

Logging to the table is an option that can be set through the Advanced tab of the New Job Step dialog box in SQL Server Management Studio. This tab also allows you to view the log using Notepad. Alternatively, you could use the new sp_help_jobsteplog stored procedure to view this log. Just pass the job ID or job name to this stored procedure as follows:

USE msdb
GO
sp_help_jobsteplog @job_name='FullDBBackup'
GO

WMI Events and Agent Alerts

Tokens are character placeholders within T-SQL code. The use of tokens within SQL Server Agent job steps has been around in previous versions of SQL Server. At runtime, SQL Agent replaces these tokens with values such as the current date or the computer name.

SQL Agent responds to WMI event notifications raised by SQL Server 2008, as well as any other WMI event provider (one restriction is that events must be raised from the local server on which the Agent resides). This token is called out by the following:

$(WMI(X))

where X is the desired WMI property of the WMI event that the user wishes to insert.

SQL Server 2008 event notifications can raise WMI events from various Data Definition Language (DDL) and Data Manipulation Language (DML) statements. The following example shows how the administrator can now raise an Agent alert when someone creates a new database.

First, let’s create an Agent Operator:

EXEC msdb.dbo.sp_add_operator @name=N'MyAgentOperator',
@enabled=1,
@pager_days=0,
@netsend_address=N'robs_laptop'
GO

Since we supplied the netsend_address parameter, this Operator should be notified of the database creation via net send. Note that the Messenger service must be started in order for the Agent to send network messages.

Next, we’ll create an Agent alert using built-in system stored procedures called sp_add_alert and sp_add_notification. This code will notify an Operator if anyone issues a CREATE DATABASE statement to SQL Server.

EXEC msdb.dbo.sp_add_alert @name=N'Create_Database_Alert',
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@wmi_namespace=N'.rootMicrosoft SqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * FROM CREATE_DATABASE'
GO
EXEC msdb.dbo.sp_add_notification
@alert_name=N'Create_Database_Alert',
@operator_name=N'MyAgentOperator',
@notification_method = 4
GO

In order for SQL Server Agent to raise WMI event-based alerts, Service Broker must be enabled for msdb. If the previous example resulted in an error, try executing the following command:

IF(SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb')=1
ALTER DATABASE msdb SET ENABLE_BROKER
GO

The sp_add_alert stored procedure has two optional parameters:

  • @wmi_namespace is the namespace of the desired WMI event object. If you were to create this alert through the user interface provided by Management Studio, the default path of .rootMicrosoftSqlServerServerEventsMSSQLSERVER is already in place for you. You may have noticed that even though the SQL Server event namespace is prepopulated, there is nothing stopping you from replacing it with any other WMI event provider. Thus, it’s possible to raise Agent events on things like free disk space and all the other Win32 event providers installed on your server.
  • @wmi_query is the actual WMI Query Language (WQL) query that the Agent will issue when looking for this event. In this example, we’re interested in the CREATE_DATABASE event that is raised by SQL Server, so we entered SELECT * FROM CREATE_DATABASE. Although this WQL resembles T-SQL, it isn’t the same.

Once the preceding script has been executed, the Operator will be notified via a net send alert whenever a new database is created on the server.

Net send alert notification

Net send alert notification

Agent Performance Counters

The SQL Server Agent service has a set of performance counters installed on the server. Four performance objects exist for each instance of SQL Server Agent that is installed on the server:

  • SQLAgent:Alerts: This performance object provides information such as number of alerts activated since the Agent service started and how many alerts are raised within the last minute.
  • SQLAgent:Jobs: This performance object describes a variety of job states, such as number of active jobs, how many jobs are queued, and the job success/failure rate as a percentage of successful/failed jobs from to the total number of executed jobs.
  • SQLAgent:JobSteps: This performance object can determine the active and queued job steps per subsystem, in addition to the total number of step retries.
  • SQLAgent:Statistics: Perhaps one of the more simple performance objects, this one has just one counter, Server Restarted, and describes how many times the SQL Server service was restarted during the life of the current Agent service process.

Although not an exhaustive list of everything inside the Agent service, these counters should be a starting point in monitoring the Agent service activity through Performance Monitor. Most of the instances for these performance objects are self-explanatory, except for the Jobs object. When you select the Jobs object, you’ll have the option to pick all counters or specific ones.

Add Counters dialog box in Performance Montior

Add Counters dialog box in Performance Montior

In the case of the job instances, Alerts means jobs that were started by an alert, Schedules means jobs that were started by a schedule, Others means jobs that were started manually via sp_start_job, and _Total means jobs that were started by any one of these.

Agent Upgrade

If you’re upgrading from SQL Server 2000, you should be aware of what happens with the SQL Server Agent service. If you’re planning a new installation of SQL Server 2008 or upgrading from SQL Server 2005, this section doesn’t directly apply to you; however, you may find this information educational.

Previously in this chapter, we discussed the addition of the three new database roles in the msdb database: SQL Agent Reader Role, SQL Agent User Role, and SQL Agent Operator Role. As with a new installation, an upgrade of SQL Agent also restricts default access to only sysadmin users. The exception to this, in the case of an upgrade, is that users who owned jobs prior to the upgrade will be automatically granted access to the SQL Agent User Role database role in msdb. This will allow these jobs to continue to execute after the upgrade. In most cases, the jobs will continue to execute as before without any issues. There are a few exceptions that relate to jobs when the single global proxy account was set and used in the job.

As noted earlier, in early versions of SQL Server Agent, the sysadmin could define a single proxy account to use to allow non-sysadmin users to perform any of these functions:

  • Execute active scripting job steps.
  • Execute command shell job steps.
  • Log job-step output to the file system.
  • Execute the xp_cmdshell XP.

In an upgrade, the single global proxy, if defined, is created on the upgraded server and is called UpgradedProxy. Job owners who had active scripting or command shell jobs will have access to this proxy after the upgrade. Those non-sysadmin users who were logging their job output to the file system will now be upgraded to logging this output to a table. These changes will allow the jobs to continue to execute as they did previously and now provide the sysadmin the ability to configure the security context of the job step to a more granular level.

Another important note about proxies is with respect to the use of the xp_cmdshell XP. In previous versions of SQL Server, sysadmins set the proxy for this via an Agent XP, xp_sqlagent_proxy_account. In SQL Server 2008, this behavior has changed; if any of your scripts call this XP, they will need to be changed to use the new stored procedure, sp_xp_cmdshell_proxy_account, to set the proxy account for the xp_cmdshell. The link between Agent’s global proxy account and the credentials that xp_cmdshell run under are now broken, which makes sense since you should not have to configure SQL Agent to use xp_cmdshell.

Another minor surprise you may notice is if you had originally configured Agent to connect to SQL Server using SQL Server authentication. In SQL Server 2008, Agent prohibits connecting back to SQL Server using SQL Server authentication. With the move to multiple proxies, Agent did away with storing credentials in the registry and now accepts only Windows authentication, which means that the Agent service account must be sysadmin on SQL Server. If you had SQL Server authentication and upgraded to SQL Server 2008, Agent will use the Agent service account to connect back to SQL Server after the upgrade is complete.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics