In the past I've blogged a bit about the importance of enabling your SQL Servers to be pro-active in terms of alerting you via email when something bad has happened or requires attention. I've also addressed a very common 'gotcha' that prevents the SQL Server Agent from raising email alerts/warnings when jobs fail or when configured alerts have been triggered.
But I've never really covered any specifics about setting up Database Mail.
Enabling and Configuring Database Mail
Happily, setting up Database Mail is FAIRLY straight-forward, and Microsoft has even documented much of the process online. In my estimation, though, the documentation would be a bit better if it was accompanied by screenshots. My guess, though, is that since Microsoft is making this documentation available in MULTIPLE languages, screenshots would make the documentation process substantially more complex and expensive to manage. Otherwise, the documentation that Microsoft provides is pretty comprehensive (even if it is a bit terse).
That said, a couple of screenshots might help. For starters, you’ll want to enable (or check on) Database Mail via the Management Node in SQL Server Management Studio – against the server you’re configuring:
Of course, if you’ve never configured Database Mail before, you’ll likely get a warning/confirmation dialog that looks similar to:
Which is just asking if you want to configure (or enable) Database Mail XPs (or specialized, eXtended Procedures (XP) designed to enable SQL Server to talk to mail servers). And while there's always the risk of increasing your surface attack area any time you open up additional functionality like enabling XPs, the reality is that the risk of compromise here is QUITE small—to the point where enabling XPs on MOST servers is something that’s 'totally' safe. That said, if there's a zero-day exploit or other problem discovered against this functionality, then you'll have to patch accordingly and so on. (So, in other words, what we’re primarily dealing with here is that Microsoft has taken a best-practices approach to forcing enablement of these features to be an explicit operation.)
From there, things are pretty straight-forward in terms of walking your way through the various wizards and options (unless you’re like me and don’t actually READ the options).
In fact, at this point, the actual UI or setup is pretty simple, as all you really need to do from this point is:
- Set up an SMTP account.
- Set up a Profile that wraps (or abstracts) that account and which allows SQL Server to be ‘bound’ to various SMTP accounts WITHOUT forcing your apps, alerts, and so on, to be ‘hard-coded’ against a single SMTP account.
From here you'll need your SMTP credentials and configuration details, and will then just need to put them into the appropriate Database Mail 'screens' to get things working. And, note, while it typically makes sense to set these up against your own domain or SMTP servers, you CAN actually set Database Mail to work against pretty much any mail server—as outlined here.
General Tips for Setup and Debugging
Otherwise, everything else you'll do when setting up Database Mail should be pretty self-explanatory. That said, I do have a couple bits of advice and guidance to offer in terms of setup and troubleshooting.
- Security. Once you've got this all set up, do NOT forget to allow the SQL Server Agent to 'talk' to Database Mail—otherwise, you'll find that only your code/applications can do so and you will NOT get alerts when jobs fail or when alerts are raised/etc. For info on how to enable this functionality, check out my previous post on how to avoid 'failed to notify operator errors.'
- Simplicity. In terms of configuration, I’d recommend that you try to be as abstract, or general, as possible in setting up your Profiles. Because while you CAN actually set up multiple/different profiles (so that, say, 'reports' could be sent by/via one profile (or email address) and alerts could be sent via another, and so on), my experience is that MOST SQL Servers really only need a single profile. To that end, I commonly recommend setting it up and calling it something VERY generic, like 'General' or something similar. (In a similar manner, when it comes to setting up operators, UNLESS you really need to keep tabs of different people on different shifts and at different times for responding to events, I commonly recommend just setting up a 'General' operator as well – that just drops emails into a 'generic' firstname.lastname@example.org (or something similar) email address/alias that keeps things MUCH easier to manage. (So, in other words, while you CAN customize Database Mail and operators to address complex situations or needs, try not to make things any more complex than they absolutely need to be.)
- Troubleshooting. Once you’ve finished setting up your SMTP details and created a Profile, you can right click on the Database Mail node in SSMS and send a test message. Far too often, unfortunately, I find that these test emails don’t work out as planned—right away. Meaning that you’ll need to do some troubleshooting. And while troubleshooting reasons for why SMTP messages don’t go through to inboxes AFTER they've been successfully logged to the SMTP server is beyond my pay grade (and outside the scope of SQL Server), it's worth noting that SQL Server DOES surface a Database Mail log that I find is VERY helpful in diagnosing connectivity and SMTP problems during initial setup.