Introduction
There are not any individuals - no matter how dedicated an employee or proficient technically - that can work 24 hours per day every day of the year and every year after that year yet that is what is asked of Database Administrators on a daily basis: to never drop the ball, be ready to deploy or execute at a moment’s notice, and to perform vital maintenance tasks regardless of what the clock shows. We are capable of meeting these requirements though thanks to the SQL Server Agent service and it’s components. The SQL Server Agent is a scheduling system, a job processor, a communication tool, and a shepherd of 24x7 administration for Microsoft SQL Server. As we dig into the breadth of this powerful tools suite you’ll see that for yourself.
The Components of the SQL Server Agent
While SQL Server Agent presents itself as a single service it has multiple components:
- Job Scheduling
- Notification and Alerting
- Operators
- Error Logging
- Proxies
The SQL Server Agent is a broad subject and we will need to span many articles to expound on every facet of its scope of features. Since this is part of a series derived from my IT/Dev Connections presentation covering the most important concepts Accidental DBAs need to know early in their careers we’re going to focus on a high level in this initial article on the first of these: Job Scheduling.
SQL Server Agent as a Job Scheduling System
When I speak to the needing-to-be-there-24x7 I’m referring specifically to the aspect of SQL Server Agent as a job scheduler. SQL Server Agent allows you to create jobs of various complexities that are composed of one or more “steps” each with the ability to run commands created from languages ranging from Transact-SQL and the suite of SQL-centric scoped tasks (SQL Server Analysis Services commands or queries, SQL Server Integration Services packages) and also PowerShell, VB Script, and operating system tasks. Each of these steps can be chained to execute subsequent or out-of-sequence steps based upon the success, failure, or completion of each step in the chain. Furthermore you can send alerts, save step output, or generate log files from each step. These jobs can be scheduled to execute one time or on a given schedule and just as the steps can be configured for alerting and notification so can the job itself. You can even configure jobs to run on remote targets if you so choose.
Why don’t we step through the process of creating a simple, multi-step job that performs a truncate of a table on a database after performing a differential backup first to ensure you’re able to recover should the change prove incorrect.
You locate the functionality for job creation through the Object Explorer in Microsoft SQL Server Management Studio (SSMS). Expand the server node and you’ll see SQL Server Agent as a sub-node:
From here you would right-click on Jobs and select “New Job” from the displayed drop-down menu. You’ll be greeted with the following form:
At this point I’ve taken the liberty of filling out the necessary fields to accomplish what we want to in this initial step: creating a differential backup. By default the job owner is the login of the current user. Even though the job is owned by my AD login that does not mean that is the context for the execution. By default the job is executed by the SQL Server Agent service account. This means that if you need to ensure that service account has all rights necessary to perform the tasks it’s being requested to enact. Also if any local or remote drives are involved in the job effort the SQL Server Agent service (or the executing account since this is a definable setting) must have rights to the volumes involved.
Each step has two screens: a general screen and an advanced screen. The general screen is displayed above and allows you to declare the “what” of the step – the command to be run. The advanced screen provides for the criteria over how the steps flow from one to another as well as logging information about the step (see below.)
As you’ll not here for this example I want to ensure that the truncate step (the next one in the chain) will only occur if the backup succeeds. If the backup fails then I want to fail the job. I’m not retrying this step though you can see the ability to do so exists. I also want to write the output of the step (what you’d see in the messages tab of a query window in SSMS if you executed the step’s code there) to a file shown in C:\temp. This file, if exists, will be overwritten and I also want the step output stored in history within the log. I usually write the output in both manners because the file itself will not truncate the output whereas the output file to step history is subject to limits of line size. You can also see that you can set the job to run as a user other than the default service account for SQL Server Agent.
Now we repeat the process for the truncation step with appropriate values:
In the truncate step I’ll set the database context to the database in which I plan on truncating the table. Optionally I could have left it as the default of master so long as I fully qualified the command to include the database name and schema.
For the truncate step I’m willing to attempt the execution up to three times with a wait of one minute between attempts. If the step succeeds I want the job to complete as succeeded. Likewise if the step fails then fail the job. This comes into play when we look at what I have configured for job alerting and notification. Again I want to log the output to the same file created/listed in step one but this time append the output to the existing job so I can see the output of all steps in order within a single file.
As for the scheduling of the job you have a plethora of possibilities. You can execute the job one time (as shown above) or you can schedule it to occur repetitively.
Finally we have the notification settings screen:
In this case I want to notify (well) me should the job fail via a page as it’ll be urgent. If the job completes (succeeds or fails) I want an email of the status. I also desire to have the Windows log retain an entry for the job status and then if the job succeeds clean up behind itself by deleting the job since I only intend it to be run once.
Conclusion
The SQL Server Agent is a broad tool. Next month we will look at SQL Server Agent and the areas of:
- SQL Server Agent as a Notification and Alerting System
- SQL Server Agent and SQL Operator Objects
- SQL Server Agent and Error Logging
- SQL Server Agent and Proxies
