February 2, 2007 at 2:52 pm
I have a couple of tasks I wish to accomplish, but not sure if it is possible or feasible. I am using SQL2000 and SQL2005
I have 20 independant database servers (SQL2000) all running a very similar suite of applications. Currently they all have a common data dictionary, but are geographically dispersed and produce regional reports for their own specified server. (propriatary reporting tool)
I needed a backup strategy and now intend to backup all databases to a central repository database server(SQL2005 - With reporting services). As I am doing this I thought I could capatilize on the reporting problem I currently have. This is to amalgumate all the individual regional reports into an enterprise report with many options. I though of using the reporting services to accomplish this, so here are my questions:
If I were to run reporting services on this central server -
1) can reporting services create reports by accessing all 20 backup databases - as opposed to having to run 20 separate reports and joining them somehow? Ie bring up one report that shows all my regional data?
2) Would I have to create a 21st database with all the relevent information to getall the data into one database for generating this report?
Any useful links or ideas are very welcome as currently I am not able to determine how to get the reporting services to access all 20 databases for 1 report and really have no idea wheather this will kill the CPU when I try and do it. BTW - Database sizes are approx 10GB each, data rows read from tables for reports are approx 1 mill rows per database (max 10 fields probably less)
Until this happens I will be utilizing cut and paste and many excel sheets :'(
** What you see, Depends on what you Thought, Before, You looked! **
February 2, 2007 at 3:37 pm
1. Yes, although I'd recommend you use a stored procedure to join them all together, instead of having RS do that work. We have single reports that talk not only to multiple databases, but even have ones that have dropdowns created from Oracle datasources, with report data coming from a combination of SQL Server and Oracle datasources. If table structures are the same for all databases (it sounded like each database was a region in your setup), then you might look into using a view as a wrapper for each table, and let the stored proc just work with the views.
2. No, although you might want to create one to hold the stored procedures/views I recommend in the above answer.
February 5, 2007 at 1:27 pm
Thanks for that reply, I feel happier I am persuing the correct direction to attaining my goals with this project. I totally forgot about creating a view that can span all the databases and basically concatenate the specified tables within the individual databases.
Of course I have another question (as is always) :
For the central database is there a configuration variable that I should look at to help in threading the "view" , so that when a report is requested and the view is being accessed, it could theoretically spawn off 20 threads to get the relevent data from each database/ table?
I did have a look at several flags, but am not sure they will work in this instance. i.e cost threshold for parallelism- is one that looks like it may help?
I am looking at having the central repositary as a 4 way or quad processor.... will that buy me much in performance - or is it not really worth it for the report timings I could gain ?
Once again thanks for the feedback.
** What you see, Depends on what you Thought, Before, You looked! **
February 6, 2007 at 4:32 am
While I don't know enough about your data, you might be more concerned about performance issues than you need to be. A view spanning 20 databases really isn't that bad typically, and if your particular needs are atypical, there are usually ways of handling that as well.
For example, if your tables are huge, but divided into regional databases, and if the users typically, but not always, pull reports by region, then perhaps a horizontally partitioned view (on region) would work best for you. Another possible path, depending on your needs, would be indexed views.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply