AWS CloudWatch alarms for failed SQL Agent job notifications

Question

I am trying to setup AWS CloudWatch and SNS alerts for SQL Server Agent job failure notifications using AWS Console and AWS CloudWatch Console, not LAMDA.  I have successfully enabled the SQL RDS instances to publish their logs, but I am struggling to go further.  This is what I've done:

1. I enabled the RDS SQL instance to publish logs to CloudWatch.  Both of these Log Groups are visible in the AWS CloudWatch Console:
   /aws/rds/instance/instance-name/agent
   /aws/rds/instance/istance-name/error
2. I created an Agent job which runs 'SELECT 1/0' to fail by design.  The job fails.  Job history says 'The job failed'.
3. I tried a metric filter with both of these patterns but neither returned the job failure: 
  ?Error ?ERROR ?error ?Failed ?FAILED ?failed ?Failure ?FAILURE ?failure
  The job failed
Only the 1st one metric pattern returned this when tested:
   [412] Errorlog has been reinitialized.  See previous log for older entries.
4. I can read the Agent error log with this call, but it also only returns the above event that I am seeing in the CloudWatch console when I test the metric filter.  It is not returning the Agent job failure.
  EXEC rdsadmin.dbo.rds_read_error_log @type = 2
5. I read the log streams within AWS CLoudWatch \ Log Groups \ Log Streams, but it also does not provide any evidence of the job failure.  Which I've ran multiple times since the logs were published.

So, I don't know if it's because the Agent job failure is not being recorded in the Agent log for CloudWatch to see, or if it's not seeing it yet because I do not have a functional metric filter pattern.

Are there any procedures for what needs to be done now to successfully generate CloudWatch alarms based on the published logs?  Presumably that will also tell me what changes are needed, if any, so the Agent job failures are recorded in the Agent log.

re:Post Agent · Answer

Setting up CloudWatch alarms for SQL Server Agent job failure notifications in AWS RDS can be challenging, as RDS for SQL Server doesn't natively support SQL Server Agent operators or notifications. However, there are some workarounds you can try:

1. First, ensure that the SQL Server Agent error log is being correctly published to CloudWatch. You may need to verify the configuration of your RDS instance to make sure it's properly sending these logs.

2. Instead of using the Agent log, you might have better luck focusing on the SQL Server error log. SQL Server Agent job failures are often recorded there as well.

3. For your metric filter, try using a more specific pattern that matches the exact wording of job failure messages. For example:
   "The job failed.  The Job was invoked by User"

4. If you're still not seeing the job failures in CloudWatch, you may need to modify your SQL Server Agent jobs to explicitly log their failures in a way that CloudWatch can detect. One approach is to add a final step to your jobs that runs only on failure and writes a specific message to the SQL Server error log.

5. Another option is to use the sp_send_dbmail stored procedure within your SQL Server Agent jobs to send email notifications directly when a job fails. This bypasses CloudWatch but still provides notifications.

6. If you want to stick with CloudWatch, you could create a custom metric by having your jobs write to a specific table when they fail, then use a scheduled task to check this table and publish a custom metric to CloudWatch.

7. As a last resort, you might consider using AWS EventBridge (formerly CloudWatch Events) to monitor for RDS events related to SQL Server Agent job failures. This could be more reliable than trying to parse log files.

Remember, these are workarounds due to the limitations of SQL Server Agent in RDS. The most reliable solution would be to redesign your jobs to handle their own error reporting, either by writing to a custom log table that you can monitor, or by directly integrating with SNS or another notification service.  
**Sources**  
[Add support for Agent Operators and email notifications in RDS for SQL Server. | AWS re:Post](https://repost.aws/questions/QUlhAoIbkFRQSwPFCS9qW3aQ/add-support-for-agent-operators-and-email-notifications-in-rds-for-sql-server)  
[Cloudwatch Alarm for Glue Job Failures | AWS re:Post](https://repost.aws/questions/QU9mlu4koMRu-akCyAjjxX1Q/cloudwatch-alarm-for-glue-job-failures)

AWS CloudWatch alarms for failed SQL Agent job notifications

Add your answer

Relevant content