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
