SQLServerCentral Article

SQL Server Agent 2005

,

What is new in SQL Server 2005 Agent?

It has been well over five months since SQL Server 2005 was released. Market research has shown that it is still gathering momentum among the software community. If you meet a person who is new to SQL Server 2005, the obvious question that they will ask is "What is new in SQL Server 2005?". Well, obviously there are lots of differences.

To start with it does have a nice UI. You can rename the databases, which you could not do in earlier versions. With these simple (but important) enhancements, there are a few major changes as well. DDL triggers and peer-to-peer replication can be considered as major changes.

So, it is obvious that the above question needs a lengthy answer, which cannot be completed in one article. So I thought to identify a major component of SQL Server 2005 that has had drastic changes. SQL Agent is one of them, so this series of articles will discus the changes in SQL Server 2005 Agent.

This article will describe about security and performance improvements are done to the SQL Server Agent.

Security Improvements

In SQL Server 2000, the SQL Server Agent service account had to be a member of the local administrators group when executing Jobs owned by users who were not members of the sysadmin fixed server roles. In order to give more control to the administrators, new three roles have been added in SQL Server 2005. These roles are SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

After the initial installation, only sysadmin users can edit, view and execute SQL Server jobs. These roles exists only in msdb database. The following screen will show you how to create a user with above mentioned roles.

With SQL Server 2005, a user must be a member of one of the new fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. After this basic introduction let us identify what are the specific uses of each role.

SQLAgentUserRole

This is the least privileged role among new three new fixed server roles. This role has permissions only on operators, local jobs, and job schedules. These types of users can only view the operator but they can't create, modify or delete operators. They cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. These users cannot delete job history.

SQLAgentReaderRole

SQLAgentReaderRole has all the options that SQLAgentUserRole has. In addition, it does have permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.

SQLAgentOperatorRole

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server.

Performance Improvements

When a job is running in SQL Server 2005, at the end of the each job step, the thread goes back to SQL Server Agent and check whether there is another job step of the same type waiting to be executed. If there is, the thread will execute that job step. If there is no similar job steps the thread terminates. This modification has allowed SQL Server Agent to execute jobs more efficiently than the SQL Server 2000 jobs.

In SQL Server 2000, registry entry determines how many threads of a subsystem could be activated at once. the max_worker_thread settings is stored in registry. For the default instance, this setting is in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems registry key. Last part of each key is a number which specifies the max_worker_thread setting for the subsystem. For QueueReader  subsystem , in my SQL Server instance it has the value C:\Program Files\Microsoft SQL Server\MSSQL\BINN\SQLREPSS.DLL,C:\Program Files\Microsoft SQL Server\80\COM\QRDRSVC.EXE,ReplStart,ReplEvent,ReplStop,100. This means that max_worker_thread  value is 100.

In addition to the above improvement, new four performance counter objects have introduced in SQL Server 2005 Agent. These objects can be used to improve the performance of your SQL Server.

SQLAgent:Alerts

SQLAgent:alrets counter object gives information about the SQL Server Agent alerts. This object has two counters.

Counter Name Description
Activated alertsThe total number of alerts that SQL Server Agent has activated since

the last time that SQL Server Agent restarted.

Alerts activated/minuteThis counter reports the number of alerts that SQL Server Agent

activated within the last minute.

Events are generated by Microsoft SQL Server and entered into the Microsoft

Windows application log. SQL Server Agent reads the application log and compares

events written there to alerts that you have defined. When SQL Server Agent

finds a match, it fires an alert. So from the above counters, a system admin can examine the alert pattern and their quantity.  If the Alert count is high it needs to prompt immediate actions. Alerts activated/minute is more important as it gives the density of alerts.

Only users who are member of the sysadmin fixed server role can use this counter object.

SQLAgent:Jobs

SQLAgent:Job counter object will give information about SQL Server Jobs. Following table shows available conunters.

Counter Name Description
Active Jobsthe number of jobs currently running.
Failed jobsthe number of jobs that exited with failure.
Job success ratethe percentage of executed jobs that completed

successfully.

Jobs activated/minutethe number of jobs launched within the last minute.
Queued jobsthe number of jobs that are ready for SQL Server Agent

to run, but which have not yet started running.

Successful jobsthe number of jobs that exited with success.

Each counter contains the following

instances:

Instance Description
_TotalInformation for all jobs.
AlertsInformation for jobs started by alerts.
OthersInformation for jobs that were not started by alerts or

schedules.

SchedulesInformation for jobs started by schedules.

SQLAgent:JobSteps

SQL Server Agent:JobSteps object has the counter to report information about SQL Server Agent job steps. Following table shows available counters.

Counter Name Description
Active stepsreports the number of job steps currently running.
Queued stepsreports the number of job steps that are ready for SQL

Server Agent to run, but which have not yet started running.

Total step retriesreports the total number of times that Microsoft SQL

Server has retried a job step since the last server restart.

Following are the instances for each counter listed above.

Instance Description
_TotalInformation for all job steps.
ActiveScriptingInformation for job steps that use the

ActiveScripting subsystem.

ANALYSISCOMMANDInformation for job steps that use the ANALYSISCOMMAND

subsystem.

ANALYSISQUERYInformation for job steps that use the ANALYSISQUERY

subsystem.

CmdExecInformation for job steps that use the CmdExec

subsystem.

DistributionInformation for job steps that use the Distribution

subsystem.

DtsInformation for job steps that use the Integration

Services subsystem.

LogReaderInformation for job steps that use the LogReader

subsystem.

MergeInformation for job steps that use the Merge

subsystem.

QueueReaderInformation for job steps that use the QueueReader

subsystem.

SnapshotInformation for job steps that use the Snapshot

subsystem.

TSQLInformation for job steps that execute Transact-SQL.

SQLAgent:JobStaticstics

This object has only one object named SQL Server Restarted. This counter gives number of times the Microsoft SQL Server has been successfully restarted by SQL Server Agent since the last time that SQL Server Agent started.

Conclusion

The availability of these roles have given administrators more flexibility when defining users for Jobs. Consider your requirements and associate the necessary fixed role that fit them.

SQL Server 2005 Agent also has four new performance counters. Using these counters, administrators can fine tune their jobs to a great extent.

In the next article we will take about few more additions that are introduced to SQL Server Agent 2005. Until then , do not hesitate to send your comments to dineshasanka@gmail.com

Rate

4.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.33 (3)

You rated this post out of 5. Change rating