Microsoft® SQL Server™ provides features that allow administrators to program the server to administer itself for many repetitive actions or exception conditions. This frees the administrators to spend more time on activities such as designing databases and advising programmers on efficient database access coding techniques. Applications from any vendor can choose SQL Server as their data storage component and minimize the administrative requirements of customers by automating administrative tasks.
These automation features are not limited to database administration tasks such as scheduling backups. They can also be used to help automate the business practices that the database supports. Applications can be scheduled to run at specific times or intervals. Specific conditions detected in the system can be used to trigger these applications if they need to be executed before the next scheduled time.
The features that support the automation of administrative tasks are:
SQL Server Agent is a separate executable program that executes administrative jobs and alerts defined by the system administrators. It runs as a service named SQLServerAgent on computers running Microsoft Windows NT®, as an executable file on computers running Microsoft Windows® 95/98.
A job defines an administrative task. Each job has one or more steps; each step specifies a Transact-SQL statement, Windows command, executable program, replication agent, or Microsoft ActiveX® script. Jobs can be run once, scheduled to run at periodic intervals, or specified to run when the server is idle.
Jobs give administrators the ability to define when administrative tasks are performed. Each job can combine various operating system commands, Transact-SQL statements, stored procedures, and applications to complete complex administrative functions. Each job step can be very complex. For example, a Windows command could be a command or batch file that contains many commands. The Transact-SQL statement executed by a step could be a stored procedure containing many Transact-SQL statements.
SQL Server Agent runs these tasks at the times specified without the need for human intervention. Complex procedures with error-checking logic can be designed into each job to address the most likely conditions the job would encounter. These capabilities result in the ability to build complex, robust jobs that run all periodic maintenance.
Each copy of SQL Server running on Windows NT records significant things that happen to it in the Windows NT application log. Each entry in the log is called an event. SQL Server administrators can define alerts that specify a job to be run when a specific event occurs. SQL Server Agent compares the SQL Server events in the application log against the alerts defined by administrators. If a match is made, the job specified in the alert is executed.
Windows 95/98 does not have event logs. Copies of Desktop SQL Server running on Windows 95/98 use a SQL Server Profiler-based mechanism to communicate events to SQL Server Agent.
SQL Server creates events for errors with a severity of 19 or higher. 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 Transact-SQL scripts, triggers, stored procedures, and applications to raise events that could fire a job.
Operators are e-mail and page addresses defined to SQL Server for use in alerts. An alert can be defined that either e-mails or pages a specific person. If SQL Server is running on Windows NT, it can also use the Windows NT net send command to send a network message to a Windows NT user or group.
Triggers are used to enforce business logic. Triggers can be integrated with automated administrative tasks by using either RAISERROR or xp_logevent to generate an event that fires an alert. For example, assume that a retail company has an inventory database, and all of their suppliers accept electronic orders. Every night, a scheduled job executes an application that reviews all inventory levels and, using guidelines established by management, either places orders with preferred providers for items in short supply or prints a report for the purchasing agents. This could be backed up by a DELETE trigger on the parts table that fires a similar job for emergency orders if heavy sales deplete the inventory during the day.
| Automating Administrative Tasks | Enforcing Business Rules with Triggers |