I have done some searching and can't find anything official from ESRI, so I'm bringing my question here:
Is there a best practice for maintaining a robust back-end data processing environment? Right now my small government shop runs all nightly/weekly/monthly data processing scripts on our SQL server using Windows Task Scheduler. Due to some permissions conflicts, we are interested in migrating these processes to a standalone server to allow more of our GIS professionals access to the scripting environment.
I have been researching Notebook server but I'm pretty sure this isn't what we're looking for - it seems like Notebooks have a focus on web admin / web content management.
Does ESRI recommend a standard Windows VM that has arcpy, task scheduler, etc. for running all back-end data processing scripts? Do they recommend keeping it on the SQL server? Are they moving towards Notebooks for local data processing too?
For someone who has worked in both a cloud VM and on premise machine, I prefer on premise. Especially if you are concerned about security issues or latency. I currently run approx. 40 scripts on a daily basis using a single on premise machine. So far so good. I could share the specs of my machine if you are interested.
My organization actually just migrated our scripts to a dedicated server. Before we had a similar setup to what the OP probably currently has; a server with an enterprise GDB/SQL and scripts set in Task Scheduler. In an effort to segregate separate GIS tasks/workflows, we stood up an automation-focused server. Honestly it's set up the same as we had it before ... scheduled tasks are still set up in Task Scheduler. No real issues with the new server setup. In fact scripts do run slightly quicker. Some of that might have to do with the newer operating system we migrated to.
As for Notebook server, that's something I have been interested in exploring, but don't have any experience with at the moment. I'd also be interested to hear how other organizations are using it for their day-to-day operations.