Wiki Home

SQL Server Agent

Namespace: WIN_COM_API
The SQL Server Agent is a Windows service that can interact with a SQL Server. It contains three main features: Alerts, Operators, and Jobs.

Alerts are objects that respond to errors that occur on the server. There are some demo alerts in the Agent by default, but they don't do anything as nothing is defined for the response of the alert - therefore, alerts are useless until you set up one or more Operators. Once that is done, you can have an alert notify you when it is triggered by email, pager, or a net send.

Operators are those lucky folks who should be notified whenever something goes wrong on the SQL Server. The operator is identified by a name as well as one or more of the following: an email address, a pager email address, or a net send address. Optionally, you can set a schedule for each operator, allowing you to bother the right person at the right time. Operators can be notified for specific alerts as well.

Jobs are probably the most used part of the Agent, as they allow you to automate nearly any task on the server. A Job contains one or more steps of code, and each step can be coded as T-SQL code, ActiveX script (such as VBScript or JScript) or even a DOS batch file. The steps are linked as desired, including skipping steps when problems occur, and steps can be set to be retried up to 9999 times with a delay between retries.

Once a job is set up, you can set up a schedule for it, allowing you to automate the work done by the job. Also, if you have set up one or more operators, you can be notified when the job succeeds or fails as appropriate. It's even possible to setup a one-time-only job that deletes itself upon successful completion.

Note that to set yourself up as an Operator so you get emailed by the agent, you must set up SQL Agent Mail. In my experience, this is not a trivial setup process, requiring that you set your agent to run under a domain account, have a MAPI-compliant email client installed, a mail profile already setup and tested, and then selecting that profile from the dialog in the SQL Server Agent properties dialog (right-click the agent in EM to get it).

Also, jobs can be created by the wizards in Enterprise Manager, such as using the Database Maintenance Plan wizard. This particular wizard is not a great learning tool, however, as this wizard creates a one-step job that calls a command-line utility sqlmaint, with cryptic parameters.

All of the data needed by the agent is stored on the server's MSDB database - therefore, if you do any significant configuration in the agent, be sure to make a good copy of your MSDB database.

-- Chuck Urwiler
( Topic last updated: 2004.01.09 01:59:52 PM )