Introduction

SQL Server Agent (Agent) – one of the original features of SQL Server that is probably predates to SQL Server 2000. Agent enables ease of scheduling routine tasks without leaving the comfort zone of SQL Server Management Studio (SSMS) while SQL Server is storing all the related data under msdb database tables. This blog 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 go 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, its not advanced enough for an Enterprise. Of course its light years ahead of scheduling jobs 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 expression like https://crontab.guru/. Unfortunately, with all the new SQL Server features that were introduced starting from 2005, Agent didn’t get enough TLC – its mostly the same original interface that allows you to schedule routine task in 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 Steps management into a Workflow management. Currently, steps management is very limited in options – you can run in a strictly serial order (example: Step1 -> Step2 -> Step3 – see Fig #1), only 1 outcome condition can be selected from only 2 (success/failure), and only 1 outcome could be selected.

Fig #1

I would recommend to give 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-thread 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 Maintenance Plan is actually creating SSIS packages.

Fig #3

Adding SSIS Toolbox to SSMS will add many SSIS features from SSDT, while other components like loop, trigger, etc…. could be maybe 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.