Agent and Jobs (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Enterprise customers require automation and scheduling of scripts or tasks. Both of these can be accomplished by the Microsoft SQL Server Agent and by jobs which can execute tasks on a scheduled basis, via a trigger event or on demand.

SQL Server Agent is a Windows service that executes scheduled tasks and uses SQL Server to store job information. Typical requirements for the creation of jobs are time-based automation, stepping mechanisms, and job status reporting.

Common scheduled jobs that run through the SQL Server Agent service include database backups, database maintenance (such as index rebuilds or statistics updates), batch operations, and the execution of SQL Server Integration Services packages. Assuming that appropriate permissions are provided, the jobs allow for a variety of task types, ranging from the execution of Transact-SQL commands to the execution of external operating system calls.

Best Practices

The following section describes some best practices and potential pitfalls, and provides references for additional information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

  • The SQL Server Agent subsystem has a number of worker threads allocated to it, and limited by the max_worker_threads configuration. You may need to consider increasing this number if you are running a very large number of jobs at once. Also, be aware the Service Pack 1 (SP1) for SQL Server 2008 resets the configured max_worker_threads value. See the Microsoft Support article FIX: Installing SQL Server 2008 Service Pack 1 may reset the "max_worker_threads" column value for a SQL Server Agent subsystem1 for more information.

  • Plan for the service account to be used for SQL Agent service. Do not use an administrative account as the service account. For details, see Selecting an Account for the SQL Server Agent Service2 and Service Account Types Supported for SQL Server Agent.3

    In early versions of SQL Server, it was common to allocate a highly privileged account as the service account for SQL Server Agent, so that the Agent could perform all required tasks. SQL Server 2005 introduced the concept of credentials and proxy accounts. Via credentials and proxy accounts, individual job steps can be granted the use of an identity that has the required permissions for the job step. Create SQL Server Agent proxy accounts as necessary for jobs to complete, but be wary of granting permissions for particularly high levels of data access and to objects outside of SQL Server. For further information about proxy account creation, see Creating SQL Server Agent Proxies.4 Create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps. For more detail, see Implementing SQL Server Agent Security.5

  • If you have large number of target servers, avoid defining your master server on a production server that has significant demands from other SQL Server functions because the target server traffic can slow performance. If you also forward events to a dedicated master server, you can centralize administration on one server. For more detail, see Creating.6

  • By default the SQL Server Agent service is not configured to start automatically when SQL Server 2005 (or later versions) is installed unless the user explicitly chooses to auto-start the service. For more information, see Starting, Stopping, and Pausing SQL Server Agent Service.7

  • SQL Server Agent jobs that are running at the time of a failover event on a SQL Server failover cluster instance do not resume after the failover. SQL Server Agent jobs that are running at the time a Hyper-V node is paused do not resume if the pause causes a failover to another node. Jobs that begin but fail to complete because of a failover event are logged as started, but do not show additional log entries for completion or failure. For more information, see About SQL Server Agent.8

  • Frequently there are multiple servers or instances that a user wants to manage from a single source. To achieve this, you can implement a master job server that can allocate jobs to specific target servers with the master as a centralized place to manage all the jobs. For more detail, see Automating Administration Across an Enterprise.9

Case Studies and References

Following are some helpful examples:

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Establish which administrative tasks or server events need to be automated. A task is a good candidate for automation if it involves a predictable sequence of steps and occurs at a specific time or in response to a specific event.

  • All jobs are stored in the MSDB database. If you are currently using SQL Server 2008 or SQL Server 2008 R2, when you move a user database to another server, the jobs associated with the database are not transferred over to the other server. You need to manually script the jobs and apply them to the other server (or run a backup/restore of MSDB). Many users script jobs and apply them to multiple servers (while keeping the jobs on the secondary server in an inactive state).

  • SQL Server Express does not have a SQL Server Agent. There are other options for scheduling maintenance tasks, such as using the Windows Task Scheduler. Note that some Tier-1 applications can use a mix of SQL Server editions, such as SQL Server Enterprise Edition and SQL Server Express. (For example, a British customer whose engineers work on a race track uses a combination of servers including SQL Server Express).

  • From our experience and based on feedback, the out-of-the-box toolset provided may not be comprehensive enough for some enterprises. There are some gaps in enterprise solution offerings for job management and scalability, and gaps in what SQL Server Agent and Master Job Servers can provide. The biggest customer requests are for more specific calendaring functions, advanced routing/conditional logic, and broader event viewing. There are some third- party vendors who try to provide a more comprehensive solution (for example, BMC Control-M or SQLSentry).

  • While SQL Server Agent can run commands and even includes simple routing and logging, for more complex operations you might want to investigate SQL Server Integration Services, which has far more advanced routing, conditional branching, and logging capabilities. SQL Server Integration Services packages can be launched from within SQL Server Agent job steps.

  • Ask which of the SQL Server technologies (such as database maintenance plans, log shipping, and SQL Server Replication) will be using the Agent/Jobs service to get a holistic usage scenario.

  • SQL Server Agent includes the ability to send e-mail. You can configure SQL Server Agent Mail to send e-mail messages to predefined operators in the following situations:

    • When an alert is triggered. Alerts can be configured to send e-mail notification of specific events that occur.

    • When a scheduled task, such as a database backup or replication event, succeeds or fails.

Appendix

Following are the full URLs for the hyperlinked text.

1 FIX: Installing SQL Server 2008 Service Pack 1 may reset the "max_worker_threads" column value for a SQL Server Agent subsystemhttps://support.microsoft.com/kb/972759

2 Selecting an Account for the SQL Server Agent Servicehttps://msdn.microsoft.com/en-us/library/ms191543.aspx

3 Service Account Types Supported for SQL Server Agenthttps://msdn.microsoft.com/en-us/library/ms345380.aspx

4 Creating SQL Server Agent Proxieshttps://msdn.microsoft.com/en-us/library/ms189064.aspx

5 Implementing SQL Server Agent Securityhttps://msdn.microsoft.com/en-us/library/ms190926.aspx

6 Creating a Multiserver Environmenthttps://msdn.microsoft.com/en-us/library/ms191305(v=SQL.105).aspx

7 Starting, Stopping, and Pausing SQL Server Agent Servicehttps://msdn.microsoft.com/en-us/library/ms190695(SQL.105).aspx

8 About SQL Server Agenthttps://msdn.microsoft.com/en-us/library/ms189237.aspx

9 Automating Administration Across an Enterprisehttps://msdn.microsoft.com/en-us/library/ms180992.aspx

10 Scheduling Sub-Minute Log Shipping in SQL Server 2008http://sqlcat.com/technicalnotes/archive/2009/02/24/scheduling-sub-minute-log-shipping-in-sql-server-2008.aspx

11 Restart SQL Audit Policy and Jobhttp://sqlcat.com/toolbox/archive/2009/04/22/restart-sql-audit-policy-and-job.aspx