Sql-server – Pros and Cons of SQL Server Database Mail

sql-server-2008

I'm planning to use SQL Server Database Mail.

I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new receiver records added to this table.

I would like to know pros and cons from the experts who already have used, or are aware of, this Database Mail feature. Is this the correct system for sending mails, or is there better options to send mails from SQL Server?

Update: Above mentioned table is a kind of queue table for mails to be sent. There are many operations going on in the application and all these operations need to send mail once each transaction is complete. Hence, they store that information in this table. Later on, at every minute, we retrieve data from this table and send mails accordingly, marking that record as sent. I hope this better clarifies my requirements.

Best Answer

It seems that the two things you are trying to accomplish are (a) a single process for sending mail and (b) the ability to check the status of sent e-mail messages later.

If this is your requirement, it is totally acceptable to have a table to which various operations write simple information about e-mail notifications which are sent. However, you do not need to store the whole message or the message status.

From the Technet entry for sp_send_dbmail:

When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message.

And, from an MSDN walkthrough titled How to: Check the Status of E-Mail Messages Sent With Database Mail (Transact-SQL):

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems views of the msdb database. The Database Mail external program logs activity and displays the log through the Windows Application Event Log and the sysmail_event_log view in the msdb database. To check the status of an e-mail message, run a query against this view. E-mail messages have one of four possible statuses: sent, unsent, retrying, and failed.

All you need to store, then, is the ID of the message which was queued, along with any application-specific information (such as which operation generated the notification, or the reason the notification was generated). When you are ready to get the status of a message, you can join back to one of the views described in the walkthrough above using the mailitem_id you saved to your table. If you do not have such application-specific data, there is no need for a custom tracking table at all, since you can search the sysmail* views by recipient.

As for the "single process for sending mail" approach, I have used that abstraction myself and I don't see anything wrong with it, though I would offer some suggestions and caveats.

  • If the reason you are polling the table every minute is that the messages are time-sensitive, this is probably not a good approach and you really should consider moving the sp_send_dbmail call back into to the operation that needs to send notifications.
  • When you call sp_send_dbmail inside a transaction, the message is never sent if the transaction is rolled back. I mention that because you said you want the e-mail to be sent only when the transactions succeed.
  • If you do need to keep some kind of "message builder" table to which various operations submit pending notifications, there is no need to keep records in this table once they have been processed. From that point forward, all the data you need should be available in the sysmail* views, combined (if necessary) with the tracking table above.

I would like to provide an example where I have used this kind of abstraction. I have an application in production for which notifications are generated several times a minute, but users only need to receive these notifications once per day (or, in some cases, a few times per day). Each operation which needs to send a notification submits the message to the notification queue, and once it is submitted, the operation does not have any need to know how the notification is sent out. The centralized messaging process determines whether the message needs to be sent out immediately or should be aggregated with similar messages. For messages that can be aggregated, the messaging processor—which runs as a SQL Agent job—takes all the notifications for a single recipient and combines them once per day into a single e-mail.

Related Question