Hi,

I have to design a side report database system (using SQL Server 2005) for a software used on several separate systems. Each separate instance of the software will insert in the database some data relative to the users' performance and some other meaningful data.
The idea is that an administrator will be able to see reports (maybe using Cristal Reports) on the data of all the different systems at the same time.

1 - should I have a different database instance for each system?

2 - Assuming that the 1 is yes, since the report application has to query from different remote databases, I wonder how the performances could be affected. Some of the tables are supposed to grow pretty fast and might contain million of records. What do you think it is better: querying the different and possibly huge remote databases from the report application or having the databases copied (again, the tables are huge and so the complexity could be high) in a central server and query the data from there?

Thank you in advance!

This is just my take on this:

Trying to run reports on multiple servers at the same time will produce terrible performance. So you're left with pulling the data across to a single central reporting database.

Should you pull all the data or just an aggregate of the data that's useful for reporting is more difficult to say. How much new data are you expecting and from how many sources? Do you report on the lowest level of the data or can the data be aggregated somewhat? Are the separate systems just copies of the same database running in different locations with different data or are they just loosely similar data or are they totally different systems with no similarities?

A few million records isn't a huge amount of data by any means and the size of the table doesn't make things more complicated but you could always just store aggregate data in the reporting database and then allow the user to extract more detailed data from the source database but this is more complicated to write and maintain. If the data needs to be more up to date than a daily down load then you could simply pull the data more often but this might interfere with reporting.

Hi Mike,
thank you very much for replying!

Well, those tables are supposed to contain all (and only) the information needed for the reports. I guess some queries will join some tables and will use some aggregate functions.

Yes, the separate systems would be just copies of the same database, so it is the same schema and the tables will be copied in the central reporting database which will have pretty much the same schema, maybe with just 1 additional column to make distinction between the different systems.

I think that a daily upload is more than enough. If you think that millions of records in not that bad, do you think that a 'nightly upload' from the different databases tables to the central reporting databases is not that bad, in terms of performances?

Thanks in advance!

Few more questions:

• Do you need to download the all the supporting data each day and from each system or is it the same and rarely changes?
• Can't you just download the new data each data rather than the whole 1m records from each system?
• Are all the systems in the same timezone?
• Curious why you didn't just use one database in the first place?

The speed to download shouldn't be that bad if you're not downloading all the data each time but it would be easy to test. A wild option is to download a complete database dump from the remote system into a local database and then do the transfer from there - repeating for each system. Reporting on millions of records is straight forward but you must be careful on indexing or at least limit what the users can report on.

Hi Mike.

I apologize because I was not clear.
The software we use keeps separated the information belonging to different logical systems. It could be only one copy of the software running via terminal server, or different installation of the software in different locations (maybe with different timezones). In any case, there is a separate database for each logical system.

What I have to do is designing a new reporting system that can correlate the information belonging to these separate systems.

So what I was thinking was
1 - I have to add some new tables (same schema) to each separate (already existing) database, in order to collect the operators performances separately for each systems (new software feature)

2 - since I can not assume the all the different databases reside in the same sql server, I was thinking that the reporting system (let's say Crystal Report) should operate against a central dedicated database (since you stated that reporting against remote databases will be bad); it will consist only on the new tables (the ones I have to add) with an additional column which will represent the system. This way, the records have to be imported (yes, only the new ones) from each separate system to this central report database.

I was wondering about the complexity, but it should not be that bad...

what do you think?

Thanks!