Automated Administration Architecture
Microsoft® SQL Server™ 2000 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
SQL Server Agent is a separate executable program that executes administrative jobs and alerts defined by the system administrators. SQL Server Agent runs as a service named SQLServerAgent on computers running Microsoft Windows NT® or Windows® 2000, and as an executable file on computers running Microsoft Windows 95 or Microsoft Windows 98.
Jobs
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 enable administrators 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 specified times, 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.
Events and alerts
Each instance of SQL Server 2000 running on Windows NT or Windows 2000 records significant events in the Windows NT or Windows 2000 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 and Windows 98 do not have event logs. Installations of SQL Server Professional edition running on Windows 95 or Windows 98 use a SQL 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
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. Instances of SQL Server running on Windows NT or Windows 2000 can also use the Windows NT or Windows 2000 net send command to send a network message to a Windows user or group.
Triggers
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, a retail company has an inventory database, and all of its 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.