Using SQL Server Service Broker as a message source?

Replies to This Discussion

Permalink Reply by Marty Wasznicky on September 8, 2010 at 4:27pm

Hello Volox,
I believe we have one customer doing this today...and another looking into it. There are a few approaches you can take to use SQL Server Message Broker as a source of messages for Neuron....each one has pros and cons.
1. The first approach is to use the Activation feature of SQL Server's "Alter Queue" statement. This allows you to associate a “Target” stored procedure with a Sql Broker "Queue"....and hence it would execute each time a new message arrived in the queue. There are plenty of examples on line. Within this stored procedure, the message would be read from the queue and from there, a call would be made to a SQL CLR (extended) procedure. An extended procedure maps to a .NET assembly. To use it, you have to import your .NET assembly into Sql Server using the "CREATE ASSEMBLY" syntax like so:
CREATE ASSEMBLY [Neuron.ESb.Sample.SqlClr]
FROM N'C:\Neuron\SampleNeuronAPI\SqlClrAssembly\bin\Release\Neuron.ESb.Sample.SqlClr.dll'

If your .NET assembly references other assemblies, those assemblies will need to be imported first. There are a few "gotchas" though with this. Sql Server's support for .NET is not very good. WCF is not supported....and many namespaces are not. But you can get this to work if you keep to the basics in your custom .NET assembly....say like calling a service reference for an ASMX based service. In many cases, you'll probably have to use the option for Visual Studio to create a serialization assembly for your project and load that as well like so:
CREATE ASSEMBLY [Neuron.ESb.Sample.SqlClr.XmlSerializers]
FROM N'C:\Neuron\SampleNeuronAPI\SqlClrAssembly\bin\Release\Neuron.ESb.Sample.SqlClr.XmlSerializers.dll'
Once you import all the necessary .NET assemblies into Sql Server, you need to create the extended stored procedure which the “Target” procedure would call out to. You do this by using the CREATE PROCEDURE like in the sample below:
CREATE PROCEDURE spNeuronSendToBus (@url nvarchar(max), @message nvarchar(max), @action nvarchar(max))
AS
EXTERNAL NAME [Neuron.ESb.Sample.SqlClr].[Neuron.ESb.Sample.SqlClr.BusManager].SendToBus
If you decide to have the method within your .NET assembly call out to an ASMX service to do the work, just use WSDL.exe to generate a client proxy class from the wsdl of your asmx service. Using an ASMX service as an intermediate step allows you to either use our client API, or WCF against a neuron client connector (generic web service on ramp hosted by Neuron) to submit the messages to Neuron. Also, since you don’t have any constraints with what assemblies you can reference…you can pretty much do whatever you want within the ASMX service code. Also, if you need to change the way/logic in which you submit messages to Neuron, you only need to edit the ASMX service…you won’t need to re load your .NET assembly into Sql Server.
Alternatively, if you didn’t want to use an ASMX intermediate service to do the actual submitting of the message, you could just use System.Net.WebClient to submit the message to a Neuron client connector. This has the benefit of simplicity since there’s no intermediate ASMX web service you have to build….and there will not be a requirement to load a serialization assembly into Sql Server.
Either approach works fine.
2. The next approach…which I tend to think is easier, is to write a receive side Neuron adapter that sits and waits for messages to be submitted to the queue. It would use the same WAITFOR and RECEIVE and receive statement that you would have to use in the “Target” stored procedure in the first approach. This doesn’t poll…nor does it place a lock on the queue. So very efficient. I think there are still bugs with Sql Broker….so you’ll probably want to optimize by placing the statement that the adapter uses into a stored procedure.

In any case, I have working examples of all these….I do tend to like the latter the best. I’ll post all the source code hopefully in the next few weeks. If you need this earlier, just feel free and ping me at marty.wasznicky@neudesic.com

• ▶ Reply

Permalink Reply by Volox on September 8, 2010 at 4:45pm

Will look forward to seeing the samples.

Is there any plan to add a built-in adapter for Nueron to connect with Service Broker? And maybe use the queue activiation events to launch message consumption (similar to the way the new External Activation does)?

For External Activation see... http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thre...

• ▶ Reply

Permalink Reply by Marty Wasznicky on September 8, 2010 at 7:36pm

Hi Volox,

We currently have no plans to add a built in adapter to support it. We really don't see too many people at all using Sql Broker....so the demand just isn't there to justify it unfortunately.

• ▶ Reply