====== Lesson 1: Automating Administrative Tasks in SQL Server 2005 ======
===== Benefits of Automation =====
  * Reduced administrative workload
  * Reduced risk of overlooking vital maintenance tasks
  * Recuced risk of human error
  * Proactive management
        
===== What Is the Maintenance Plan Wizard =====
  * Helps database administrators schedule core tasks
  * Creates one or more SQL Server Agent jobs
  * Administrators can edit and create plans manually
      
===== What Is SQL Server Agent =====
  * The compnent of SQL Server responsible for automation
  * Runs as a Windows Service
  * Must be running to perform jobs, fire alerts, and contact operators    

====== Lesson 2: Configuring SQL Server Agent ======
===== Considerations for SQL Server Agent Configuration =====
  * 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

===== Options for SQL Server Agent Mail =====
  * 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
               
====== Lesson 3: Creating Jobs and Operators ======
===== What Is a Job? =====
  * 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
      
===== What Are Operators? =====
  * 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
            
===== How to Create Job Steps =====
<code>
                                         +-----------------+
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 <--+          
</code>

===== What Is the Job Activity Monitor? =====
  * 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)
                     
====== Lesson 4: Creating Alerts ======
===== What Is an Alert? =====
  * Predefined response to an event
  * Triggered by jobs, performance conditions, or application log events
  * Can notify an operator or start a specified job
      
===== How to Create an Alert =====
  * 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
                 
====== Lesson 5: Managing Multiple Servers ======
===== What Are Master and Target Servers? =====
  * 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
                 
===== Considerations for Multi-Server 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
        
===== What Is Event Forwarding? =====
  * Forward event messages to an instance of SQL Server
    * All events
    * Events above a specified severity level       

====== Lesson 6: Managing SQL Server Agent Security ======
===== What Are SQL Server Agent Roles? =====
  * Database roles in the msdb database with predefined SQL Server Agent permissions
    * SQLAgentUserRole
    * SQLAgentReaderRole
    * SQLAgentOperatorRole
               
===== What Are SQL Server Agent Proxies? =====
  * 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