October 16, 2007 at 2:56 am
Hello Everyone,
I am currently looking at designing a reporting database for users to query data, there are massive/intensive reports that runs on the database. Below are the current challenges that we face:
The database is getting huge in size due to expected data growth
The size of the database is making crucial tasks such as data backup take ages
The size of the database means that archiving/house keeping needs to be done from time to time.
Performance is slowly degrading.
The data isn’t normalized and wouldn’t be worth the time and effort of normalization
The data is static, so once written to the database, is very unlikely to change.
I am a bit skeptical about using SQL reporting services, as users are not technical at all, and they would need a reporting tool so easy to use with minimal or no technical knowledge, I am not sure if SSRS is an ideal tool here
Proposed solution
A proposed solution is being proposed to design a new reporting application, the new database will be based on SQL 2005 standard edition, so the idea of having partitioned tables, online indexing etc is out of the question.
At the moment, I am looking for new ideas.
The new idea must solve the following problems
Allow data backups to be simpler and faster and also faster restores
Allow for fast querying of data
Faster loading of data from flat files
I look forward to hearing from you all.
Thanks
October 17, 2007 at 3:32 am
I face the same issues at work. I think that there is no magic to solve this. I basically use jobs to backup the database during offhours. Maintenance is also automated using some jobs. I created more filegroups and moved some less-used tables to them in order to have smaller files.
I use sql profiler a lot to try and determine which application are using slow/repeated sql statements and try to optimize them by tuning indexes, but I think the optimization % gained is small(ish).
In my experience, the only reporting application that users can use without much training is MSExcel :P, so the best would be to develop an interface between the database and Excel to output the results into worksheets according to user-selected criteria and let the users do the formatting themselves, or create inside Excel some vba/macro routines that generate the reports using some sort of templates.
It's not the best solution, but it works here and the users don't complain because all they are more interested in flexibility.
Best regards
Hugo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply