Automating Administrative Tasks

This topic defines automated administration and its components, defines multiserver administration, and introduces Microsoft® SQL Server™ tools you can use to define tasks for automation.

What is Automated Administration?

Automated administration is the programmed response to a predictable administrative responsibility or server event. Administrators, application writers, and analysts operating data warehouses can benefit from task automation. To automate administration:

• Establish which administrative responsibilities or server events occur regularly and can be administered programmatically.
• Define a set of jobs and alerts.
• Run the SQL Server Agent service.

Why Should I Automate Administration?

The job of an administrator entails various administrative duties that do not change from day to day and can be tedious chores. By automating recurring administrative tasks and responses to server events, you can free time to perform administrative tasks that require creativity and lack predictable or programmable responses.

An Example of Automatic Administration

Joe, a SQL Server administrator, backs up all the company servers every weekday after hours. He wants to be sure that the backups run smoothly. If there is a problem, he must correct it before work begins the next day. All the company servers are networked.

To automate daily backups, Joe:

• Defines himself as the MSXOperator operator.
• Sets up a nonproduction server as a master server and enlists the other company servers as targets in this master server.
• Creates a backup job with Transact-SQL job steps.
• Schedules the job to begin every evening at 8 P.M.
• Defines the job to notify him by pager and to write an event to the
  Microsoft Windows NT® application log if it fails.
• Starts the SQLServerAgent service, which must be running before administration tasks can be automated.

Joe can leave work at 5 P.M. confident that his servers are backed up on schedule. If the backup job encounters a problem, SQL Server Agent pages him and records the event. If SQL Server Agent encounters no problem with the backup job, Joe can return the next day to fully backed up servers.

What is Multiserver Administration?

Multiserver administration is the process of automating administration across multiple servers in a network.

You can benefit from multiserver administration if you:

• Manage three or more servers.
• Schedule information flows between enterprise servers for data warehousing.

A multiserver administration configuration consists of at least one master server and at least one target server. A master server distributes jobs to and receives events from networked target servers. A master server stores the central copy of job definitions for jobs run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the job status.

For example, if you administer departmental servers across a large corporation, you can define one backup job with job steps, operators to notify in case of failure, and an execution schedule. You can write this backup job one time on the master server, then enlist each departmental server as a target server in the master server. In this way, all the departmental servers can run the same backup job even though you defined it only one time.

Multiserver administration features are intended for members of the sysadmin role. However, a member of the sysadmin role on the target server cannot edit the operations performed on the target server by the master server. This security measure prevents accidental deletion of job steps and interruption of operations on the target server.

What are the Components of Automatic Administration?

Jobs, operators, and alerts are the three main components of automatic administration.

Jobs

You can 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. Jobs can be:

• Executed on one local server or on multiple remote servers.
• Executed according to one or more schedules.
• Executed by one or more alerts.
• Made up of one or more job steps.

Job steps can be executable programs, Windows NT commands, Transact-SQL statements, Microsoft ActiveX Script™, or replication agents.

Operators

An operator is an individual responsible for the maintenance of one or more computers running 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 in one or more ways:

• E-mail
• Pager (through e-mail)
• net send

You can choose to define an operator’s e-mail alias as an alias assigned to a group of individuals. In this way, all members of that alias can be notified at the same time.

Alerts

An alert is a definition that matches one or more SQL Server events and a response, should those events occur. In general, an administrator cannot control the occurrence of events but can control the response to those events with alerts. Alerts can be defined to respond to SQL Server events by:

• Notifying one or more operators.
• Forwarding the event to another server.
• Executing a job.

How do I Define Jobs, Operators, and Alerts?

To define jobs, operators, and alerts you can use:

• SQL Server Enterprise Manager.
• Transact-SQL scripts.
• SQL-DMO objects.

Regardless of what method you use to define your administrative tasks, they do not run automatically until the SQLServerAgent service has been started.