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.
16 Comments. Leave new
So SSIS packages which run on a regular basis via an Agent job, to load new data into application databases, you would do a separate install of SQL that the application folks have all to themselves?
Jason – generally speaking, I want SSIS on a separate instance anyway. SSIS loves memory, so whenever it’s trying to do big bulk loads, it needs memory – at the exact same time the engine does. While you can bulk up horsepower on the SQL Server to make all that work well simultaneously, I’d rather give the BI guys their own server to manage.
I rather like that idea, although as with most things SQL, how to implement would depend on the requirements of the environment.
In my case, I’d still be the guy managing the SSIS instance, although not the actual packages (beyond ruling out the SQL config as the source of any problems, or fixing the problem if it is SQL.)
This is 100% accurate. If SSIS wants to have more memory it can and will Request from the OS. If the OS has none left to give it will request some memory back from SQL Server. SQL Server will then have to clear out some room from the plan cache or buffer pool to give some memory back to the OS. This is bad for any number for a large list of reasons that I won’t go into in the comments section.
If you are running in a VM environment, would it matter? Since all the servers would be on the same host, would it be better to have a VM set up just for SSIS, or take those resources and add it to the SQL Server and run it from there?
Yes, in fact, it matters even more! When you’re in a VM environment, your hypervisor can automatically move guests around between different hosts in order to balance load.
Another frequently-overlooked pain point is that since Agent jobs are in the instance, the app db no longer encompasses the entire code base. From a DevOps/ConfigurationControl perspective, the ‘app’ = code + schema + agent jobs.It makes HADR extra not-fun.
The agent security model is weak and conditional branching is non-existent. Anything that is considered an asset worth maintaining should be moved into a SP in the db. Only use the Agent for scheduling code that lives elsewhere.
I like to make the point that if it lives in msdb, that means it’s Microsoft application built on top of Sql Server.
We had something like this set up. When one job would finish, it would kick off another job through a job step. That would then kick off another job, and so on. It was a maintenance nightmare. I was trying to move everything into some SSIS packages and/or stored procedures so there would at least be some process control and source control around them. I definitely learned not to place all of that logic and process control into SQL Agent jobs.
Sadly, this was one of those “it keeps working” jobs so as long as it was working it was really, really low priority. It wasn’t until the process needed to be migrated that it had to get some serious attention. Had it been addressed much earlier, it would have been a lot easier to deal with and refactor.
As a funny hypothetical, if you were offered a packet of money, but found you were consulting on an Express instance and so had to schedule everything in Windows, would you do it or walk away?
For a big enough packet?
I’d do it. Powershell and DOS Batch files for the win!
*ALMOST* wound up doing this at my last job, they didn’t want to tell customers to get Standard Edition and thought Express would be fine for the application they had developed. Found out after I left it only took another month for the first customer to hit the 10GB database limit…
I don’t work for the company, so do don’t think I’m pitchin the product as a sales person, but MVP systems JAMS in a lot cases can replace not only the sql scheduling aspect of things, but also create heterogenous system scheduling and workflows. I use it for coordinating sql backup’s to disk and tape pickup. Nice workflow capabilities… I’m not saying it’s a good fit for everything but it’s certainly something woth considering if it’s just job scheduling that you need.
This article is exactly why I love brentozar.com. They clearly know what the real world is like and don’t live in an ivory tower expecting the world to be perfect. The advice given may not go over well with folks in my organization but still a worthwhile idea that I am going to look into.
thanks!
My default production ‘enterprise architecture’ is SQL and SSAS on clustered instances and SSIS and SSRS on VMs. SSIS also requires its own pet SQL instance, but I make known that it’s not for data (maybe just a staging DB used by a package). Putting these ETL boxes in the context of app servers absolutely fits. I love the part where you don’t need to give developers sysadmin to do their jobs anymore (post 2012), but now I’m wondering about who should have control (full admin permissions). At this point my only reason for not ceding it over is that I kind of understand the SSISDB folder security and no one else sees how cool it is. So, given that ETL is actually an app, and purely in the context of proper role alignment, who should control the ETL box?
I’m not sure I understand the question. What does “no one else sees how cool it is” have anything to do with who should be managing the box?
Sorry, I was referring to the underlying complexity. If you don’t understand the Integration Services Catalog folder level security, your only option is to over-grant permissions “until it works”. One less than obvious example is that you (only) need to grant someone public permission in MSDB for their account to show up in the accounts search list, and therefore be able grant them permissions to a folder within the SSISDB catalog. Not knowing that tiny requirement could theoretically mean sysadmin for everyone. 🙂 I’ve found a general tendency for developers to go with the ‘least resistance’ solution, so leaving security to, for example, the guy that replaces the guy I trained, could end up biting… something.
Some days (many days), events drive me to come and reread this article. It’s entirely for emotional support–To know somebody out there understands. Then I typically cry at my desk for a bit.