SQL Server Database Mail feature

SQL Server Database Mail

This feature enables a computer running SQL Server to send outbound mail messages. It can be used for sending messages to DBAs  when an error or performance condition happens or if there is a change in some database objects. This is very useful and it was introduce to SQL Server 2005 in order to avoid the use of SQL Server Mail feature (already linked to MAPI), and to give SQL Server more independence from subjacent local machine services. This makes it easier and more reliable when sending messages from SQL Server instances.

Database Mail uses SMTP (Simple Mail Transfer Protocol) relay service on Windows Local machine for transmitting all mailed messages. Every time a mail message is sent by SQL Server, it is logged into a table in Msdb (database). On a regular basis (periodically) SQL Server Agent Executes and runs a mail send process, and all messages inside the send queue are forwarded using the appropriate mail profile.

Database Mail gives you a failover feature when sending e-mail messages, every single profile can use many e-mail accounts so if one of these e-mail server is down, there is always a way to send and guarantee that the message is sent and delivered to the recipient.

There are also public and private profiles. Public ones let all users to use them to send any message they want to be sent. On the other hand, Private profiles restrict users for sending messages, only SQL Server will use these profiles to send queued messages.

Also users can have access granted to their SQL Server Database Mail Private Profile explicitly. It is also mandatory, when sending messages to specify the public or private mail profile to be used (otherwise, SQL Server will use the profile you setup as default to send all these mail messages).

If your messages are located at Msdb but never delivered, please check out SQL Server Agent (it must be running to send all queued messages)

So, now we are ready to setup Database Mail in our SQL Server Instance, to do so:

1. Open SQL Server Management Studio, right click on the server instance you want to activate Database Mail, and select New Query, this will open a blank query window,
2. Type the following (or copy/paste) Transact-SQL code for enabling Database Mail Feature on your instance:

EXEC sp_configure 'Database Mail XPs',1

GO

RECONFIGURE WITH OVERRIDE

GO

3. At Object Explorer, open the Management node, right click Database Mail, and select Configure Database Mail,
4. Click Next on the welcome screen, then
5. Click Set Up Database Mail, do the following steps, when completed click Next,
6. Give a name to your profile, click Add button to select mail account settings,
7. Then fill in all the fields (Account Name, E-mail Address, Display Name, Reply E-mail, and Server Name). If you are using and internal mail server you have to specify Server Name (if no your internet mail server account will be used as default.
8. Chose the right SMTP Authentication mode for your organization and, specify username and password (basic authentication).
9. Click Ok, and Next…
10. Select the check box in the Public column next to the recently created profile, also set this profile to YES in the Default Profile column, click Next
11. Review all the settings (this is Configure System Parameters page), then click Next
12. Click Ok, and again click Next. Click Finish.
13. The final page should show that all four configuration steps completed successfully. Click Close.
14. Right click on Database Mail, and then select Send Test E-Mail
15. Select the Database Mail profile you just created, enter an e-mail address in the To: field, and click Send Text E-mail,
16. Go and check if your e-mail client received the e-mail you just sent.

Remember to keep up running SQL Server Agent, if you do not do so, all your sent messages will be held in the Msdb database, and will never be sent.

Best regards to you my friends,

Jaime Gibertoni, tweets to @jaimegibertoni