I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases. Note that all my databases reside on the same server, except the central database, which is on a separate server. I have evaluated a couple of options. My current thinking and related questions are below:
With SSIS I can do data transfer from the source to the destination databases; however, there is no change tracking. I have to track the changes myself and then extract and push the data manually. Apart from this I will end up creating a number of control flows and related data flows as the data sources are different which would be hard to manage in future.
Replication has change tracking, so the data transfer would be quick enough. However, I have to create those many publishers against each database. I am afraid server performance would be impacted due to this. I am also not sure which replication type to use, or if any performance issues could be handled with a better configuration. I am okay with a nightly update and data transfer need not be real time.
How should I proceed from here?