Monolog conversations in SQL Service Broker 2008

1
votes

I have a scenario in which I need to process(in SQL Server) messages being delivered as .xml files in a folder in real time.

I started investigating SQL Service Broker for my queuing needs. Basically, I want the Service Broker to pick up my .xml files and place them in a queue as they arrive in the folder. But, SQL Service Broker does not support "Monolog" conversations, at least not in the current version. It supports only a dialog between an initiator and a target service.

I can use MSMQ but then I will have two things to maintain - the .Net Code for file processing in MSMQ and the SQL Server T-SQL stored procs. What options do I have left?

Thanks.

2
sql-server-2008service-broker
I'm not sure why you would need monolog conversations. You can always simulate monologs using dialogs. Could you clarify what exactly you want to achieve? – Pawel Marciniak
I don't have control over the source of my input messages. They are are being dropped in a directory folder on the server. My idea was to have a monolog conversation between my service which picks up the xml files and processes them in sql server. If I had some degree of control over the source of input messages, I could ask them to publish the message straight to the my initiator queue in sql service broker. But, that's not the case. The message s are sent in .xml format in a directory folder. – user352

2 Answers

1
votes

You'll want to leverage the FileSystemWatcher to monitor the directory. Your implementation can simply respond to new files and use the event to queue processing of the file(s) (which could implemented in Service Broker if that makes your life better).

As the other posters have mentioned, you're really got things backwards: Service Broker responds to messages; someone must send a message for it to respond to. It is not a generic service host process. Depending on the feature set and scale out/up requirements, you might want to look at BizTalk as this is a very common pattern implemented with it and it has TONS of infrastructure to support all the orthagonal "cost of doing business" components to make the thing be reliable and actually work.

Once you're done writing/debugging all the required code on your own you'll often find you've spent more $ than the licenses cost. Again though, it's all about requirements.

0
votes

None. The whole idea you have is broken - as you have to pick up the files from a directory, the use of service broker simply does not make sense to start with. YOu need a listening process, so you can have the listening process do the processing, too.