Database Mail
Database Mail is
designed for reliability, scalability, security, and supportability.
·
Database Mail uses the
standard Simple Mail Transfer Protocol (SMTP) to send mail. You can use
Database Mail without installing an Extended MAPI client on the computer that
runs SQL Server.
·
Cluster support.
Database Mail is cluster-aware and is fully supported on a cluster.
·
Multiple accounts:
Each profile can contain multiple failover accounts. You can configure
different profiles with different accounts to distribute e-mail across multiple
e-mail servers.
·
SQL server database
engine define rules for attachment, type and size limitation.
·
Database Mail keeps
copies of messages and attachments sent in the msdb database.
You can easily audit Database Mail usage and review the retained messages.
Database mail architecture
Database
Mail is designed on a queued architecture that uses service broker
technologies. When users execute sp_send_dbmail, the
stored procedure inserts an item into the mail queue and creates a record that
contains the e-mail message. Besides storing the sent, or unsent, e-mail
message, Database Mail also records any e-mail attachments in the system
tables. You can view mail log using following commands on msdb database
SELECT *
FROM sysmail_mailitems
SELECT *
FROM sysmail_log
Setting up Database Mail
Database Mail can be setup using SQL Server management studio and scripts. I am doing this using management studio
In database instance , go to the management and Configure database Mail.
If database mail is disabled, you will get message to enable it before configuration. You need to have Admin rights on msdb database.
During data mail wizard, You will setup your Profile name and SMTP setup. You can use Windows Auth and password to verify email account.
As a last step you can configure File size, retry attempts etc as shown in figure, After finishing all these steps, You will receive successful installation message.
To verify your configuration. You can send test email by right clicking on your profile name and press Send test email. You can Audit you mail history by using sysmail_log and review your profile setting using sysmail_mailitems table in msdb.
Once you finish setting up Database Mail profile, To send SQL Job Notification or Alert you have to create new Operator.
Creating Operator
Create new operator using
SQL Server management studio.
You can setup schedule for your Operator by using Pager on Duty Scheduler
Setup SQL Agent
You have to enable and setup your Profile account along with SQL Agent to send email.
Press Include body of email in notification message will send message detail as email in case of error or alert. You can setup Fail-safe operator in case there is any problem in primary profile.
Once all setting has been completed. restart SQL Agent service to implement changes.
Creating Alert
Alert will send an email when TEMPDB database gets
larger than 0 KB.
Right Click Alerts and select New Alert
On the General tab properties are set to
In Response Tab, You can run Job to accommodate Alert changing i.e Database shrinking job or you can generate email notification.
You can add error message in Alert body or Custom message can also be added
You can see last email time to verify either Alert is working fine. You can also view history using command given above in msdb.
Stop the Test
To stop the Alert from being sent every two minutes, you have two
options disable the alert or provide a more appropriate size and response
times.
To disable alert
- Right click on
Alert name and select Disable
Email SQL Job Status
You can attach operator to send email notification on the failure, successful or completion of any SQL Agent job. In following image you can see email notification as well as Windows event log is being written on different status of SQL Job.
I tried to cover all possible scenario's about Database Mail setup and attaching it to send email notification in different ways. Normally we find all this information in number of posts. Hopefully it will help.
