SQL Server Database Mail configured with the Send Grid Email Service in the Azure Market Place

Problem

How can I setup database mail for SQL Server 2016 running on Azure's Infrastructure As A Service (IAAS)?

Solution

The Azure Marketplace has the SendGrid email delivery service that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs.

Business Problem

Your manager has asked you to install and configure database mail for the test system you just built. This tip builds upon the virtual machine that was created for the Contoso Retail BI sample database. Please read this previous tip for more details.

High Level Tasks

There are four steps to accomplish this simple task.

Install and configure the SendGrid service.
Validate service settings.
Install and configure database mail.
Send a test message to confirm the deployment.

Create Send Grid Service

There are several steps that need to be executed to install and configure this service. First, log into the Azure portal and bring up the Azure dashboard. On the dashboard, click the plus sign (new service) under the main menu and search the Azure Marketplace for the SendGrid email delivery service. Double click the selection to move onto the next screen (blade).

The second step allows the end user to read more about this service. If your application sends less than 25K emails a month, the service is absolutely FREE. That is a great deal! Find out more information about pricing options if your company will exceed this limit. Click the create button to move onto the next screen (blade).

The third step is to fill in all the required information and accept the terms of the agreement.

The name of the service, grid4mail, is the object name that will show up under your subscription. I choose a strong password and placed the service in the same resource group, rg4tips16, as the virtual machine SQL16DEV. Contact information, pricing tier and agreement acceptance are all required to create the service. If you are satisfied with your choices, click the create button. This will close the screen and process your request.

Validate Send Grid Settings

It is always wise to check the settings of an Azure object that you have just deployed. On the Azure dashboard, the click the cube image (resource groups) under the main menu. Double click the name of the only resource group. The following screen should show up. We can see that the grid4mail service has been deployed under the rg4tips16 resource group. Double clicking this item brings up details on the service.

The general section under the SendGrid setting supplies us with the information we entered when creating the service. The properties screen can be used to determine the pricing tier and data center location of the service.

The configurations screen can be used to get the User Name and SMTP Server URL. These are very important pieces of information that we will need when configuring database mail. Use the copy icon to save this information to a handy text file opened in Notepad.

Last but not least, the contact screen shows the end user information that was entered before creating the service.

Install SQL Server Database Mail

The next set of tasks require you to use the remote desktop protocol (RDP) to log onto the virtual machine named SQL16DEV and launch SQL Server Management Studio (SSMS).

The quickest way to setup and configure database mail is to execute T-SQL scripts. By default, database mail is not installed. Please see sp_configure for server options that you can set via code.

The TSQL script below turns on 'show advance options' and enables 'database mail extended stored procedures'.

/*
   Turn on database mail
*/

-- Select the correct database
USE [msdb]
GO

-- Just shows standard options
sp_configure
GO

-- Turn on advance options
sp_configure 'show advanced options', 1;
GO

-- Reconfigure server
RECONFIGURE;
GO

-- Turn on database xp's
sp_configure 'Database Mail XPs', 1;
GO

-- Reconfigure server
RECONFIGURE
GO

The first step in configuring database mail is to create a mail account.

The sysmail_add_account_sp system stored procedure will do this work for us given the correct inputs. Make sure you supply the @mailserver_name and @username that you copied from the SendGrid configuration screen. This information is saved in our text file. The @password is the string you supplied when you created the service. The @email_address is the actual from address while the @display_name is what is shown in Outlook. The rest of the parameters are pretty straight forward.

The T-SQL script below creates a mail account called act_Default_Email.

/*
   Creating mail account with Send Grid SMTP server
*/

-- Create a Database Mail account 1
EXEC msdb.dbo.sysmail_add_account_sp
  @account_name = 'act_Default_Email',
  @description = 'Mail account for use by all database users.',
  @email_address = '[email protected]',
  @replyto_address = '[email protected]',
  @display_name = 'SQL SERVER (IAAS-SQL16DEV)',
  @mailserver_name = 'smtp.sendgrid.net',
  @username = '[email protected]',
  @password = 'enter your unique password';
