Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Agent 2005

By Dinesh Asanka,

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 alerts The total number of alerts that SQL Server Agent has activated since the last time that SQL Server Agent restarted.
Alerts activated/minute This 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 Jobs the number of jobs currently running.
Failed jobs the number of jobs that exited with failure.
Job success rate the percentage of executed jobs that completed successfully.
Jobs activated/minute the number of jobs launched within the last minute.
Queued jobs the number of jobs that are ready for SQL Server Agent to run, but which have not yet started running.
Successful jobs the number of jobs that exited with success.

Each counter contains the following instances:

Instance Description
_Total Information for all jobs.
Alerts Information for jobs started by alerts.
Others Information for jobs that were not started by alerts or schedules.
Schedules Information 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 steps reports the number of job steps currently running.
Queued steps reports the number of job steps that are ready for SQL Server Agent to run, but which have not yet started running.
Total step retries reports 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
_Total Information for all job steps.
ActiveScripting Information for job steps that use the ActiveScripting subsystem.
ANALYSISCOMMAND Information for job steps that use the ANALYSISCOMMAND subsystem.
ANALYSISQUERY Information for job steps that use the ANALYSISQUERY subsystem.
CmdExec Information for job steps that use the CmdExec subsystem.
Distribution Information for job steps that use the Distribution subsystem.
Dts Information for job steps that use the Integration Services subsystem.
LogReader Information for job steps that use the LogReader subsystem.
Merge Information for job steps that use the Merge subsystem.
QueueReader Information for job steps that use the QueueReader subsystem.
Snapshot Information for job steps that use the Snapshot subsystem.
TSQL Information 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

Total article views: 21454 | Views in the last 30 days: 24
 
Related Articles
ARTICLE

Managing Database Data Usage With Custom Space Alerts

A quick how-to on configuring SQL Server Agent alerts to manage file space usage.

FORUM

SQL Server Counters

SQL Server Counters

ARTICLE

SQL Server Alerts

SQL Server Alerts provide a great way for the server to notify a DBA that some event has occurred, u...

FORUM

Using WMI for SQL agent alerts

trying to get wmi to trigger an alert (to a remote sql server) when local database or agent service ...

FORUM

check counters

check counters

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones