I am working on a custom scheduling process and have a few rough ideas in my mind but I am not sure what is a scalable, simple, defect-free solution.
I plan to have one table, named ProcessSchedule, and one Select statement within a sproc scheduled to run every minute as a job through SQL Agent. There will be many processes coming into ProcessSchedule. The sproc's job is to query the table every minute and determine which process(es) are ready to run at that minute (granular at minute level).
Frequency int, -- 1:Once 2:Hourly 3aily 4:Weekly
Time int, --1800 means 6pm
Days int, --Mon:1 Tue:2 Wed:4 Thu:8, so 9 is Mon+Thu
* I don't want to use char in WHERE clause to make it sargable
* There might be additional 3 computed columns like FreqDesc, TimeDesc, DaysDesc in string datatypes
* If SQL Agent stops for a while (like a service restart), ideally, the SELECT should pick up where it's left off.