GO

-- Show the new mail accounts
EXEC msdb.dbo.sysmail_help_account_sp;
GO

The second step in configuring SQL Server database mail is to create a mail profile.

The sysmail_add_profile_sp system stored procedure will perform this action given the correct inputs. Mail profiles allow a database administrator to logically group one or more accounts into a mail service. In our example, we are using only one SMTP service. Therefore, it is susceptible to outages by our one vendor. Adding additional accounts/vendors makes the mail service more resilient.

The T-SQL script below creates a mail profile called prf_Default_Email.

/*
   Creating a mail profile
*/

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'prf_Default_Email',
  @description = 'Profile used for administrative mail.' ;
GO

-- Show the new mail profile
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

The third step in configuring database mail is to link our mail profile to one or more mail accounts. In our example, we have one mail account. The sysmail_add_profileaccount_sp system stored procedure will execute the task for us given the correct inputs.

The T-SQL script below links the prf_Default_Email profile to the act_Default_Email account.

/*
  Linking the mail profile to the account
*/

-- Add the account 1 to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'prf_Default_Email',
  @account_name = 'act_Default_Email',
  @sequence_number = 1 ;
GO

-- Show the link between profile and accounts
EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'prf_Default_Email';

The fourth step in configuring database mail is to give public access to the mail profile. This allows database users to send mail. The sysmail_add_principalprofile_sp system stored procedure will complete the action for us given the correct inputs.

The T-SQL script below sets properties of the prf_Default_Email profile to public and default.

/*
   Given public access to profile
*/

-- Grant access to the profile to all users in the msdb database
EXEC msdb.dbo.sysmail_add_principalprofile_sp
  @profile_name = 'prf_Default_Email',
  @principal_name = 'public',
  @is_default = 1 ;

-- Show the new default profile
EXEC msdb.dbo.sysmail_help_principalprofile_sp

Again, it is prudent to verify the information that you coded in the T-SQL script matches the server settings. By browsing the object explorer in SQL Server Management Studio, you will find the management node that contains the Database Mail item. Right clicking the item allows you to configure Database Mail. Choose the manage Database Mail accounts and profiles to bring up the following screen. You now can double check your work.

Validate SQL Server Database Mail

The easiest way to validate this whole solution is to send an email message to an Outlook account from the database server. The following T-SQL code sends an email to my [email protected] email account. There are many different parameters that can be used with the sp_send_dbmail stored procedure. This includes sending HTML formatted messages, adding attachments and setting the priorities of the message. Use the above hyperlink to learn more.

/*
   Send test message
*/

-- Plain text message
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'prf_Default_Email',
  @recipients = '[email protected]',
  @body = 'The stored procedure finished successfully.',
  @subject = 'Automated Success Message' ;
GO

The image below shows the test message arriving correctly at the destination.

Summary

Today's information technology specialists have to be versed in both old and new techniques. Installing and configuring the SendGrid email delivery service is a new task that you can add your tool belt. However, deploying SQL Server Database Mail has been a task performed by Database Administrators for years. Knowing when and how to combine your skill sets to solve a business problem is key for today's employees.

Although the focus of the tip was on enhancing the SQL Server 2016 Virtual Machine, the SendGrid service can be leveraged by any product or language that supports a SMTP relay. For instance, an Automation Batch job that executes a PowerShell workflow can use this same service to send emails.

Next Steps

The next step is to install basic alerting on our test server.
How can we configure alerts for severity levels 17 to 24?
Please see Brian Kelly's tip on alerting.

Alerting is dependent upon MSDB database being available.
Is there a way to make sure the DBA's get alerts when this database is down?
Please see Ken Simmons's tip on the failsafe operator.

About the author

John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips