SQL Server Agent

The chief component of the automated administrative capability is the SQL Server Agent (named Agent for brevity in this chapter). It is a separate binary layer that executes your administrative jobs and alerts. The Agent runs as a service named SQL Server Agent on Windows. You can also deploy it to the workstation.

Jobs

A job is the central and definitive administrative task managed by the Agent. Each job is a collection of one or more steps. Each step can be a T-SQL statement, an operating system command, an executable program, a replication agent, or some Visual Basic, Java, C# or batch command driven script. You can run the jobs once, or you can schedule them to repeat periodically. Using the many functions you have at your disposal, you can create some of the most advanced scripts that execute on SQL Server only if the server is in a specific state.

For example, using T-SQL you can assess processor activity, monitor it for a certain length of time, and then decide if the time is right to begin a backup, a defragmentation, or a distributed query, or else you can check if the boss is logged on and working so that you can go out and catch a cappuccino.

The Agent can run your jobs at the specified times, without the need for any human intervention. You can code the most complex procedures with error-checking logic and flow control and design them to allow SQL Server to address any condition it will likely encounter at any time. The effort you put into the Agent will pay big dividends and can make the difference between working at all hours or going home early.

SQL Server Agent is most useful for the scheduled running of various ETL, packages (which stands for Extract, Transform and Load). These SSIS packages, discussed earlier in this chapter, are themselves highly sophisticated programs. One common usage of ETL packages is to move data from various data repositories on the enterprise network, such as those that live in the likes of huge Teradata data warehouses, into the fact tables and dimensions of your SQL Server based data warehouses and data marts. Under SQL Server Analysis Services, SQL Server Agent can be used to schedule the ETL packages, purge and load CUBES and perform sophisticated functions used by the business intelligence community.

Events and Alerts

When SQL Server 2005 is installed, it records all its significant events in the Windows application log facility Each entry in the log is called an event; you can define alerts that watch the event log and trigger the Agent to start a job when such an event is encountered. The Agent compares the events in the application log against the alerts you defined; if it encounters a match, a job can be fired.

SQL Server creates events for errors with a severity of 19 or higher. These events are also raised if a RAISERROR statement is executed using the WITH LOG clause, or the xp_logevent system stored procedure is executed. This allows T-SQL scripts, triggers, stored procedures, and applications to raise events that can be used to fire a job.

Operators

SQL Server Agent Operators are the e-mail and page addresses you define in SQL Server for use in alerts. You can define an alert that either e-mails or pages a specific person. Instances of SQL Server running on Windows Server 2005 can also use the Windows net send command to send a network message to a Windows user or group. There are numerous methods for detecting an alert and making sure someone gets your message-short of sending a note in a bottle.

Triggers

Triggers are used to enforce business logic and however, triggers can be integrated with automated administrative tasks by using either RAISERROR or xp_logevent to generate an event that fires an alert.