search

DBTrenches / tsqlscheduler

MIT License
4 stars 1 forks source link

Expand schedule granularity support #11

Open petervandivier opened 2 years ago

petervandivier commented 2 years ago

This is only a partially articulated thought but it's been coming up with increasing frequency so I want to get it written down.

The options for schedule in the solution are somewhat less than what is supported in native jobs. This was a reasonable 80/20 choice in original development because capturing full coverage is a pain and most jobs don't need it. In practice though, the partial support for schedule options is a disproportionate source of metrics pollution.

For example: Jobs that need only run monthly are set to daily and have internal proc logic to check Day-of-Month before proceeding. Ditto with weekly jobs or jobs that want certain day-of-week restrictions. These jobs have a huge false positive success rate for those no-op executions which masks chronic failures.

Expand support for scheduling options and provide examples on the "new" variants.

taddison commented 2 years ago

Any suggestions for the schema? We could keep the schema the same and add Weekly with FrequencyInterval solving for DayOfWeek. This would only give us support for every 1 week (which is less than the options for SQL Agent), but feels like that'd solve a large chunk.

Similar thoughts on adding Monthly and DayOfMonth would be stored in FrequencyInterval.

Do you see the need to support schedules like 'last Wednesday of every month'?

petervandivier commented 2 years ago

Tbh, I sort of had this in mind for ~v2.0~ v3.0. I also want to add support for multi-step jobs for a similar reason (but I'll try to keep that to a separate issue (edit: see #12 )). As such, there's some idea of support backwards compatibility, but I don't think it's prudent to maintain the current schema to the exclusion of feature improvements.

Do you see the need to support schedules like 'last Wednesday of every month'?

It was a problems with schedule of this type that broke me into creating the issue last night so... ✨yessssss✨

taddison commented 2 years ago

Maybe you can share some more details of the kinds of schedules, as those are sometimes the default and the 27th of each month is fine.

There's a balance to strike between a more fully-featured scheduler (which might mean it's time to grow-up from SQL Agent) and a simple schema (recreating the complexity of SQL Agent is perhaps an anti-goal).

petervandivier commented 2 years ago

recreating the complexity of SQL Agent is perhaps an anti-goal

it is also almost certainly what I'm steering towards here 😅