What is the practice of scheduling multiple interconnected SQL Server Agent jobs?

Question

The way my team is currently assigning jobs is the SQL Server job agent. Many of these tasks have dependencies on other internal servers, which, in turn, have their own SQL Server Jobs, which must be run in order to update their data.

This created dependencies at the start and length of each of our SQL Server Jobs. Task A may depend on the completion of work B, so we assign Job B a certain approximate time to Job A. The whole process is very subjective and not scalable, as we add more jobs and servers that create more dependencies.

I would like to get out of business to subjectively plan these tasks and hope that dominoes fall in the correct order. I'm curious about what SQL Server job scheduling practices are. Do people use SSIS to pool jobs? Are there tools already built into the SQL Server Job Agent to handle this?

What is an acceptable way to handle scheduling multiple SQL Server jobs with dependencies on one another?

Answer 1

I used Control-M before to schedule several interdependent jobs in different environments. Control-M usually works using batch files (from what I remember) to execute SSIS packages.

We had a complex environment in which 2 data warehouses were located nearby (1 international and 1 local USA). There were jobs that depended on other jobs and those jobs on others, etc., but using Control-M we could easily identify the dependency (it has a really nice and intuitive graphical interface). Another tool that comes to my mind is the Tidal Scheduler.

There is no standard for task planning, but I think that it can be said with confidence that work schedules are completely dependent on the needs of the organization. For example, financing assignments may depend on sales and inventory sales, etc. But the fact is that if you need to be addicted to work, using third-party software such as Control-M is a safe bet. It can control jobs in different environments and gives you a real idea of ​​the wide control of the company.

Answer 2

We also had a requirement to manage dependencies between several tasks of the agent - after viewing various third-party tools and discounting them for various reasons (mainly, to the internal limitations associated with using third-party software), we decided to create our own solution.

The solution focuses around the configuration database, which contains information about the processes (tasks) that must be performed and how they are grouped (parties), as well as the dependencies between the processes.

Summary table of configuration tables used:

Package - determining the level of a group of related processes at a high level includes metadata, such as maximum parallel processes, the current instance of the batch, etc. Process - metadata related to the process (task), such as name, maximum timeout, earliest execution time, status (enabled / disabled), batch (which package belongs to the process), name of the process task, etc. Batch instance - active instance of a specific batch Process Instance - active process instances for a specific batch Process dependency dependency matrix Batch instance state - search for batch instance status Process instance status - loolup for process instance status

Each batch has 2 control tasks - START BATCH and UPDATE BATCH. The first deals with the launch of all processes belonging to him, and the second - with the latter, which runs in any given batch and is engaged in updating the status of the results.

Each process has an agent job associated with it, which is executed when the START BATCH job is executed. Processes have limited concurrency (defined in a batch configuration), so processes start up to the maximum x value at a time, and then START BATCH waits until free access to the slot becomes available before the next process begins.

The process agent job steps invoke the SSIS boilerplate package, which processes the actual ETL operation and decides whether the process should start and wait for dependencies, etc.

We are currently moving to a Service Broker solution for more flexibility and control.

In any case, perhaps there are too many details and not enough examples, so the VS2010 project is available on request.

Answer 3

I'm not sure how much this will help, but we decided to create an email planning solution.

We have created a mailbox that accesses an exchange mailbox. Upon completion of the work, they send an email to the mail reader to start another work. Another nice part is that most applications have built-in email notifications, so there really aren't many programming methods.

We really only built it to process data files coming from many other partners. It was much easier to give them an email address rather than having them set up with an ftp site, etc.

Currently, an email reader application has grown to include basic filtering, time-of-day scheduling, the use of semaphores to prevent concurrent tasks, etc. It really works great.