We're faced with a fun challenge. A server has 60+ databases to segment data due to privacy and security. We have 7 tables in each of these databases that need to be merged into a master dataset for reporting purposes.
I've been trying to tackle this issue with Transactional Replication, but am worrying that the number of publishers and over head of the jobs may be too much.
Would Replication be the best route? Should I look at trying to do an SSIS job to merge/join everything? Is there something else I am missing that could also be a good option?