Troubleshooting Database Mail - Part I

I've been wrestling with Database Mail lately - working through a list of servers and instances, making sure that they all send emails in the event of a problem.  Most did, some didn't.  Some had never been set up; some had been set up to use an old mail server which was about to be decommissioned; and one was apparently set up perfectly - except that it didn't actually work  <sigh>.

My purpose in setting up database mail on a server is to send warning messages to myself and my team in case anything goes wrong.  If a backup job fails, for example, I want to know about it.  Some people set up a corresponding message for when a job succeeds - this is up to you, but my attitude is that things normally work, and I don't plan to worry unless they don't.  I usually run a job under supervision the first time, so I can see whether it runs or not.  

Let's start with setting Database Mail up from scratch.  This came in with SQL Server 2005, replacing the cleft stick and smoke signals used by earlier versions.  (What?  you still use SQL Server 2000?  I don't have personal experience, but this may help).  There's a perfectly good wizard for doing this, and in general I suggest that you follow the wizard.

Starting in SSMS then, expand Management by clicking on the + symbol.
Right click on Database Mail and select Configure Database Mail - up pops the wizard.

The Welcome screen is optional - if you don't want it, click on the Skip this page in the future box.  Press Next.

The default is best - Set up Database Mail by performing the following tasks...  The other options are really there for making changes and additions subsequently, although you probably could set things up from scratch this way.  But you are using the wizard because you want to do things the easy way, aren't you?  I certainly am.

Assuming this is your first time setting up Database mail, up pops this box:

Well, fairly obviously, Yes I do!  If not, you might as well give up now...

Here's the new profile dialog box:

Profile name - I usually use the server name - it's as good as any other.  And ditto for the description.  But if you are going to have several mail profiles per server, you'll need to give this a bit more thought.  

Then click on Add to add a new SMTP (outgoing mail) account,  

Again, use the server name for the account name and description, and also for the Display name.  The email address and company name need to be valid addresses, but you don't really want any emails being sent to them, hence noreply@...  My purpose in setting up database mail on a server is to send warning messages to myself and my colleagues in case anything goes wrong.  I don't then send a message back to the server - this only encourages the server gremlins.  

The bit that really matters here is the name of the outgoing mail server (SMTP).  Your network team will be able to tell you the name of the mail server, the port it uses (default is 25) and whether it requires a secure connection.  Notice the name: mail.companyname.com; not mail@... - it's a server name, not an email address.  

Click on OK and it takes you back to the New Profile screen, this time with the Account name added.  Not very exciting, but for completeness, here's what it looks like:

Now, click on Next.  This is the Manage Profile Security page:

Make the Profile public by ticking in the box.

Make it the default profile by selecting Yes from the pull down menu.  The pull-down doesn't show until you click in the box, but it's there, I promise.  

You can set up private profiles that can only be accessed by specific users.  But we don't need that here.

Click on Next and you get a chance to configure the system parameters.  Most of the time, the defaults are fine, so don't worry about it.

Click on Next and it gives you a chance to review the choices you have made:

Click on Finish and it does what you told it to do.  Look, you only have yourself to blame...  

Close, and test.  

The easiest way to test is by right clicking on Database Mail and selecting Send a Test Email.

It should have the profile name you entered before, although if you need several profiles you will need to use the pull down menu.  Put your own email address in the To: box, the subject and Body are fine (although you can change them if you want) and press Send.  Sit and twiddle your thumbs for a few moments and a test email should appear in your inbox.  

What if it doesn't?  What you want is Troubleshooting Database Mail - Part II
Stay tuned to this channel, database fans...