Featured Article(s)
Troubleshooting SQL Server 2008 full-text search
In this article, Alexander Chigrik explains some problems you can have with SQL Server 2008 full-text search.
Scheduling Jobs For Your Databases
Steve has been doing a series of Articles on TSQL, the last of which he focuses on Job Schedulers for SQL Server, TSQL 10 – Need To Know Information About Jobs and Automation.
There are a couple of CHRON (chronological) Job Schedulers available to you in the Microsoft Platform. There is the Windows NT Scheduler Service you manage through Control Panel, and there is the SQL Server Agent that ships with SQL Server.
Both of these scheduling tools execute a process in a thread on the local machine. However, it does have the ability to fire off work on other machines…but this is not a common usage and not a task for the novice.
Windows scheduler is a good choice when you need to combine work at the operating system and in SQL Server. The scheduler will execute executables, batch files, or PowerShell scripts. Using windows scheduler you can create tasks that integration things like database backup, external compression, FTP transfer, dos copy, file deletion, log parsing, etc. Other script languages that you can run from a DOS prompt may also be executed here such as ANT, NANT, Python scripts, etc.
The SQL Server Agent is more powerful. In addition to the features of the Windows Scheduler, it also has more flexibility on the scheduling, can execute SQL command directly, can also execute dos, PowerShell and other commands. The problem you run into here is security. The Agent jobs by default run under the credentials of the SQL Agent service. By default, you want to minimize those credentials as much as possible.
SQL Server Agent also maintains a history (configurable) of the execution of the tasks. For recurring tasks the history is valuable for debugging, etc. In addition, SQL Agent jobs allow multiple steps. And there is a form of program control in that you can determine what the next step should be when the current step fails or succeeds.
Since SQL Agent can execute any TSQL task you wish, it can also execute stored procedures. This is a common way to build SQL Agent steps. Stored procedures specific to your task are created.
The ability to execute SQL Server Integrated Services jobs from SQL Agent make it an attractive programming interface. SQL Server Integration Services has built into it tools for performing almost any automated task, and the ability to tie in your own Dot Net application for those special case scenarios that don’t fit into the tool. Already it can execute any TSQL command, import and export data from multiple file types and sources, execute FTP or DOS commands, perform backups, and much more. The best part of SSIS is the error handling options available to you. When a particular task fails you have the ability to handle those different errors. SSIS also allows you to introduce variables into your SSIS commands causing it to work differently under different situations.
These are just some of the many ways to use the tools right out of the box as shipped by Microsoft. Don’t forget the third party tools that either make managing this easier, or have their own management capabilities. The biggest issue you will find is that as your servers increase, the complexity of your jobs and the oversight of them increases as well. It can be very difficult to get a picture of what is scheduled to run, and how long it takes.
Are you struggling with managing your servers? Do you have tips you would like to share with other readers about how you handle your CHRON jobs? Send me a note at btaylor@sswug.org.
Cheers,
Ben