The case for SQL Server Agent 2.0

Question

The case for SQL Server Agent 2.0

Steve Rezhener

SSChasing Mays

Points: 615
More actions
December 21, 2020 at 12:00 am

#3817951

Comments posted to this topic are about the item The case for SQL Server Agent 2.0

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

e603536 Newbie Points: 1 More actions · Answer 1

Hi,

I totaly agree, sql agent need a facelift. One specific issue I have been struggeling with many times which just does not work is running powershell code using ”subsystem powershell”. With the intruduction and rapid development of that language and the intruduction of dbatools (dbatools.io) the need to run powershell code in the agent is necessary.

David.Poole SSC Guru Points: 76000 More actions · Answer 2

I like idea of parallelism and also the on_success, on_complete, on_fail options

Just as Microsoft looked at its competitors when it incorporated column store tech I think it should look at what has emerged in the open-source market place for job scheduling. The Apache Airflow idea of DAGs (Data Acyclic Graph) strikes me as useful.

In many respects a lot of what we used to do with DTS aligns with this.

SSIS is a far more sophisticated ETL tool but sometimes I get nostalgic for the simplicity of DTS. An awful lot of what we used to do with DTS wasn't ETL, it was workflow management.

Probably worth considering extended events and other observability and monitoring capability as well.

LinkedIn Profile

Jim.Young 4999 Newbie Points: 4 More actions · Answer 3

I'd love to see the ability to run one step or a range of steps only from SSMS.

Times I have to change job steps temporarily by changing the on success condition to stop at a given point....

SQL Agent is very powerful but I agree that Microsoft hasn't shown it any love for a long time and it is long overdue for a revamp.

phegedusich SSCommitted Points: 1552 More actions · Answer 4

Another improvement would be to expand the options in the scheduler, most notably to enable scheduling by business day based on an internal calendar.

paultormey Mr or Mrs. 500 Points: 573 More actions · Answer 5

Great idea...and how about letting operators see the current operating status in Job Activity Monitor.

Not just from a history point of view.

OffToSeeTheWizard Newbie Points: 2 More actions · Answer 6

Good post 🙂 I would love those extra error handling \ step features

Another option I would like to see is improvements to the UI...

For instance, if I open Job History, I usually change column widths etc, and the default view settings show a lot of gumpf I am not interested in. Yes I can use TSQL scripts to export specifics or even use an SSRS report (hmm might well do this!...), but then its all extra hassle. If I could have the option to save my custom view settings that would be great.

Additionally, a filter option in Job Activity Monitor of <> or Not like would be great.

We do use PaaS but there are limits and we have a lot of ETL workloads that require a VM as they are not supported in the PaaS platforms.

latkinson SSCommitted Points: 1674 More actions · Answer 7

Just adding something as simple as a loop, and a test for a file existence could make my day easier. And some operators to let me test a step outcome and decide what job step to execute next. Success or Fail is pretty limited. I'd like to test for a specific value or string being passed back by the step to control execution.

Luther

g.britton SSChampion Points: 13863 More actions · Answer 8

I would simply write an SSIS package and run that using SQL Server Agent. After all, that is exactly what Maintenance Tasks for SQL Server do. Those are a little limited, but if you roll your own, they can be as sophisticated as you like (including parallelism). Note that all the tasks you can use via SSMS are also available as SSIS tasks.

Added bonus: you can easily source-control SSIS projects and packages (although I admit that diffs are a little hard to read!)

Gerald Britton, Pluralsight courses

John Bigler SSC-Addicted Points: 450 More actions · Answer 9

John Bigler

SSC-Addicted

Points: 450

December 21, 2020 at 3:38 pm

#3822872

Hear, hear to Steve's proposal.

JediSQL SSCrazy Points: 2118 More actions · Answer 10

Hi. Here are a few points from me...

The overall success of a job depends on the success status of the last step run, even if an intermediate step failed and the job continues running on an "on error go to step X" workflow. There should be an extra checkbox for each step something like "mark job as failed if this step fails."
Job variables that can be referenced by all steps: YES!
I'm concerned about getting too much like SSIS. SSIS cannot be scripted out.

Sincerely,
Daniel

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 11

I like it. A few other things I think are needed:

Jobs need to be contained in dbs, with agent picking them up when the db is attached/restored, or the job edited.
we should be able to "start a job at 10am or as soon as other jobs complete"
jobs setting for auto disabled if they apply to a secondary AG db.

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 12

One of the things about SQL Agent is that it has worked fined, failed safe, and drained to the bilge quite nicely since I can remember. It does have a couple of quirks and shortcomings but I've grown to count on its functionality and reliability.

With the very common observation that "Change is inevitable... change for the better is not", I'd just as soon that MS didn't touch it for fear of them screwing it up like they have with so many other things. If you want some of the other functionality that folks have mentioned on this thread, learn how to use SSIS and schedule an SSIS package (which I avoid like the plague and do everything I need via T-SQL with no issues). 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

g.britton SSChampion Points: 13863 More actions · Answer 13

super easy if you put the steps in an SSIS package and run that from Agent

Gerald Britton, Pluralsight courses

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 14

If Microsoft does go this route, I am all for it, but they would need to be careful with it too.

Thing is some of that is already possible. Parallel job steps can be done with some workarounds. And loops can be done with SSIS or similar workarounds to parallel job steps. Sure they are not ideal and require some tweaking to make work right, I think that it is a safer option than monekying around with the SQL Agent.

One of the big risks with changing the SQL agent (in my mind) is you are going to need to do several pieces of updating. Maintenance Plans will need tweaking to work with the new SQL Agent, all existing scripts for SQL agent jobs will need to be run through an "upgrader" script of some sort, SSRS will need to be updated, SSMS will need to keep check the agent version before presenting the UI to the end user (to ensure it doesn't present features that shouldn't exist or miss features that should).

I think if they are going to go this route, it would be better to take the extended events route and build up a brand new service for this that can be run in parallel with the old service for multiple versions going forward (profiler and exctended events is what I am thinking here). This will ensure no loss of current functionality or skillsets and will protect systems during an upgrade to the new Agent 2.0. Imagine you have 1000 report subscriptions on your SSRS server and you upgrade your database and ALL subscriptions start failing and you need to manually script out an update to it? Or your backup jobs fail because you didn't indicate if step 2 can be run in parallel with step 1?

Another advantage of having a different service is you have time to plan and stage your upgrade to the new service without breaking existing functionality.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.