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
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.
2