May 8, 2012 at 11:10 am
I'm investigating Service Broker External Activation for one of our products. We would like to be able to drop items into a queue and have a C# app be alerted and handle the queue. I've done some googling and will continue to search, but the external activation implementations I've found are using the "Service Broker External Activator" built by Microsoft's Service Broker team[/url]. I don't think it's going to work the way I want it to for two reasons:
One: The external activator is a service that "watches the user configured notification queue". The term "watches" implies to me that it is regularly polling to see if there are items in the queue, not receiving an event (or something similar) from SQL Server. Cool, but I was hoping to have something that would be very lightweight and not induce a load on the server, so this isn't exactly what I was hoping for.
Two: The external activator service can only launch an executable as an action, I need to integrate this into an existing service that will already be running. SQLDependancy won't work for us because it doesn't have the granularity we need - it only notifies the app that "something" has changed, not the record that caused the trigger (Didn't research this particular item myself, but I trust those who brought the info to me).
Am I correct on these assumptions? Has anyone done external activation without using the "External Activator" service?
Based on what I've currently found, I think I'll have to write something myself. One of the options I'm considering is using internal activation (using a stored proc) that then fires out a CLR Proc that either hits the app directly or a webservice.
Thanks,
Chad
May 8, 2012 at 1:47 pm
Never researched External Activation, but we do have a number of queues with activated CLR procs whose purpose is to communicate with systems (such as Redis) outside of the SQL Server instances.
We have lots of huge, busy databases, so I prohibited running CLR in those instances. Instead we set up a separate SQL Server instance in each environment that hosts all of these queues and the CLR stored procedures. Messages are simply routed from the source databases to services in a database in the dedicated CLR instance.
Eddie Wuerch
MCM: SQL
May 9, 2012 at 12:21 pm
I think that is what I'm going to recommend - a CLR hit to a webservice to simulate "activation". It will work the same, I've done it before, and I haven't found any good examples of roll-your-own external activation. External Activation would be a fun project to do, but I wouldn't be able to complete it within deadlines and this will work just as well for my purposes. Thanks Eddie!
Chad
August 6, 2012 at 8:54 am
Hi,
The external activator will read the notification queue using "waitfor" (every 3 seconds).... which in my understanding is the only way to retrieve messages from a queue.
We are using the external activator and it works beautifully once it is configured correclty.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply