Take My SQLMail, Please!

By Bill Wunder

Who would disagree that the ability to send email is essential to SQL Server. The need to know when a critical failure has occurred or to deliver a result set in a timely manner is endlessly valuable. On the other hand, the health and availability of the database undeniably outweighs the need for notification. No need to know that the water is boiling if the kitchen is on fire.

SQLMail is the out of the box answer to notification and Outlook 2000 is the vendors recommended mail client for SQL 2000 (For example see Q281293). SQLMail has burned me so many times I don’t think I will stick my hand back in those flames. If you're not having any failures that you can directly attribute to SQLMail or errors and not experiencing failures that seem to be manifestations of memory leaks and you're not running a standby server that requires a rename in the event of a failover you may not need to consider anything other than SQLMail. If, however, you are experiencing such behavior as the server just stops responding – perhaps the mouse won’t even move - and has to be restarted or you have to use the -g switch for reasons such as linked server queries fail with an "out of memory message" after the server has been continuously up for several days or your jobs with notification enabled hang in a “Pending Completion Operation” state you may want to consider chucking SQLMail and Outlook out the Windows.

Undoubtedly Microsoft will eventually solve what ever the issue de jour is with SQLMail, but I’m thinking as long as the strongly coupled connection between SQL Server and a desktop application with a divergent product lifecycle like the Outlook mail client remains, there will be a compounded stream of new issues, either with the interface or the mail client that will put the stability of the SQL Server at risk. I find it curious that Microsoft has wedding the enterprise database to a desktop application and continues to let the two products go down separate evolutionary paths. It’s like putting the Taj Mahal on stilts. I notice they had to come up with Notification Services to fill the needs at msn.com. Notification Services is built to work with an Simple Mail Transport Protocol (SMTP) mail relay. It doesn't read, it only sends mail. It is equally interesting that they have documented the use of Collaboration Data Objects (CDO) as a way to send email without using SQLMail in the Microsoft KnowledgeBase document Q312839. Sure does give me the feeling they know there is a problem, yet the replacement they recommend involves enabling the IIS on the SQL Server machine and using the sp_OAs to dispatch the emails. Seems like a move from a Mail API (MAPI) client application to an SMTP bloat-ware solution that will add memory contention to the SQL Server with it’s own set of know memory leaks to me (see my previous article “Automation and T-SQL” for my opinions concerning server stability and the sp_OAs in a high availability setting).

Even before Q312839 in our shop we began developing the concept of eliminating SQLMail dependencies from our SQL Servers. Over time we’ve seen an increased stability of our SQL Servers and we’ve also seen a few tools emerge that ease the transition away from the MAPI dependencies. Early on, the transition was made easier for us because we have an in house custom notification system - that can go head to head with Microsoft’s Notification Services add-in in terms of published scalability. The problems we experienced in using our notification system were it’s limitations in delivering a result set and the risk of a development miss-queue having a negative impact on the production environment. Our notification system is built with a relatively small email body limit so the only real option was to send all query results as attachments. We really wanted to be able to put the data in the body in most cases because there is a tendency to not look at an attachment in the middle of a busy workday. Too, we actually experienced a case where a developer sent about 60,000 emails in a T-SQL while loop during the busiest time of the production day only to discover that this will cause unacceptable queuing at the production SMTP server. We decided to look around for other ways to solve our SQLMail needs and leave the production application to do what it does so admirably: serve our customers. 

Looking around the Internet one can come up with a few solutions aimed at helping you and I move our SQL Server notifications toward an SMTP mail server other than the all Microsoft alternative offered in Q312839. A Google search for “SQL SERVER SMTP” came up with hundreds of hits. Rather than try to present an exposé of the available SQLMail alternatives, in the next couple of articles I’m going to describe a solution for you that does not depend on MAPI or SQLMail or even on one particular alternative. A solution that includes a replacement for the T-SQL xp_sendmail and the SQLMail features in the SQLAgent.  

We’ll take a look at a solution that is asynchronous, keeps a history of sent messages, maintains the kept history, uses a  standard user interface that mimics xp_sendmail, enhances the messages sent by the SQL Agent by including actual error messages rather than the generic failure message that SQL Agent notification sends, provides full support for SQL Agent Alerts and is adaptable to any SQLMail alternative technology you might decide to use. As you will see, our solution makes it very easy to use any underlying technology and even to change the underlying technology quite easily by centralizing the interface in a single stored procedure. In our shop we chosen the xp_smtp_sendmail extended stored procedure that is a no cost download from www.sqldev.net as the underlying SMTP relay agent to replace SQLMail. We've been pretty happy with the features of this .dll but have had a few problems getting it to work correctly with Microsoft's IIS SNMP service so we use a LINUX SMTP server. That might not be the best alternative for everyone. In our case we have a line of these servers and the ability to support them in house. I’ll leave it to you to select the best technology for your shop. After all, you’re the DBA.

Bill