7 Steps to Setting Up Database Mail

As an alternative to SQL Mail, Microsoft suggest using Database Mail. They advise using Database Mail because SQL Mail will be removed in a future version of SQL Server, so using Database Mail in new development work should save you some future heartache.

Plus “Database Mail runs outside of SQL Server in a separate process, is scalable, and does not require Extended MAPI client components to be installed on the production server.”

But the real advantage of using Database Mail is that it can alert you on new database creation, notify you on Key Performance Indicator (KPI) changes, and can send you a report every morning on the quantities sold by product. The possibilities are near endless to keep you aware of what is going on in your databases.

Database Mail is available to any user of Gmail or similarly-structured email services. Once enabled and configured correctly, you will ready to start using Database Mail to automate notifications and reports that keep you up to the minute on the status of key performance indicators.

My newest white paper, Hands-On Guide to Automatic Notifications and Scheduled Reports with Database Mail, begins with setting up a Gmail or similar account for Database Mail, and then using that account to receive automated reports and alerts. But here is a shorter version to save you time.

Setting Up a Database Mail Account

The steps in setting up an account include establishing the following database settings:

@email_address
@display_name
@mailserver_name
@mailserver_type
@export
@username
@password
@enable_ssl

Setting Up a Database Mail Profile

Once the settings for an account are established, you can create Database Mail Profiles, which can contain multiple Database Mail accounts. The parameters for Database Mail Profiles include:

@profile_name
@description

Once a profile is set up, you can add Database Mail accounts to the profile using a simple system-stored procedure that assigns each account a sequential number within that profile.

Testing Your Database Mail Settings

You can send emails to the accounts listed as recipients using a system-stored procedure that allows the following variables:

@profile_name
@recipients
@body_format
@body
@subject

If you refer to the white paper, The 5 Hats Worn By Database Administrators, there is a script for testing the Database Mail set up. The scripts are in SQL, and can be used with Gmail or many other private and public email services and accounts. Check with the system administrator of your internal network for an email account and port number to use.

Listing Database Mail Accounts and Database Mail Profiles

It is essential for maintenance to be able to get a handle on database mail accounts, lest they start to accumulate alerts and reports. The white paper dives deeper into how to do this and provides the system-stored procedure for getting a list of Database Mail profiles and the accounts associated with them.

Once you’ve established a Database Mail account and you know how to maintain it, you can use it to make your life easier with alerts and reports. For example, you can establish an alert on inventory that is selling faster than the resupply schedule can handle, or you could generate a weekly report on inventory movers and shakers.

Instructions for this and more are all detailed in the white paper but hopefully this has given you some insight on how easy it is to set up Database Mail and begin using it to your advantage. For more solutions to common and advanced DBA related questions, head over to Datavail’s frequently updated blog.