Last week has been a bit of a busy week for me, I’ve got really stuck into how MS SQL RDS works and the difference between on prem from a SQL Server DBA point of view and how DBA's can manage the specific areas we'll be responsible for in RDS.
So one big feature you’ll come across in the SQL land is SQL Agent to run jobs. I think this is especially important within RDS as you don’t have the ability to implement maintenance plans! So if you need to implement a job for SQL server to do all your maintenance tasks (CheckDB, Update statistics, Index defrags) your probably going to want to use SQL Agent jobs to implement this as a quick solution.
So there I was happily setting up my Agent jobs, adding the relevant steps in etc. We have our job- but I want to be alerted when the job fails. So on management studio I scroll down to SQL Server Agent and I look for the operator section but I notice it's not there.
I then think ok not a problem- lets so it the old fashion way by scripting out sp_add_operator and adding it that way. Apart from the fact even when I run under the master account I don't have the permissions to run this.
Having sight on your jobs and if they are working successfully is pretty critical- If your maintenance job fell over for whatever reason and wasn't running you could go far too long without noticing and when those jobs are checking for vital things like corruption or updating stats and defragging indexes you'll find yourself in a world of potential trouble if left unnoticed.
So at this point I took a step back and thought how do we get around this? I did a little research online but I couldn't find a whole lot, I scanned high and low through the RDS MSDB database and RDSAdmin database to see if there was an RDS Equivalent proc I could execute to set this up- my search turned back nothing.
My Solution
My Solution was to create a custom agent job which runs ever X amount of minutes (However Often you want to check eg 1 hour.. 30 minutes, once a day etc!) and then to utilize database mail to ping an email off should any failures have occurred! If you haven't set up database mail yet Amazon already has some great documentation to take you through it step by step here. If your using SES to act as an email server than you can obtain your credentials by following this AWS article here
You can find my custom stored procedure in my github here:
Once you have an agent job failure this will send through an email automatically that looks something like the below:
and there you have it- we now have monitoring on RDS Agent job failures so we can react accordingly.
Comments