AWS Database Blog

Powering up Database Mail on Amazon RDS for SQL Server – How Under Armour runs Database Mail on Amazon RDS for SQL Server

Update [11/04/2020]:  We’re happy to announce that Amazon RDS for SQL Server now fully supports SQL Server Database Mail. With the release of Database Mail for SQL Server, you can enable Database Mail seamlessly by using database parameter groups. Check this blog post for more information.

 

 

Database Mail is one of the heavily used features in Microsoft SQL Server. Database Mail enables you to send messages from the SQL Server to users by using an SMTP server. The solution described here will help you use Database Mail if your SQL Server workload is on Amazon RDS.

You can use Database Mail for:

  • Sending text messages
  • Sending query results or reports as text or file attachment
  • Sending alerts for errors or notifications
  • Sending SQL agent job status messages when jobs succeed or fail

Under Armour, an AWS customer, was looking to migrate the SQL Server workload to Amazon RDS for SQL Server and had concerns about Database Mail not being supported in RDS. This issue can be a show stopper if you’re trying to move your SQL workload to Amazon RDS.

I worked with the Under Armour Data Architect, Leonard Humphries, to address this issue and together we came up with this solution: We promoted the SQL Amazon EC2 instance to a centralized Database Mail hub. By doing this, we were able to tackle the Under Armour Database Mail issues. In this post, I explain our solution.

AWS services involved

  • Amazon RDS for SQL Server
  • Amazon Simple Email Service (Amazon SES) if you don’t have an existing SMTP server
  • Amazon Elastic Compute Cloud (Amazon EC2) instance with SQL Server. This instance must be in the same VPC as your Amazon RDS for SQL Server.

Steps to power Amazon RDS for SQL Server with Database Mail

  • Step 1: Setup Database Mail on an EC2 instance with SQL Server
  • Step 2: Setup linked server between Amazon RDS for SQL Server and your EC2 instance
  • Step 3: Call a remote sp_send_dbmail stored procedure that is hosted on your EC2 instance  from the Amazon RDS via linked server.

You can see an overview of the solution in the diagram here:

Step 1: Setup Database Mail on an EC2 instance with SQL Server

Enable Database Mail in SQL Server

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
Exec sp_configure 'Database Mail XPs',1
RECONFIGURE
GO

Configure Database Mail

  1. Connect to the EC2 SQL Server
  2. Click the plus symbol expand Management
  3. Right click Database Mail and select Configure Database Mail as shown in the below image
  4. Choose Next
  5. Select Setup Database Mail by performing the following tasks
  6. Choose Next
  7. Enter a name for your profile and add description if required as shown in the below screenshot
  8. Choose Add and enter the details. I am using SES as an email server here to send out the emails.
    • Enter the email ID for SQL Server from which the emails would be sent
    • Enter the SMTP server name. This is my SES server URL.
    • Enter the port number
    • Enter the authentication credentials

    SES has sending limitations, which include an email size limit of 10 MB. This limit does not apply if you use your own on-premises or self-managed mail server.

  9. Choose Finish
  10. Choose Next
  11. Under Manage Profile Security, make the profile is public by selecting checkbox and default as follows.
  12. Choose Next
  13. Accept the default settings for System Parameters
  14. Choose Next > Finish > Close

If you want to use multiple RDS instances, repeat steps 7-14, to create more public profiles in conjunction with their instance-linked servers.

Step 2: Setup a linked server between Amazon RDS for SQL Server and your EC2 instance

My colleague Richard Waymire has written a very helpful blog post describing the process to setup a linked server between RDS for SQL Server and an EC2 instance with SQL Server. Please refer the post for the details and set up a linked server.

Step 3: Call a remote sp_send_dbmail stored procedure that is hosted on your EC2 instance from the Amazon RDS via linked server

Now run the sp_send_dbmail stored procedure on the Amazon RDS for SQL Server, as shown below . The procedure is present on the EC2 SQL Server, and run via the linked server.

EXEC [Ec2-SQL-Server].msdb.dbo.sp_send_dbmail   
@recipients = 'wasishai@amazon.com',  
@body = 'This email is from RDS',  
@subject = 'This email is from RDS';

Conclusion

This completes the solution to send Database Mail from RDS for SQL Server with the help of an EC2 instance and linked server connection. You can repeat the same type of solution if you need to send out email alerts for SQL Server job status. In that scenario, just create an extra step in your job. If you have any questions or feedback about this blog post, feel free to use the comments area here.

About the Authors

Wasim Shaikh is a Database Consultant with AWS Professional Services. He works with customers to provide guidance and technical assistance about various database and analytical projects, helping them improving the value of their solutions when using AWS.

 

 

Leonard Humphries was a Data Architect at UnderArmour and has worked at HomeAway.com and Match.com. Prior to entering the the IT field, he spent several years playing professional Football in the NFL and CFL.