There are conversations that every DBA wants to avoid. They often begin like this: “The data disk on the SQL Server crashed last night. The database backups have apparently been failing for several days and our only backup file is a week old. How come we didn’t we know about the backup failures?”
Or like this:
“The transaction log disk filled up last night and the database stopped working. the site’s been down since 4am. Why didn’t we know yesterday that the disk was nearly full?”
The easy way to avoid these inconvenient conversations is to let SQL Server send you an email when something goes wrong or is about to go wrong. You don’t need to buy a third-party system to alert you to problems. You can do it in SQL Server just by enabling Database Mail and setting some alerts.
SQL Mail Bad, Database Mail Good
Database Mail may be the most under-used feature in SQL Server. It is simple to set up and very useful but I don’t often find it used at the sites I visit. This may be because its predecessor SQLMail had such a bad reputation. So, let’s clear that up before moving on.
SQL Mail not only sent out alerts and other messages programmatically, it could also receive email in the form of TSQL commands. It would execute the command and return the results to the sender in an email. This vulnerability was the target of several nasty hacker exploits incuding the infamous SQLSlammer worm.
SQL Mail also required the installation of a MAPI2 mail client on the sql server. At the time, the simplest way to do that was to install Microsoft Outlook on the SQL Server. This worked out very well for Microsoft but left users wishing for a simpler, standards-based architecture.
Database mail fixes both of these issues. It is a send-only system and does not require a mail client installed on the server. You simply point Database Mail at any SMTP (outgoing mail) server. The server can be local or remote, so long as you have an email account there. If you have email in your organization, you may already have access to an SMTP server that will work. If you are just experimenting, you could probably use a free hotmail account. I haven’t tried that, but it should work.
Alerts
Database mail is the principal delivery method for SQL Server Alerts. Alerts are warnings that can be delivered when certain events occur or certain resource levels are exceeded. for example an alert can be sent automatically when free space on a disk falls below a certain level or when a scheduled job fails. Alerts can be defined for just about any SQL Server event imaginable. Some custom alerts might require a bit of programming, but most can be set up with simple point and click.
But Alerts are a topic in themselves. We will cover them in more detail in a later article. For now we are just examining the delivery method, Database Mail, which does more than deliver alerts.
Simple Ad Hoc Reporting
Database Mail can also be used to send ad hoc emails from a TSQL batch or procedure using sp_send_dbmail. This procedure can also return the results of a query as an email message. You can do this by passing the query string into the procedure as a parameter (illustrated below). this gives you the ability to deliver simple text reports quite easily.
For example, suppose that the Vice-President of Sales wants to see yesterday’s sales every morning when he boots up his PC. This doesn’t require a complex reporting environment. Database Mail can have this information waiting in his inbox each morning. Just schedule a job that executes a query something like this one:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N’VP@yourdomain.com’
,@body=’Message Body’
,@subject =’Yesterday Sales By Product’
,@profile_name =’YourDBMailProfile’
,@query = ‘select p.Name
, SUM(od.LineTotal) as TotalOrderAmount
, SUM(od.orderqty) as Units
from Adventureworks.Sales.SalesOrderDetail od
join Adventureworks.Sales.SalesOrderHeader o on o.SalesOrderID = od.SalesOrderID
join Adventureworks.Production.Product p on p.ProductID=od.ProductID
where o.OrderDate > ”2002-07-01 00:00:00.000”
group by Name
order by TotalOrderAmount desc’
,@attach_query_result_as_file = 1
,@query_attachment_filename =’Results.txt’
If you enable the @attach_query_result_as_file parameter, the query results are sent as an attachment. If not, they are sent as the body of the message.
This query will run in the AdventureWorks sample database if you want to experiment with it. I hard-coded a date in this query but you will have to do a little date arithmetic to get yesterday’s results from live data. Also note the two single-quotes (Not a double-quote) that are necessary when you quote a text value within the @query parameter.
Other Uses
As well as returning query results, you can use Database mail to do many other things. The most obvious use is to send email to lists of recipients selected from email addresses in your database. For example, you might write some code that would send different messages to clients based on their account status. If the account is current they get a thank you. If payment is overdue they get a reminder.
You can also add a job step to a scheduled job that will send email to various people with information about job status and results. Alerts can do a similar thing, but you have greater functionality when you create a custom job step. For example, you can email to people who are not registered as agents to receive alerts. You also have a finer control over what information is sent.
I am certain you can imagine a number of other ways that this functionality can be useful to you.
Only Two Moving Parts
Conceptually, Database Mail involves only two objects, the Account and the Profile.
Account: The Database Mail account contains information about an email account on the SMTP server that you will use to transmit the messages. To create a Database Mail account you need the address of the SMTP server (smtp.hotmail.com, for example). You also need the username and password for the email account, the email address and perhaps some authentication details. It is the same information you need in order to set up an email account in Outlook or any other email client.
Don’t be confused by the ambiguous use of the word ‘Account’. When you create a Database Mail account you are not creating an email account. A Database Mail account is a structure in SQL Server that holds login information about an actual email account that already exists on the specified SMTP server. If it doesn’t already exist, you should create it before you set up Database Mail.
Profile: This is an identifier for the sender of database mail. Not surprisingly, the profile name is what you put into the sp_send_dbmail @profile_name parameter. You will also pick the profile name from a list of all available profiles whenever you create an email alert.
A profile is essentially a named set of one or more accounts. Multiple accounts in a profile allow for redundancy but they are not required. If the SMTP server in your first account cannot be reached, Database Mail will attempt to use the second, and then the third, etc. if they are defined.
Implementing Database Mail
Database Mail can be set up using a simple wizard, or masochists may prefer writing the TSQL code to configure it.
The wizard automatically enables Database Mail on the server, but if you write code to set things up, don’t forget to also enable Database Mail at the server level. It is disabled by default.
You can enable database mail in the Surface Area Configuration tool in SQL 2005 or in the Surface Area facet if you are using SQL 2008.
Assuming you prefer doing it the easy way, click on the Management folder in the SQL Management Studio object browser . Then double-click on Database Mail. That will launch the wizard. You will need to have the SMTP server and account information handy.
The wizard is pretty simple but if you want step-by-step instructions for using the wizard or help with the TSQL scripting, click here:
Conclusion
I am puzzled why Database Mail is not used more universally. It is easy to set up, it involves no expense beyond the cost of theSQL Server license you already own and it can help you avoid all kinds of serious trouble. What’s not to like about it?
As always, if you have opinions, questions or comments about this article, I’d like to hear them.