How to get an email alert for a blocking on RDS SQL Server Instance

There are a few approaches you can use to set up email alerts for blocking on an RDS SQL Server instance:

1. Using Database Mail with SQL Server Agent: While RDS for SQL Server supports SQL Server Agent jobs, it doesn't natively support SQL Server Agent operators and notifications. However, you can create a workaround by:

• Setting up Database Mail on your RDS SQL Server instance
• Creating a SQL Server Agent job that runs periodically to check for blocking sessions lasting more than 15 seconds
• Adding a step in the job to send an email using the sp_send_dbmail stored procedure when blocking is detected

2. Using Amazon EventBridge with SNS:

• Create an Amazon EventBridge rule that monitors for specific RDS events
• Configure the rule to trigger an SNS notification when blocking events occur
• Subscribe to the SNS topic with your email address to receive notifications

3. CloudWatch Logs and Alarms:

• Export your SQL Server logs to CloudWatch
• Create a metric filter to identify blocking patterns in the logs
• Set up a CloudWatch alarm based on this metric
• Configure the alarm to send notifications via SNS when triggered

Since you've already configured the "blocked process threshold" parameter, you should ensure that the blocking information is being properly logged. Then you can use one of these methods to capture and alert on those events.

For the Database Mail approach, you would need to configure it to work with an SMTP server (either on-premises or a cloud-based email service) and create a SQL job that queries for blocking sessions and sends emails when they're detected.
Sources
Add support for Agent Operators and email notifications in RDS for SQL Server. | AWS re:Post
Alerts for specific RDS event id | AWS re:Post
Send notifications for an Amazon RDS for SQL Server database instance by using an on-premises SMTP server and Database Mail - AWS Prescriptive Guidance

The blocked_process_threshold parameter in SQL Server only controls when blocking information is written to the error log, but it doesn't automatically trigger alerts. You need to build a monitoring solution on top of this.

The most effective approach for monitoring SQL Server blocking involves publishing RDS error logs to CloudWatch, creating metric filters to detect blocking events, and setting up CloudWatch alarms that trigger SNS notifications. This solution allows you to scan through Amazon RDS for SQL Server error logs and set up alerts based on your specific requirements.

Step-by-Step Implementation-

Enable Log Publishing to CloudWatch: First, configure your RDS SQL Server instance to publish error logs to CloudWatch Logs. This provides the foundation for monitoring specific events that appear in the SQL Server error log.

Create Custom Metric Filters: Set up CloudWatch metric filters that scan the published logs for blocking-related entries. You can configure these filters to detect when blocking duration exceeds your 15-second threshold.

**Configure CloudWatch Alarms: **Create CloudWatch alarms based on the custom metrics generated by your filters. These alarms should trigger when blocking events occur for more than 15 seconds.

Set Up SNS Notifications: Configure Amazon SNS topics and subscriptions to send email notifications when the CloudWatch alarms are triggered

References:

[1] Title: "Receive SNS notifications about Amazon RDS for SQL Server when database state changes to Offline or Online" URL: https://aws.amazon.com/blogs/database/receive-sns-notifications-about-amazon-rds-for-sql-server-when-database-state-changes-to-offline-or-online/

[2] Title: "Best Practices on how to configure Monitoring and Alerts for Amazon RDS for SQL Server: Part1" URL: https://aws.amazon.com/blogs/database/part1-best-practices-on-how-to-configure-monitoring-and-alerts-for-amazon-rds-for-sql-server/

[3] Title: "Best Practices on how to configure Monitoring and Alerts for Amazon RDS for SQL Server: Part 2" URL: https://aws.amazon.com/blogs/database/part2-best-practices-on-how-to-configure-monitoring-and-alerts-for-amazon-rds-for-sql-server/

[4] Title: "Monitor deadlocks in Amazon RDS for SQL Server and set notifications using Amazon CloudWatch" URL: https://aws.amazon.com/blogs/database/monitor-deadlocks-in-amazon-rds-for-sql-server-and-set-notifications-using-amazon-cloudwatch/