NServiceBus: Receive on SqlTransport, send (forward) to MsmqTransport

0
votes

Lets say I have a sql server database with a table T. When a row in T changes I would like to receive a message via NServicBus (Publish/Subscribe).

I would simulate a sent message by using a trigger on T that inserts a message in the table polled by a NServiceBus subscriber (SqlServerTransport). In the message handler I would like to publish (forward) this message to another NServiceBus subscriber (MsmqTransport).

I've looked at the SqlBridge sample but it goes the other way around (msmq -> sql server). I can't figure out a way for the IAdcancedSatelite to receive from a sql server transport i.e. I seem to only to be able to create a IDequeueMessages of type MsmqDequeueStrategy in GetReceiverCustomization().

Is this even possible? If not, my options seems to be:

1) Use SqlServerTransport across the whole system. I would rather not do this.

2) Brigde the endpoints with some other means of communication (rpc call etc.). Maybe this can be done with a Gateway and bus.SendToSites?. I'm new to NServiceBus so I have not looked into gateways.

3) Something else? I've seen solutions using the sql server service broker. Our DBA does not like that.

Any advice is appreciated.

2
sql-servernservicebus

2 Answers

0
votes

With V5 you can host multiple bus instances in the same endpoint instance, given that you can have one instance that is connected to SQL using the SqlTransport and the other connected to MSMQ.

The above requires that your trigger knows how to create a message in the raw SQL format that NServiceBus expects to find in the Sql Tables that represent the queues. To me it is not the way to go since you will depend on a NServiceBus table structure that can change in the feature without any notice since it is an implementation detail.

I like the Phil idea, even if IIRC the sql dependency misses the details of the changed row. I'd go with a mix:

  • in the trigger populate a different table with the details of the change;
  • use the SqlDependency on the "history" table to react to changes;
  • handle the change and mark the history row as handled or delete it;
0
votes

The simplest solution would likely be to use SQLDependency to detect changes in table T, and then publish an event via NServiceBus.

SQLDependecy does use the SQL Service Broker under the hood, so it does need to be enabled, but (IIRC) it does not require a lot of setup to get it up and running.