Automating Administrative Tasks
Automated administration is the programmed response to a predictable administrative responsibility or server event. By using automated administration, you can free time to perform administrative tasks that lack predictable or programmable responses and require creativity.
For example, if you want to back up all the company servers every weekday after hours, you can create a job to perform this task. Schedule the job to run at the required time. If the job encounters a problem, SQL Server Agent can record the event and page you.
If you are running multiple instances of Microsoft® SQL Server™, use multiserver administration to automate tasks. For more information, see Multiserver Administration.
To automate administration:
- Establish which administrative responsibilities or server events occur regularly and can be administered programmatically.
- Define a set of jobs, alerts and operators by using SQL Server Enterprise Manager, Transact-SQL scripts, or SQL-DMO objects. For more information, see Creating Jobs.
- Run the SQL Server Agent service.
Automatic Administration Components
Jobs, alerts, and operators are the three main components of automatic administration.
A job is a specified series of operations performed sequentially by SQL Server Agent. Use jobs to define an administrative task that can be executed one or more times and monitored for success or failure each time it executes. Execute jobs:
- On one local server or on multiple remote servers.
- According to one or more schedules.
- By one or more alerts.
For more information, see Creating Jobs.
An alert signals the designated operator that an event has occurred. For example, an event can be a job starting or system resources reaching a threshold. You define the conditions under which an alert is generated. You also define which of the following actions the alert takes:
- Notify one or more operators.
- Forward the event to another server.
- Execute a job.
For more information, see Defining Alerts.
An operator is an individual responsible for the maintenance of one or more instances of SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In larger enterprises with multiple servers, many individuals share operator responsibilities.
Operators are notified of alerts in one or more of the following ways:
You can define the e-mail alias of an operator as the alias for a group of individuals. In this way, all members of that alias are notified at the same time.
- Pager (through e-mail)
- net send
For more information, see Defining Operators.