Integrating SQL Service Broker and NServiceBus

Question

My requirement is that I have "items" with different states. I need to do all kinds of different things based on the states of these items (send emails, store files, update internal applications, etc). The problem is that the application that updates these statuses is third party, so I can't update it; furthering the difficulty is the fact that only half of the organization is using this system and the other are using it's future replacement. I do have open access to both of the databases used.

My plan is to add a message to a SQL Service Broker Queue with and ID and the status via a trigger on each representation of the Item table. Then, call a CLR procedure from the SSB activation procedure that adds a message to a publish queue via NServiceBus.

This all seemed to be going well until I tried to register my assemblies in SQL with CREATE ASSEMBLY:

Msg 10327, Level 14, State 1, Line 1
Warning: The Microsoft .NET Framework assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

etc. etc. etc.

CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Msg 10301, Level 16, State 1, Line 2
Assembly 'log4net' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Msg 6218, Level 16, State 3, Line 3
CREATE ASSEMBLY for assembly 'NServiceBus.Core' failed because assembly 'NServiceBus.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
Msg 6218, Level 16, State 3, Line 4
CREATE ASSEMBLY for assembly 'NServiceBus' failed because assembly 'NServiceBus' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

First, I want to know if this is a completely crazy idea. I want the messages processed in order and added to the queues asynchronously. NServiceBus provides a host application that makes it very easy to spin up a new publishers when needed. The alternative seems to be to write a windows service that monitors the SSB queue and then publish. I'd just like some opinions or advice. Thanks.

Spirula

Answer 1

Take at look at the SSB Transport in NServiceBus-Contrib.

Portie

Yeah I looked at that, but it depends on 3.0.0.0 and I'm only allowed to run 2.6 at the moment. – Spirula

There is a 2.6 compatible version: github.com/NServiceBus/NServiceBus-Contrib/tree/v2.5 – Armes

It seems that the repository is not available anymore but there seems to be a clone: github.com/rmoritz/NServiceBus-Contrib – Argillite

Answer 2

I would probably go with the windows service polling model. Even through this is non-exciting and introduces latency, at least you can make everything nice and transactional and can handle failures in a well-understood container. I am not sure how the sql-clr performs under failure conditions, or conditions of heavy load. For example, could you de-queue from the service broker and then lose the message?

Just my 2 cents

EDIT

@Marco pointed me to the following MS sample for receiving SB messages into a windows service: http://code.msdn.microsoft.com/Service-Broker-Message-e81c4316

Ozone

And NServiceBus does not introduce latency there? – Baier

Not sure what you mean, but no, unless the NServiceBus implementation for SQLSB uses polling under the hood. – Ozone

I don't know that much about SQLSB anyway, but I was under the impression that the only way to retrieve messages from it was using pooling (SQL statements), after all it is a queue based table, but I might be wrong. – Baier

There maybe other ways. If you look at Oracle database queues (which are also just tables), OTP.net provides events which you can register a handler with. So when a message arrives in an oracle queue, the event fires and the handler executes. This is a much nicer solution and I would hope any implementation would do something similar to this. – Ozone

But events don't appear out of nothing, unless there using some COM object, maybe with support to callbacks, in the case of SQL Server I don't see how they could be using anything but pooling. – Baier

No idea. If you can be bothered git the contrib and have a look! I'm too lazy ;) – Ozone

Don't know if it doing pool under the woods but this looks like is uses some subscription: code.msdn.microsoft.com/Service-Broker-Message-e81c4316 – Baier

Looks interesting, thanks. Have editted my answer to include. – Ozone

It does indeed. No bother :) – Baier