Sending mail via SQL Server has become a much more reliable process. Database Mail in SQL Server 2005 offers many improvements -- compared to its predecessor SQL Mail -- including its compatibility with SMTP servers. SQL Server expert Roman Rehak explains how to configure Profiles and Accounts within the Database Mail architecture for sending and monitoring email.
For many years, SQL Mail was the only built-in option for sending email from SQL Server. SQL Mail was plagued by numerous issues and problems, mainly because it was dependent on MAPI (Messaging Application Program Interface). In order to use SQL Mail, you were required to install Microsoft Exchange or Outlook on the database server. In some versions, mail would get stuck in the Outbox unless you kept Outlook running on the server around the clock. In later versions it was possible to set up SQL Mail to use an SMTP (Simple Mail Transfer Protocol) server (Outlook install was still required on the server) but sending mail would often leave the process hanging and SQL Server would need to be restarted.
Database Mail in SQL Server 2005 features many improvements over SQL Mail. The most important one is that Database Mail is no longer dependent on MAPI. It now uses the SMTP protocol and you can configure your SQL Server to use one or more SMTP servers in your organization.
Another improvement is that Database Mail works in the background. It uses Service Broker to queue up messages for asynchronous, out of process delivery. And if you work for a high-end shop, you'll like the fact that unlike SQL Mail, Database Mail is cluster aware and it works in the 64-bit version of SQL Server. It also has plenty of email options, including attachments, HTML format, importance.
Configuring Database Mail – Profiles and Accounts
Before we start setting up Database Mail, le...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
t's go over a few basic concepts of the architecture. Database Mail uses Profiles and Accounts for sending email. The fundamental object of Database Mail is an account. It contains everything that's required for sending an email: account name, account email address, reply-to email address, SMTP server and its port number and a few other parameters. For authentication, you can use Windows Authentication, Basic Authentication (with a specified username and password) or Anonymous Authentication if your SMTP Server does not require credentials. Basic Authentication actually allows you to send email using SMTP servers on external networks such as your Internet provider -- as long as you supply valid credentials. But it goes without saying that it's a good practice to keep your database servers behind a firewall where they're unable to communicate with the outside world.
Figure 1 shows an account configured to use the smtp22.mydomain.com server for sending email messages.
[IMAGE]
Figure 1: This Database Mail account specifies using the smtp22.mydomain.com server to send email. (Click on image for enlarged view.)
Now that we've covered accounts, let's look at profiles. A Database Mail profile is just a collection of accounts with sequence numbers. Figure 2 shows a profile called DBMailProfile1, containing two accounts.
[IMAGE]
Figure 2: This Database Mail profile, called DBMailProfile1, contains two accounts. (Click on image for enlarged view.)
When you send mail from the SQL Server database, you need to specify which profile to use. If you don't specify a profile name, Database Mail uses the profile marked as the default. Each profile can have one or more accounts, but the relationship is not exactly hierarchical, meaning you can use the same account in multiple profiles. The main purpose of having multiple accounts in a profile is to present alternatives in case the selected account is not able to send email, which improves the reliability of the email system.
When you have a new email in the queue, Database Mail first tries to use the last account that successfully sent an email – or the account with the lowest sequence number if no email has been sent. If that account fails, Database Mail tries to use the account with the next sequence number. Once it cycles through all the accounts without success, Database Mail pauses for a predefined amount of time (configurable in system parameters) and then begins trying again, starting with the lowest sequence number.
Steps to set up Database Mail in SQL Server 2005
Setting up Database Mail is fairly easy. You use the Database Mail Wizard to create and manage profiles and accounts and to set up Database Mail security and system parameters. Start the wizard from the Database Mail node located under the Management node in SQL Server Management Studio. I don't like the fact that there's no way to examine your existing profiles, accounts and settings in Management Studio; you have to run the wizard again and choose one of the "Modify" options to look at the existing settings. This can get very awkward if you are trying to copy settings from one server to another. Another downside is that there's no option for scripting profiles and accounts. So, if you need to repeat the same setup on multiple servers, you should run SQL Profiler and capture the commands used for creating Database Mail objects.
All Database Mail objects are stored in the msdb database. Before a user can send email, the user account must be added to a special database role in the msdb database called DatabaseMailUserRole. If your emails are not going out when they are sent using a non-sysadmin account, check to see if the account is a member of the DatabaseMailUserRole
role and add it to the role if needed.
After you've configured Database Mail, you can start sending email from T-SQL using the sp_send_dbmail stored procedure. This procedure accepts more than twenty parameters, and most of them are optional. Here is a simple call for sending a plain text email:
DECLARE @subject VARCHAR(100)
SET @subject = 'Test email. Sent time: '
+ CAST(GETDATE() AS VARCHAR(20))
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile1',
@recipients = 'roman@hotmail.com', @body = 'It worked!!!',
@subject = @subject
Since there are so many parameters with hard-to-remember names (and you will likely never use most of them), I recommend creating one or more wrapper stored procedures to simplify the email interface.
Database Mail stores all sent email in the msdb database. You can examine them by running the following query:
USE msdb
SELECT * FROM sysmail_allitems
ORDER BY mailitem_id DESC
Probably the most important column in this system view is the sent_status column. It contains one of these four values – unsent, retrying, sent and failed. If your email is not going out, examine this view to make sure the messages are getting queued up and that you don't have messages with sent_status other than "sent." If you notice messages with "failed" or "retrying" status, right-click the Database Mail node and select "View database Mail Log" to get more details about the errors.
This article describes the basics of Database Mail and provides guidelines for setting up email. You should be able to start configuring your servers to use this practical feature for sending email from T-SQL and from SQL Agent.
ABOUT THE AUTHOR:
[IMAGE]Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer for the Professional Association for SQL Server. Roman also serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
