SQL Server Automation of Processes

It is the role of the SQL Server Agent service running on the server to control a lot of mechanisms within an instance of SQL Server. The SQL Server Agent is responsible for running jobs and tasks scheduled to occur at specific times and/or intervals. The agent also helps detect conditions for which administrators have defined actions to be taken. The agent also runs replication tasks, and in general is responsible for handling repetitive tasks and exception-handling conditions defined through the other SQL Server components.

The statistical maintenance functionality introduced in SQL Server 7 and expanded on in SQL Server 2000 may generate unwanted overhead on a production system if it initiates statistical updates during heavy production periods or starts a high number of UPDATE STATISTICS processes. It may be worthwhile to schedule this type of maintenance to occur during idle time to prevent this behavior.

Using the SQL Server Agent to automate tasks involves scheduling periodic activities on the server. Jobs are defined for tasks that are to be scheduled. Jobs can also be defined to run on demand or during idle time on the server. Notification of completion, errors, or other defined conditions is performed through the definition of operators and the assignment of conditions under which the operator is to be contacted. The definition of alerts identifies circumstances that the agent will watch for and act on when they occur.

Job, Operator, Alert Integration

Jobs, alerts, and operators can be defined as a single step because the interface tools in the Enterprise Manager are linked together, and each portion of the definition allows for new entries.

An organized approach to automation, however, is recommended. Possibly the first step is to define the important individuals in the environment who could respond to potential problems. As you create the operators, you should also supply the operator definitions with short descriptions of the operator's purpose and function as it relates to the database system.

Any of the operators defined can be selected to receive notification by a number of SQL Server operations. Email and other message-sending capabilities have been built into most of the features in SQL Server 2000. For this example, the operators are going to receive notification of a new customer. After they have been contacted, they can potentially contact the customer and perform an initial sales call.

As well as performing operator notification, you may want to execute tasks based on an alert condition.

A lot of processes can be performed through automation, and this represents an area where development and process skills are important. In tuning and recognizing other performance factors, SQL Server Agent alerts can be set up based on performance criteria. The capability to monitor the server for this type of information can be a huge administrative benefit.

The SQL Server Agent, operators, jobs, and alerts provide for a mechanism whereby maintenance operations can be scheduled to occur on a regular basis. This facility can be in itself an application architecture to build around.

Jobs Perform Recurring Tasks

Using SQL Server Agent jobs, you can automate tasks. After jobs are created, they can be run manually, on schedule, or in response to alerts. Jobs can be written to run on the local server or on multiple servers. To run jobs on multiple servers, you must set up at least one master server and one or more target servers. Anyone can create a job, but a job can be edited only by its owner or members of the sysadmin role.

Jobs consist of one or more steps that need to be performed. The steps can be operating system commands, Java Script, Visual Basic Script, or, most commonly, T-SQL statements. These steps are controlled by the workflow defined within the structure of the job allowing for mandatory, optional, and conditional procedures.

Jobs can be placed into job categories to help you organize your jobs for easy filtering and grouping. You could organize all your database backup jobs in the Database Maintenance category. You can also create your own job categories.

Operators Can Respond to Events

Operators are individuals or groups of individuals who can be identified by a network account, email identifier, or computer identification, who can address problems with the database and other servers. An operator can be sent a message resulting from an alert, a job step, job completion, or other SQL Server processes. Messages can be sent through email, a pager, or a net send network command.

Operators are not solely used for problem reporting. In an application design they may be contacted as new records enter a table within the database. Operators could be a sales team that is sent information about new clients. In essence, if you would like to contact an individual or a group for any data or server event, operators can be set up to serve the purpose.

Alerts Inform Based on Events

Alerts are actions to be taken when specific events occur, such as an error on the server, an application process call, or any other definable event. A definable event is any process that places information into the Windows 2000 application event log. Specific errors, errors of certain severity, or performance criteria can all have a corresponding alert defined. The alert can be defined to take such actions as sending an email notification, paging an operator through a paging service, or running a job to respond to the situation.

A lot of options are available for the content of the alert message. Depending on how the message is being sent out, you can include data samples, text messages, and attachments along with the message. This is important because often when an alert occurs, the only information you have to help you understand what caused the alert and the appropriate action to take is contained in the alert message itself.

Alerts can be configured for essentially anything that happens on the database server. Whether the event occurs within the computer, its operating system, the application server, an application that is executing, or the data itself, events can be configured to react to the situation.

SQL Server Event Alert

Errors and messages, or events, are generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the log and fires an alert based on the conditions supplied in the alert definition. By default, the following SQL Server events are logged in the Windows application log:

• Severity 19 or higher sysmessages errors

• Any RAISERROR statement invoked by using the WITH LOG syntax

• Any application logged by using xp_logevent

In a design consideration you may want to have other elements reported on besides the defaults provided within the system. Events are a response to what is happening with the data and on the server in which the data is held. This could easily be turned into a reporting system for any application. When data is added, someone can be notified; as data values change, audit records may be recorded; if data is removed, archival data storage is possible.

Performance Counter Alerts

The SQL Server Agent can monitor performance conditions in a similar fashion to how the System Monitor is used. A System Monitor counter can be used as the basis for specifying a performance condition to monitor. The condition causes an alert to fire if the performance threshold is reached. You can define the area of SQL Server performance to be monitored by defining the object, counter, instance, and threshold condition just as you do with the system/performance monitor. The difference is that the monitoring can be performed on an ongoing basis without any additional overhead.

The counter selected represents the specific property to be monitored. Because the performance data is collected periodically, there is often a delay between the threshold being hit and the performance alert firing.

Performance alerts can be used to activate maintenance jobs, such as backing up and clearing a log file when it is close to becoming full. You may also want individuals to be contacted in the event of potential problems such as an inordinate number of deadlocks per second. It is easier to allow for proactive management and solve potential problems before they can affect the end user.