Select Page

7 Steps to Setting Up Database Mail

JP Chen | | June 9, 2015

db_mailAs 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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS