Send notifications for an Amazon RDS for SQL Server database instance by using an on-premises SMTP server and Database Mail
Created by Nishad Mankar (AWS)
Environment: PoC or pilot | Technologies: Databases; Management & governance | Workload: Microsoft |
AWS services: Amazon RDS |
Summary
Database Mail
Prerequisites and limitations
Prerequisites
An active AWS account
An Amazon RDS DB instance running a Standard or Enterprise edition of SQL Server
The IP address or hostname of the on-premises SMTP server
An inbound security group rule that allows connections to the Amazon RDS for SQL Server DB instance from the IP address of the SMTP server
A connection, such as an AWS Direct Connect connection, between your on-premises network and the virtual private cloud (VPC) that contains the Amazon RDS DB instance
Limitations
Express editions of SQL Server aren't supported.
For more information about limitations, see Limitations in Using Database Mail on Amazon RDS for SQL Server in the Amazon RDS documentation.
Product versions
Standard and Enterprise editions of SQL Server versions supported in RDS
Architecture
Target technology stack
Amazon RDS for SQL Server database instance
Amazon Route 53 forwarding rule
Database Mail
On-premises SMTP server
Microsoft SQL Server Management Studio (SSMS)
Target architecture
The following image shows the target architecture for this pattern. When an event or action occurs that initiates a notification or alert regarding the database instance, Amazon RDS for SQL Server uses Database Mail to send an email notification. Database Mail uses the on-premises SMTP server to send the email.
Tools
AWS services
Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server helps you set up, operate, and scale a SQL Server relational database in the AWS Cloud.
Amazon Route 53 is a highly available and scalable DNS web service.
Other tools
Database Mail
is a tool that sends e-mail messages, such as notifications and alerts, from the SQL Server Database Engine to users. Microsoft SQL Server Management Studio (SSMS)
is a tool for managing SQL Server, including accessing, configuring, and administering SQL Server components. In this pattern, you use SSMS to run the SQL commands to set up Database Mail on an Amazon RDS for SQL Server DB instance.
Epics
Task | Description | Skills required |
---|---|---|
Remove Multi-AZ from the RDS DB instance. | If you're using a Multi-Zone RDS DB instance, convert the Multi-AZ instance to a Single-AZ instance. When you have finished configuring Database Mail, you will convert the DB instance back to a Multi-AZ deployment. The Database Mail configuration then works in both the primary and secondary nodes. For instructions, see Removing Multi-AZ from a Microsoft SQL Server DB instance. | DBA |
Create an allow list for the Amazon RDS endpoint or IP address on the on-premises SMTP server. | The SMTP server is outside of the AWS network. On the on-premises SMTP server, create an allow list that permits the server to communicate with the outbound endpoint or IP address for the Amazon RDS instance or the Amazon Elastic Compute Cloud (Amazon EC2) instance hosted on Amazon RDS. This procedure varies from organization to organization. For more information about the DB instance endpoint, see Finding the DB instance endpoint and port number. | DBA |
Remove port 25 restrictions. | By default, AWS restricts port 25 on EC2 instances. To remove the port 25 restriction, do the following:
Note:
| General AWS |
Add a Route 53 rule to resolve DNS queries for the SMTP server. | Use Route 53 to resolve DNS queries between your AWS resources and the on-premises SMTP server. You must create a rule that forwards the DNS queries to the SMTP server domain, such as | Network administrator |
Task | Description | Skills required |
---|---|---|
Enable Database Mail. | Create a parameter group for Database Mail, set the | DBA |
Connect to the DB instance. | From a bastion host, use Microsoft SQL Server Management Studio (SSMS) to connect to the Amazon RDS for SQL Server database instance. For instructions, see Connecting to a DB instance running the Microsoft SQL Server database engine. If you encounter any errors, see the connection troubleshooting references in the Related resources section. | DBA |
Create the profile. | In SSMS, enter the following SQL statement to create the Database Mail profile. Replace the following values:
For more information about this stored procedure and its arguments, see sysmail_add_profile_sp
| DBA |
Add principals to the profile. | Enter the following SQL statement to add public or private principals to the Database Mail profile. A principal is an entity that can request SQL Server resources. Replace the following values:
For more information about this stored procedure and its arguments, see sysmail_add_principalprofile_sp
| DBA |
Create the account. | Enter the following SQL statement to create the Database Mail account. Replace the following values:
For more information about this stored procedure and its arguments, see sysmail_add_account_sp
| DBA |
Add the account to the profile. | Enter the following SQL statement to add the Database Mail account to the Database Mail profile. Replace the following values:
For more information about this stored procedure and its arguments, see sysmail_add_profileaccount_sp
| DBA |
(Optional) Add Multi-AZ to the RDS DB instance. | If you want to add Multi-AZ with Database Mirroring (DBM) or Always On Availability Groups (AGs), see the instructions in Adding Multi-AZ to a Microsoft SQL Server DB instance. | DBA |
Related resources
Using Database Mail on Amazon RDS for SQL Server (Amazon RDS documentation)
Working with file attachments (Amazon RDS documentation)
Troubleshooting connections to your SQL Server DB instance (Amazon RDS documentation)
Can't connect to Amazon RDS DB instance (Amazon RDS documentation)