0

I have a database that collects and process large volumes of records

However due to the legacy nature of the system im working with (and the powers that be) i need to transfer (processed) records to a different database instance (with some small field modifications along the way)

The options i have identified are as follows

  • I could set up a schedule task with a stored proc to transfer the records with any logic needed
  • The company i work for is insisting i dump records to a file and ftp them and the use some existing logic to poll and insert
  • SQL Service broker which seems to have built in fault tolerance, and has facilities to do this actual task

Im just wondering what some potential pitfalls of sql service broker might be and is it the right technology for the job

Anyone have any suggestions

1 Answers1

0

I've written a system to handle offline processing with Service Broker as its core. It's not the most intuitive process, and it definitely sounds like overkill for your needs. A small sproc called by a scheduled task will definitely be easier to program and maintain. If you try Service Broker, I can promise you a steep learning curve and some diagnostic bumps along the way.

mgw854
  • 1,828
  • I guess the fault tolerance component of service broker is what is so attractive, i imagine (dreams of grandeur), that once the appropriate pages (or what ever they are called) are queued its set and forget. however i may be totally deluded. So your vote is squarely in the scheduled task column i guess –  Jul 09 '14 at 03:52
  • Fault tolerance is nice, but it comes at the cost of a lot of overhead. Endpoints, contracts, queues, activation decisions, etc. Messaging is a royal pain since you have to use a cursor (there's no set-based way to send Service Broker messages). It's also not "set-and-forget"... you need to ensure end-to-end delivery and closure, or else tempdb will explode on you. There are certainly some great uses for Service Broker, but I don't think you need it quite yet. – mgw854 Jul 09 '14 at 23:32