DaveWentzel.com All Things Data
Service Broker Tickling OR How to Run a Procedure Asynchronously Every x Seconds
Service Broker is great for asynchronous execution. There are common patterns to asynch execution in the data tier that are well-covered on the Internet, such as asynch trigger execution. Remus Rusanu's website is invaluable for all things Service Broker and I find myself visiting it FIRST, even before google, when I need an answer to a Service Broker question. Someone on stackoverflow posted a question, "How can I get SQL Server to call a stored proc every n seconds?" At my current client we have these things, and we have probably 30 of them, scheduled as SQL Agent jobs. This works very well, but it means that when you restore a database for development or testing that you have an affinity with msdb that is not restored with your database. It also leads to Blocking and Contention with sysjobhistory when you have a lot of these jobs. I just don't find a ton of SQL Agent jobs to be very elegant.
Remus answered the question by noting that Service Broker can do this using a Timer and an Activated Queue. He hasn't (as of today anyway) posted this answer or pattern on his website. I hope he will. I've done something like this in the past where we developed a software product that was deployed using SQL Express. At the time Express did NOT have SQL Agent so if you needed to have an asynchronous scheduling mechanism you needed to roll your own, probably using Windows built in AT.exe command (yuck). The solution I used was called I called the "tickler". It would "wake up" every x seconds, see if there was any work to do in the configuration table, and do the needful.
Remus' solution is far more elegant. I expanded upon his solution and wrote a more robust "tickler" in Service Broker. You can download the script here.
Let me point out some interesting code:
|We only need a single Q and Service. Message types, contracts, routes, etc are not needed. The "DoSomething" procedure is basically whatever you want to execute every x seconds.|
|I created a "Start Tickling" procedure. This simply looks to see if I have an existing conversation (you only need one) for the Tickler. If not, a create a conversation and TIMER against that conversation. That sends a message to the TicklerQ.|
|The Tickler Q is an activated Q. This is the stored proc that runs when a message is enqueued. Here we simply RECEIVE the message and execute our DoSomething procedure. Then we send another CONVERSATION TIMER (basically this is an infinite loop).|
|After we have all of the scaffolding in place (the Q, Service, activator proc, etc...we need to turn on ACTIVATION for the Q. We are now ready to seed the first TIMER using the StartTickling proc.|
Again, I think this is a much more elegant solution then a SQL Agent job. Here I can run my procedure without having affinity to a job that will not get restored when I restore my db to another server. Tickling also picks up whenever the db is restored, comes back online after a restart, etc. A very elegant solution.