Introduction

SQL Server Agent (Agent) – one of the original features of SQL Server that is probably predates to SQL Server 2000. Agent enables ease scheduling of routine tasks without leaving the comfort 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, the making of 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 SQL Server Agent (Agent) is still quite awesome at what it does, its not advanced enough for 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.

Solution

IMHO, Agent needs more features and an advanced interface. I think small changes to SSMS and maybe some changes to msdb schema would be needed.

I would love to see more freedom in Agent Steps, perhaps taking 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)
  • 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.

I believe it could be achieved by adding a new Workflow toolbox to SSMS (similar to Maintenance Plan Tasks) to keep it simple. This new toolbox can borrow the idea for a job step as a component from Execute SQL Server Agent Job Task component (see Fig #2) while other components like loop, trigger, etc…. could be added later. While SSMS Workflow toolbox would be used for a design-time, SSIS engine could be used for a run-time. This might also require a new job type to separate traditional jobs from workflow jobs.

Fig #2

Of course allowing SSMS to group jobs under categories (think folder here) would also help to manage any environment that has thousands of jobs.

Adding features to Agent would definitely cost money, but I think Microsoft has a great opportunity to create a greater appeal for it’s Enterprise Edition customers by matching Agent features to ASCI ActiveBatch and/or IDERA SQL Enterprise Job Manager products.