Introduction

SQL Server Agent enables the ease of scheduling routine tasks without leaving the comfort zone of SQL Server Management Studio (SSMS). This system stores all its related data in the msdb database. This article is going to discuss the need for more features in SQL Server Agent, essentially making the case for a SQL Server Agent 2.0.

Problem

I know everything nowadays is about Azure, but let's not forget that a big chunk of SQL Server clients (on-premise, SQL Azure Managed Instance, Azure VM) are not ready or just can't migrate to a contained SQL database product. There is still enough market for batched processing and not everything needs to be real-time/data streaming.

Unfortunately while Agent is still quite awesome at what it does, it's not advanced enough for an enterprise. It is light years ahead of scheduling job systems in Oracle/MySQL/AWS Lambda (a.k.a. cron jobs) or in general anything that is Unix based due to required knowledge of scripting and need for an extra tool to calculate schedule expressions (like https://crontab.guru/). Unfortunately, with all the new SQL Server features that were introduced starting in SQL Server 2005, Agent didn't get enough TLC. It is mostly the original interface that allows you to schedule a routine task in a few easy steps: create a job, create step(s) within the job, define condition to move to a next step, and create or select a schedule. The problems is it is not advanced enough for 2020.

Solution

IMHO, Agent needs more features added. I think small changes to SSMS and maybe minor changes to msdb schema would be needed.

I would love to see more freedom in Agent Steps, perhaps elevating it from the current step management into a workflow management. Currently, step management is very limited in options - you can run in a strictly serial order (example: Step1 -> Step2 -> Step3 - see Fig #1). Each step can only report success or failure, and only 1 outcome could be selected to decide if the next step runs.

Fig #1

I would recommend giving more power to the user by:

• supporting running steps in parallel
• allowing a combination of multiple steps conditions (example: continue to Step3 if Step1 Completed and Step2 Failed - See Fig #2)
• adding more step execution conditions like Step Completion and Step run-time threshold. It would be also we cool to see triggers, loops, variables (something better than job tokens), round robin, and a multi-threaded support.

Fig #2

I believe some of those features could be added by just adding an SSIS toolbox to SSMS from SSDT, similar to Maintenance Plan Tasks that we already have in SSMS (See Fig #3). Just in case that you don't know, creating tasks using a Maintenance Plan actually creates an SSIS package.

Fig #3

Adding the SSIS Toolbox to SSMS will add many SSIS features from SSDT, while other components like loop, trigger, etc. could be added later. Much like Maintenance Plan tasks, you will create those SSIS tasks in design-time using SSMS, while SSIS engine would be used for a run-time.

This might also require a new job category type to separate traditional jobs from SSIS (workflow type) jobs. On a separate note, it would be nice to see SSMS organize jobs by categories to help manage servers with thousands of jobs.

Adding features to Agent would definitely would most like cost money, but I think Microsoft has a great opportunity to create a greater appeal for it's Enterprise Edition customers by having some Agent features match more expensive ASCI ActiveBatch and/or IDERA SQL Enterprise Job Manager products.