Configuring SQL Server Agent to use Database mail for automatic admin job status notification.

Module 7: Automating Administrative Tools
OverviewThere are many day-to-day administrative tasks that you must perform to manage database. Automating these tasks can minimize the administrative workload associated with managing a database.

In our exercise we will configure SQL Server Agent to use Database mail to automatically notify administrators with the success or failure upon completion of scheduled jobs.

Lesson 2: Configuring SQL Server AgentYou must configure SQL Server Agent appropriately to support automated database maintenance tasks.

SQL Server Agent Service Startup Configuration:
a. It can be configured to start manually, automatically or disabled. By default it start manually.
b. You can change the startup configuration using SQL Server Configuration Manager or Services on Windows Administrative Tools.
c. If you want to implement Automated Jobs and Alerts set it to start automatically unless there is a good reason to start manually.d. When SQL Server Agent starts it connects to SQL Server and runs the xp_sqlagent_monitor extended stored procedure in the master database. This stored procedure monitors the status of the SQL Server Agent service and restarts the service if it stops unexpectedly.

Specifying SQL Server Agent Service Account:
The following built-in accounts can be used for the SQL Server Agent Service:
a. Local System - is a member of the local Administrators group. Used only for backward compatibility only and should not be used for the SQL Server Agent service.
b. Local Service - similar permission as a local user and accesses network resources by using a null session with no credentials. This account is suitable for SQL Server Agent service when all jobs and alerts can be implemented locally on the server.
c. Network Service - similar permissions to a local user, and accesses network resources by using the credentials of the computer account. Microsoft recommends not to use this account for SQL Server Agent service.

In most cases, you should specify a local or domain user account for the SQL Server Agent and assign the minimal permissions and user rights that the service account requires.

The SQL Server Agent service requires the log on as a service user right, in addition the following user rights are required to support SQL Server Agent proxies:
a. Act as part of the operating system
b. Bypass traverse checking
c. Replace a process-level token
d. Adjust memory quotas for a process
e. Log on as a batch job

If the user rights listed above are not granted, only members of the sysadmin fixed server role can create jobs.
If a domain user account is used, it should be a member of the Pre-Windows 2000 Compatible Access group on the domain controller to run jobs for users who are not members of the administrators group.
SQL Server Agent service account uses Windows Authentication to connect to the SQL Server and must be mapped to a login that is a member of the sysadmin fixed server role.
If multi-server jobs will be created, login should be mapped to a user that is a member of the TargetServerRole database role in the msdb database on the master server.
SQL Server Agent Service Dependencies:SQL Server Agent Service depends on the SQL Server service for the instance to which it belongs. Also some operations performed by SQL Server Agent might rely on other Windows services. For example, to send operator notifications by using the net send command, the Windows Messenger service must be running.

Using Database Mail with SQL Server Agent:Database Mail is a feature of SQL Server that makes it possible for SQL Server to send e-mail through a Simple Mail Transfer Protocol (SMTP) server. To use Database Mail with SQL Server Agent, perform the following:1. Enable Database Mail.2. Create a Database Mail profile that includes an e-mail account for SQL Server Agent to use. Make this default user profile for he user in the msdb database that is mapped to the login for the SQL Server Agent service account.3. Add the user for the SQL Server Agent service account login to the DatabaseMailUserRole database role in the msdb database.4. Configure the alert system properties of SQL Server Agent to use Database Mail, and specify the profile you created in the previous steps.5. Restart the SQL Server Agent service.
When you have configured Database Mail, you can send an e-mail response as part of SQL Server Agent job, and you can also send e-mail manually by using sp_send_dbmail stored procedure.