Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 0 down vote accepted

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.

share|improve this answer
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 – Saruman Jul 9 '14 at 3:52
By the way thanks for the reply – Saruman Jul 9 '14 at 3:53
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 9 '14 at 23:32
Great perfect answer thanks, ill think ill accept this – Saruman Jul 10 '14 at 0:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.