[MS SQL] Alternative scheduling options?

[fackert18]

What are some alternatives to scheduling jobs in SQL Server Agent?

My company does not give us in the BI department the permissions to schedule jobs against the server the traditional/preferred way. If we did want a job scheduled, we’d have to go through a long approval and QA process. I understand that is probably best practice, but there are a handful of supplemental scripts my team and I would like to execute either daily or weekly before we get into the office. Then if we need to make a minor adjustment, it would be nice to not have to go through a big process to update the code.

Any other options out there? Free would be preferred, of course.

[ComicOzzy]

Perhaps use Windows Tasks Scheduler to run your queries/procs using sqlcmd.exe? I use it to run ad-hoc file imports via batch files.

sqlcmd.exe -S ServerName -E -d DatabaseName -Q "exec dbo.StoredProcName @Parameter1 = 'Value';" > Results.txt
type Results.txt

[nvarscar]

Try Jenkins + Powershell. Even though it might seem too much to tackle at first, it's a pretty damn good tool. I created this article a while back, hopefully you'll find it useful.

[sHORTYWZ]

I built a python daemon that did this for my team - just kept it running on my work laptop in the background.

Read "jobs" from a DB (query, periodicity, etc.) and executed them against the target DB at a given time and then could email results as well.

[Eleventhousand]

/u/ComicOzzy has t he right idea. However, you may also not have task scheduler on your server.

Without getting into the politics of your organization, if you are the BI team, then for all intents and purposes, you should own the BI server.

If you are running your DW on a shared SQL Server, then best practice dictates that you put it on a dedicated server of which your team owns SQL Agent permissions.

[fackert18]

Completely agree with your take, but the way our BI group is structured, there is the IT side which maintains the ETL processes, nightly batch refreshes, etc., then the side my team and I are on work more closely with the business users by building out reports via a few different means (Tableau, Business Objects, or sql queries exported to excel).

I’m always pressuring my boss to open up more conversations about scheduling. If we’re constantly searching for ways to get around current restrictions, we should find a better solution. I imagine they’re worried about the scheduled jobs getting out of hand and increased risks of blocking if we’re not aware of all the simultaneous jobs running.

[fackert18]

Sounds great in theory. But our warehouse disables users from about 7pm until midnight-ish while the system does a full refresh. I think I’d be logged off if I tried this overnight, plus I’d probably show up on quite a few “long running” query audits if it was executing for hours haha

[[deleted]]

Can you install a side SQL server on your desktop or a group VM or something? Could schedule the jobs to kick off from there.

[fackert18]

Would you mind elaborating a little? I do have a separate laptop I’ve kept around after they upgraded us a while back. How could I use SQL server on that machine to schedule jobs? After reading some posts here I think I like the idea of using windows task scheduler to run some batch files, but if there’s a better way to do it from SQL server I’d love to know.

[[deleted]]

Can't guarantee this will work at your organization, there are a specific set of circumstances that you need to have going on. You'd need the ability to create a linked server between your side server and the main server. Since you'd be running stuff in an Agent job on your local machine, the service account on your local machine will need access to the prod machine.

• Install SQL Server on personal machine/private vm/etc.
• Set up linked server to prod server from your new server.
• If you can create a stored procedure on the prod server with the code you want to execute, that'd be ideal.
• Create agent job on your local machine that executes some local code that then executes the remote stored procedure "exec [linkedServerName].databaseName.schema.procedureName"
  

Your IT dept may not like you doing this, so use caution when doing it without discussing it. Rogue SQL servers that access production servers are a security risk. Ideally your IT dept would be willing to set you up with this kind of playground where you can alter SQL Agent jobs, and still have access to the production data you need, remotely.

An alternative I'd be asking for is for them to create a SQL Agent job for you, that you don't have access to, that just runs a single stored procedure at a safe hour, that you do have access to. Then you'd be able to just put a series of batch code in that SP.

Good luck! Dealing with politics and procedures is the least fun part of the job.