Automated blocking and query alerts for Amazon RDS for SQL Server

The blog post walks readers through a DIY alerting pipeline for Amazon RDS for SQL Server. Because the managed service does not emit blocking or long‑running‑query metrics to CloudWatch, the authors create a Python‑3.12 Lambda that runs every minute via EventBridge, connects to the RDS instance with the lightweight pytds driver, and queries sys.dm_exec_requests, sys.dm_exec_sessions and related DMVs. A recursive common table expression walks the blocking_session_id chain to surface the root blocker, while filters on is_user_process and known system commands keep noise down. When the Lambda spots a session blocked longer than the 60‑second poll interval or a user query exceeding 300 seconds, it pushes a custom metric to CloudWatch and fires an HTML email through Amazon SES containing session IDs, host names, and the offending SQL text. The guide also stresses security: a dedicated “rds_monitor” login with only VIEW SERVER STATE and VIEW ANY DEFINITION rights stores its credentials in Secrets Manager, avoiding the use of the master account.

This approach reflects a broader AWS pattern of augmenting native service gaps with serverless glue. RDS for SQL Server historically lags behind its Azure counterpart, which offers built‑in Query Store insights and automatic alerting. By leveraging EventBridge‑driven Lambdas, AWS enables customers to replicate similar visibility without purchasing third‑party APM tools that often carry per‑core licensing fees. The solution also showcases the growing preference for “infrastructure‑as‑code” monitoring stacks that combine managed services (Secrets Manager, SES) with minimal custom code, aligning with the industry’s shift toward cost‑effective, observable cloud databases.

Looking ahead, the reliance on a one‑minute Lambda schedule introduces a trade‑off between detection latency and Lambda invocations; workloads with sub‑minute blocking could still slip through. Maintaining the pytds package and the SQL queries adds operational debt, especially as new SQL Server versions or RDS engine updates arrive. Organizations should monitor AWS announcements for native CloudWatch metric extensions for RDS SQL Server, which could render the custom Lambda redundant. In the meantime, teams must secure SES sending limits, guard against email fatigue, and ensure the monitoring user’s permissions stay tightly scoped to prevent privilege creep.