Agent and Jobs (DW)---a Technical Reference Guide for Designing Missioin-Critical DW 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. 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.)

  • All jobs are stored in the MSDB database. Today, with SQL Server 2008 and R2, if you move a user, database jobs associated with that database are not transferred over to another server. They would need to manually be scripted out and applied (or backup/restore of MSDB) onto the other server. Many users will script the jobs and apply to both servers (with the jobs on the secondary in an inactive state).

  • 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.

  • Create SQL Server Agent proxy accounts as necessary for jobs to complete, but be wary of permissions, particularly high levels of data access, and objects outside of the SQL Server itself. For further information on the Proxy account creation, see Creating SQL Server Agent Proxies.2

  • SQL Server Express does not have a SQL Server Agent. There are other ways, such as using the Windows Task Scheduler, to scheduler certain maintenance.

Case Studies and Reference

Following are some helpful examples:

Questions and Considerations

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

  • Many times it is multiple servers or instances that a user wants to manage from a single source. To achieve this one can implement a Master Job Server which can allocate jobs to specific target servers with the master as a centralized place to manage all the jobs.

  • 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.

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 subsystem https://support.microsoft.com/kb/972759

2 Creating SQL Server Agent Proxies https://msdn.microsoft.com/en-us/library/ms189064.aspx

3 5 Things to Consider for SQL Server Data Warehouse DBAs http://www.sqlmag.com/blogs/sql-ser

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

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