• Reduced administrative workload
• Reduced risk of overlooking vital maintenance tasks
• Recuced risk of human error
• Proactive management

• Helps database administrators schedule core tasks
• Creates one or more SQL Server Agent jobs
• Administrators can edit and create plans manually

• The compnent of SQL Server responsible for automation
• Runs as a Windows Service
• Must be running to perform jobs, fire alerts, and contact operators

• SQL Server Agent service startup mode
  • SQL Server Agent service is not configured to start automatically by default
• SQL Server Agent service account
  • Requires log on as a service user right
• SQL Server Agent service dependencies
  • SQL Server service
  • Messenger service for Net Send notifications

• Database Mail
  • Uses SMTP
  • Requires mail profile for the SQL Server Agent account
  • SQL Server Agent account must be a member of the DatabaseMailUserRole
  • SQL Server Agent service must be restarted
• SQL Mail
  • MAPI-based
  • Uses Microsoft Exchange Server
  • Included for backward compatibility

• Specified series of operations performed sequentially by SQL Server Agent
• Can include Transact-SQL, command-line application, and ActiveX script steps
• Can be scheduled to run once, run repeatedly, or be started manually

• People or groups that can receive notifications
  • Notifications can be from a job, a job step, or an alert
• Can be notified by using e-mail, pagers, or net send messages
• Fail-safe operator can be specified

                                         +-----------------+
Job step 1: Back up                  Yes |                 |
Database (Transact-SQL) ---> Fail?  ---->|+---------------+|
                              |No        ||    Write to   ||
Job step 2: Transfer Data  <--+          ||  Application  ||
       (CmdExec)                         ||      Log      ||
           |                         Yes |+---------------+|
           +---------------> Fail?  ---->|                 |
                              |No        |+---------------+|
Job step 3: Custom         <--+          ||               ||
Application (ActiveX Script)             ||Notify operator||
           |                         Yes ||               ||
           +---------------> Fail?  ---->|+---------------+|
                              |No        |                 |
                              |          +-----------------+
           Notify operator <--+          

• SQL Server Management Studio tool
• Allows you to:
  • Start and stop jobs
  • View job properties
  • View the history for a specific job
  • Refresh the information in the Agent Job Activity grid (manual or automatic)

• Predefined response to an event
• Triggered by jobs, performance conditions, or application log events
• Can notify an operator or start a specified job

• Use SQL Server Management Studio or sp_add_alert
• Specify:
  • Alert name
  • Event or condition that triggers the alert
  • Response - notify an operator, start a job

• Master server
  • Holds central copy of job definitions
  • Distributes jobs to enlisted target servers
  • Receives messages from enlisted target servers
• Target servers
  • Are assigned to one master server
  • Connect periodically to the master server to update their schedule of jobs
  • Download any new jobs

• SQL Server Agent service and the SQL Server service must be running as Windows domain accounts
• First server enlisted becomes the master server; subsequent servers enlisted become target servers
• MSXOperator is created on master server to receive notifications
• Create jobs on the master server and specify the required target servers

• Forward event messages to an instance of SQL Server
  • All events
  • Events above a specified severity level

• Database roles in the msdb database with predefined SQL Server Agent permissions
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

• Define the security context for a job step
• Provide granula control over access to subsystems
• Are mapped to Windows users by using credentials
  • The user specified in the credential must have the log on as a batch job user right
• Can only be used by principals with permission
  • Logins
  • Fixed server roles
  • Database roles in the msdb database