I have an existing database that handles several asynchronous processes using SQL agent jobs. This has now grown to a point where using agent jobs is no longer a viable option.
I need to re-architect this so that it's more robust and scalable. Any "process" has a finite number of steps within it. In some cases, I only want to run one at a time, i.e. at any point, there could only be one instance of Process1-Step2 running, to reduce contention, and other processes can run freely as they don't cause any issues.
I am having trouble with fleshing this out initially, and was hoping someone could give me a push in the right direction. I thought I would start off with Service Broker, but that becomes slightly complex when you want to pause/halt some tasks, depending on others.