Monday, March 12, 2012

Multiple database question

I am planning to develop reports using reporting services. My reports will use data from 3 different SQL Server databases that are application specific on three different cpu's. I'm planning on having a reporting database that is a common repository for data extraction / summarization from the other 3 databases.

I'm trying to understand the best way to approach my design (it's been a while since I've done database work - pretty obvious...):

Use Analysis Services to move the data from the three databases to the reporting database server.

Create some stored procedures that can do this.

I'm not seeing the reporting services as being a "data movement" tool but more of a shell data access tool (correct?).

Other tools someone would suggest.

Thanks and any help would be appreciated.You could also look at replication,DTS, and even log shipping.|||Hi there,

If your (or some) reports need to COMBINE the data from the three databases, you definitely need a common data access like you mentionned. But if you have "Report A" that needs the data from "Database A" and "Report B" from "Database B", that's a breeze.

Each report has it's own datasource setup. That's it!|||tootoo, there's nothing wrong with your solution. However, the snapshot approach might be needed as reporting can sometimes crucify the perf of a database.

No comments:

Post a Comment