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?

  • Given that the structure of the tables in the databases I'm trying to replicate to my reporting database, and they are all housed on the same server, would I be better off making a view in that reporting database instead that is just ` create view tblX as select * from db1.dbo.tblX UNION ALL select * from db2.dbo.tblX etc ` – jspriggs Apr 11 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.