SQL Server Agent is a job scheduler.
When we first get started managing SQL Server, we usually use it for backups, index maintenance, DBCC CHECKDB, and other common maintenance tasks.
And then one day, we say, “Hey, I need this T-SQL script to run once a day.” We add an Agent job for it. It’s a rare occasion, we think. Not really a big deal.
The next thing you know, the entire business’s lifeline depends on dozens – or heaven forbid, hundreds or thousands – of intricately interwoven and ill-documented Agent jobs. Nobody knows what they do – they just have to run.
Except many of them don’t. They fail for one reason or another, but then they work the next time, and we just hope and pray everybody ignores it.
Eventually, some of them start failing permanently. When we try to figure out what’s going on with them, we ask around about who wrote the script, and it always turns out it was some guy who left the company months ago. Nobody knows how to debug his stuff, or if it even matters. We’ll just leave it in place and maybe it will start working again.
Don’t let yourself get to this place.
To make your life easier, keep application-logic Agent jobs out of your production SQL Servers.
If someone needs application logic, and it has to be an Agent job, it doesn’t actually have to be on the production database server. Give them their own Standard Edition VM where they can go to town, creating any Agent jobs they want. However, those users are 100% responsible for managing the success and failure of their Agent jobs because these are applications, not databases. There are no databases on this server – or if they are, they ain’t backed up or production-quality. This server is for jobs that can point at any SQL Server.
In addition, give them a VM in the DR environment where they can sync their jobs. Source control and deployment is totally up to them.
This way, it lets you focus: if there’s a failing job on the production SQL Server, you own it.
To help you pull it off, consider using my SQL Server support matrix. It sets clear expectations about what you’ll allow in dev, QA, production, and mission-critical production boxes.