I am sure this is a very basic question, so I apologize, but I've read lots of articles and done a lot of google searches and have not found any leads for this particular challenge I am looking to address. I appreciate any guidance that can be provided.
I have an enterprise environment where we need to have multiple instances of a COTS application spread geographically around the world. These applications are using SQL Server Enterprise Editions 2008, 2012, and 2014 (different versions of the COTS software) and have to remain in their geographic isolation. I have created an SSRS server to facilitate being able to give the customers access to their specific geographically isolated COTS database data. However, the demand has now grown where we want the users to be hitting a data warehouse for the SSRS data so we don't risk impacting our production systems. Additionally, my team would like to be able to aggregate the data for some enterprise reporting. I have read a bunch on data warehousing but I can't seem to find any solutions to bringing 14 different instances of a single COTS application into a single SQL server for reporting against. Ideally we'd like to be able to track the changes as well. The 3 databases per COTS instance are all named the same regardless of the version. Two of the databases are relatively static (maybe 200 updates per day each), but one is very active (70k+ writes per day). The solution doesn't have to be real time, but does need to at least offer a daily load.
Database 1 ~ 600 MB and 200 writes/day
Database 2 ~ 1.4 GB and 200 writes/day
Database 3 ~ 35-120 GB (depending on location) and 70k writes/day (all application events and audit) [old entries are never modified so would only need to send the delta]
So how can I bring in 42 Vendor Databases into a single data warehouse? Is there an approach better than the data warehouse to pursue? Thank you very much in advance, and if there is anything else that is needed to offer a solution, please let me know.