I have a database that collects and process large volumes of recordsHowever 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 followsI could set up a schedule task with a stored proc to transfer the records with any logic neededThe company i work for is insisting i dump records to a file and ftp them and the use some existing logic to poll and insertSQL Service broker which seems to have built in fault tolerance, and has facilities to do this actual taskIm just wondering what some potential pitfalls of sql service broker might beand is it the right technology for the jobAnyone have any suggestions
Is SQL Server Sevice Broker appropriate for transfering large volumns of records from one database to another
sql server
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.