Use SQL Server Service Broker when possible to get new jobs

[odinserj]

SQL Server queue fetching implementation currently uses simple polling technique to get new jobs. This works, but high poll intervals increase the latency of job performance, and low polling intervals increases DB stress.

Service broker could help there, because we can (or if we can) subscribe to receive notifications on new jobs using the SqlDependency class. As I know, it could be used even in the Express edition of the SQL Server.

This task requires more investigation. Moreover, we need to decide how to support it (if it possible) – turn on service broker by automatically or let a user to do this manually. So, we need either to know about negative impacts to SQL Server when this feature is enabled.

[odinserj]

Service Broker is not supported by SQL Azure (see also #91). If HangFire.SqlServer works on Azure, we should not enable this feature by default.

[dlongest]

I'm no expert in Service Broker, but I did implement a solution using it at work about 18 months ago in SQL Server 2008 so have some minimal experience. Also not 100% on what you're looking to do, but I think the vision is to push job notifications directly to HangFire and not have to poll for them? In our case everything we did was completely internal to the SQL Server, but we did consider app notifications as we weren't sure the best place to put our logic so this is based on my research and not from implementing it. SqlDependency would work although it's an imperfect tool as I don't think it will notify you specifically what's changed, only that something has changed, and then you have to query to find the jobs. Depending on how fast items are submitted, the notifications aren't always fired 1:1 either. I do not know about the performance impacts of having service broker enabled. Getting permissions configured on the SB objects can take a little work and it's hard for me to picture how we could do that automatically.

[odinserj]

@dlongest, yep, I want(ed) to add this feature to reduce that latency of background job processing caused by polling technique. Service Broker can reduce it, despite of some cases when notifications may not be sent.

Getting permissions configured on the SB objects can take a little work and it's hard for me to picture how we could do that automatically.

I implemented the feature like described in another project, but I did it some time ago, and already forgot all the related problems. But you reminded me that getting Service Broker to work sometimes is very painful. Can you tell me about your feelings related to Service Broker configuration process? How much problems did you face? If you tried to solve them, how much it is difficult?

Because if it is hard enough to install and/or debug this feature, then it should not be implemented, since there is support for MSMQ, and @dennyferra wants to implement RabbitMQ support as well.

[dlongest]

With what we're talking about doing, I'm not actually sure what would be necessary. I think in our case we just bludgeoned the problem by using a lot of elevated credentials on the objects that were affected, but we weren't in a high security environment. Much different problem too: we had records being written to a database and in near-realtime, needed to have some rules also stored in the database applied against them and the results written into a separate table so everything was contained there. Viewing the results was done manually. It took me quite a while to get all the various SB objects communicating appropriately and everything activated, but all of that was custom stuff, I hope SqlDependency would take care of all of that for us automatically, which would relieve a lot of those difficulties.

And in terms of what else is necessary for SqlDependency to operate, I'm unsure. Do you have a timetable for this feature? I can look into figuring out what it would take independent of HangFire to use SqlDependency, but I'm swamped for the next few weeks so won't have much time to throw at it initially. IMO I would generally prefer/expect the MSMQ approach over SQL Server, but not every user is me.

[odinserj]

The problem is not whether to use the SqlDependency or not. This is a proposed solution (to be more specific, this is the first proposed solution) to reduce the latency in background job processing that is caused by the polling technique.

This problem was already solved by MSMQ feature on top of SQL Server storage. Queueing subsystem of the storage was abstracted, so other solutions are possible to fix the problem using other ways.

Negative arguments of introducing Service Broker usage feature:

• There are solutions that solve the initial problem (written above).
• It should not be enabled by default, because it will break the SQL Azure support and may not work on shared hosting.
• It will require manual set-up, because migration can not include the correct authorization configuration.
• It is very hard to debug notifications (subjective opinion) and answer questions about Service Broker.

Positive arguments:

• This may solve the initial problem.
• Does not require infrastructural changes.

I don't like the negative arguments, they outweigh the positive ones. So, without other positive arguments this ticket will be closed after a while.

[odinserj]

Closing as outdated.