Using a scheduler in the technical design of your BI solution

In Microsoft world when you think of scheduler, SQL Agent is one of the most famous name that would come up on the list. Scheduling can be seen as a very nominal and regular requirement in almost every project, and most professionals would think of addressing it by installing a scheduler on your target server. There is a difference between having a mission critical scheduler service and an enterprise scheduling service. If you see from the eye of an architect, you would never want a mission critical scheduler, as in the long run it becomes a maintenance bottleneck. There are two points to consider before designing solution for your scheduling needs. Firstly you need to figure our an enterprise class scheduler and secondly you need to get right design in place.

Personally, I do not see SQL Agent as an enterprise scheduler. It is very much useful for scheduling database related tasks, but even there I see some very serious shortcomings. For example, there is no file-watcher capability built-in with it, and watching for file / feeds is a very essential and common requirement with application as well as BI solutions. An example of enterprise class scheduler is Autosys, that I have seen in many enterprises class IT environments. Some of the strong features are scripting support, client-server architecture, multi-platform support, file-watcher event-watcher and other intercepting capabilities. ETL / Data warehousing solutions mostly need intercepting capabilities for data feeds, and if you need to depend on your ETL tool for interception rather than your scheduler, this is a shortcoming of your scheduler. In SSIS, developers use third-party File-Watcher and WMI tasks to watch for feeds, but I personally see it as a shortcoming of SQL Agent and not SSIS.

Now comes the question of how you frame your scheduler in the technical design of your solution. Mostly designers are concerned limited to the scope of the solution they are developing. But an architect needs to foresee how the solution would grow and fit in the enterprise. Any successful solution and business would mostly flourish, and so would the IT systems surrounding the same. With this growth whether it is in the form of scaling the solution and server across geographies, or more solutions getting rigged with your solution, your scheduling needs would also grow. So in case you have opted to use mission critical database or application or system schedulers, it would be extremely hard to manage your scheduling. Consider you have your BI solution spread across geographies with multiple servers and you intend to manage different scheduling needs on different servers, and you have opted to use scheduler on each server by installing SQL Agent. So how do you enforce policies, deploy scheduling routines, as well as control and monitor the same ?



The solution you need to have is a scheduler with a client server architecture. Generally the regular practice is to have a centralized scheduling batch server and scheduling agents are installed on each server. Scheduled jobs are always punched in the centralized batch server and agents synchronize with this server to retrieve jobs respective to their servers. In this way you can replicate your BI solution by creating different server clusters at different geographies without any maintenance , and scheduling of each cluster can be managed using the centralized batch server.

Enterprise class scheduler + Scheduling design pattern = High Performance. Delivered. (It's accenture punch line !).