Email In Your Database
There are many ways to enable Email within a SQL Server database and ways to take advantage of Email. If I don’t have a third party database monitoring tool, I like to use Email from SQL Server to notify me of events I need to address such as disk space, database files becoming full, Expanding Data Files, etc.
I have seen times when Email is used in a database where it is not the best solution. For example, I have seen systems where an Email message coded to be sent in a table trigger when a record is modified (Insert, Update, Delete). This may seem like a good design in that the need to send the Email is based on the modification of the data.
While the trigger design works nicely, there are performance problems that may arise. How long does an Email message being sent wait in order to timeout? I don’t know; but if it is longer than it takes to save a record in a database, it is too long. The primary function of your database server is to store and retrieve data…it can’t do that if it is waiting for an Email to be sent. You transaction and locks on the table being modified are held for the duration of sending the Email.
If you must send Email from your database, use a user defined Queue table that you populate from the trigger. Then you work is saved and the information necessary for the Email is stored to be process by another thread.
I know this isn’t event driven in this case, because another process will have to poll the queue table for new items. However, it reduces contention and locks while storing data. If you really need to have an event driven model, consider implementing the Broker Services in SQL Server.
I guess the better question would be, do I really want to take my precious resource for persisting my data and use it to send Email? I’m not saying the need for notification is not valid. I’m asking if there isn’t a better way?
How do you handle notification in your designs? Share with us your methods for sending notifications based on data stored in your SQL Server Database. Send your comments to btaylor@sswug.org.
Cheers,
Ben