I missed the deadline because I was sick, but wanted to post about it anyway. So, here is my response to this invitation.
Managing Agent Jobs
It’s a short and sweet answer: Store your job definitions as code in GitHub—that’s my opinion.
But in the good old days with SQL Server, we just set them up in the agent. They were stored in msdb, so we, of course, backed up msdb. If something blew up, well, you were restoring msdb.
At my next job, we would store at least DBA maintenance jobs in Confluence, along with the instructions for setting up a new server. This way, we would remember to add them to new servers.
Nowadays, I would store the definitions in GitHub. I don’t manage SQL Servers anymore. My database landscape only includes Azure SQL and Azure Postgres. But speaking of Postgres reminds me of how I would manage cron jobs. I wouldn’t leave it to chance with the definitions floating in cron. I would store them in GitHub, and I do store them in GitHub. It reminds me of Elastic Agent for Azure SQL. I set up the agent and assorted bits with Terraform, so it’s all in GitHub in the Terraform code.
I would never go back to taking a chance on restoring a backup of a database to get my agent jobs back. Even though I could still do that with Elastic Agent, if I set it all up manually, which I don’t. You have the definitions in the agent database, but that seems like a terrible way to “store” them. Now, I want all my job definitions in code in GitHub. Nicely, centrally stored for future use.
Monitoring Agent Jobs
I don’t have a lot to say here. Automation is mostly what I have to say here regarding monitoring, so you are getting alerts on failure. If you ask me, no one should be going in to check on agent jobs manually. For me, these are alert rules in Azure, but if you are still on SQL Server, make sure you have alerting on jobs, or maybe you have a third-party tool, which I had at one job for compliance purposes.
Permissions to Agent Jobs
Again, not a lot to say, to me this is very much locked down to very few people, and they are the ones with the keys to the db kingdom already. I tried at one job to give someone else limited permissions to deal with job failures in SQL Server, but back then, and maybe not a lot has changed, it was impossible to allow that someone to execute a job they didn’t own.