Home Forums Reporting Services Reporting Services Design Question: What is the best practice for reporting on data that spans instances? RE: Design Question: What is the best practice for reporting on data that spans instances?

  • I think option #2 is probably the simplest option since Linked Servers aren't an option. Implementing option 2 could be as simple as a single SSIS package that runs on the central server and consolidates the data. AS long as the table(s) you are consolidating are consistently named, you could have a table in the central server where you store a list of servers to collect the data from and then use a loop in SSIS to connect to each of the servers and load the data, then when a new server is built out, all you have to do is add a row to this table to add it to your consolidation job.

    You should search for Rodney Landrum DBA Repository as that might be the kind of template you'd want to follow.