We process approximately 200,000-300,000 questions and answers a day from across the US in a learning database through an IIS based web interface on cloud servers. We'd like to move these records and their associated data to another cloud server and reporting database so we can take more time processing the data and create more reports and charts that would otherwise impact our production server and web interface. We intend to flatten (transform) the data so we can get a better feel for what the data indicates at any point in time. The questions are answered 24x7 but there are "light" times around midnight and on the weekends but still quite active.
We've looked at SQL Service Broker and triggers to move and transform the data as it changes as well as SQL Integration Services and SQL Data Tools to do the same. The output of this project should consider our system environment and describe the pros and cons of each approach. It should conclude with a high-level design of the recommended approach and a description of how it would work without needing to take into account the specifics of the database and its structure. Experience with Service Broker is the most important since it harder experience to find. Our longer term objective is to have the right person build the accepted design.