I am running reports from my database on Server 2008, using Sql server 2008 via my ASP frontend. I can run reports on around 400 records, when ever I try to export the whole database to an excel worksheet using the frontend the database becomes sluggish and unusable. I haven't had enough time to pin point the cause of the issue, however I wanted to ask the question is it best practice to have two databases one for production and one for reporting services? How would this be implemented? Trans Logs / Copy only... and then how can I get the reporting database to automatically update it self/sync from the production database? The reporting database does NOT need to be real time up to date just current. Any advice would be great.

Hi, A 400 records report shouldn't be killing your server. If so, you have a severe performance/tuning problem that should be solved immediately, before thinking on replication, log shipping or whatever.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

I totally agree, however the 400 record report works fine it's just when I try to do it with around 30,000 record. Even this shouldn't have the kind of knock on effect. I wouldn't say its killing the server, but its killing the front-end some how. I will do some investigation into what is going on tomorrow.

Hi, Try isolating the dataset queries and running them on SQL Database Engine Tuning Advisor. Maybe you're missing a few indexes or a statistics refresh, or the queries themselves have to be rewritten.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu