Best Technology for Reporting.

Current Environment:
SQL 2000 SP4 EE on Windows 2003 SP1
I need some suggestion on some of the currently available options for
reporting.
We have a transactional database where lots of transactions come throughout
the day. Some of the tables can have over 200 k records added. We need to
aggregate data on another server for reporting purposes and currently all
this is done via DTS jobs which do lots of locking and time consuming.
Since we are looking into re-architecting this solution and go away from
DTS, I am looking for various options that we can set up in test environment
and see which one supercedes over another one. Also, should I look into SQL
2005 or stay with SQL 2000 only.
Please advice.
Thanks in Advance."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:8F1E6BD8-A36D-4D3E-8799-C67DF4F19D9D@.microsoft.com...
> Current Environment:
> SQL 2000 SP4 EE on Windows 2003 SP1
> I need some suggestion on some of the currently available options for
> reporting.
> We have a transactional database where lots of transactions come
> throughout
> the day. Some of the tables can have over 200 k records added. We need to
> aggregate data on another server for reporting purposes and currently all
> this is done via DTS jobs which do lots of locking and time consuming.
> Since we are looking into re-architecting this solution and go away from
> DTS, I am looking for various options that we can set up in test
> environment
> and see which one supercedes over another one. Also, should I look into
> SQL
> 2005 or stay with SQL 2000 only.
> Please advice.
> Thanks in Advance.
If the jobs are doing that much locking, then I would probably look at
rearchitecting the jobs themselves. Take advantage of the WITH (NOLOCK)
hints where appropriate etc.
Having your reporting done on the live data with the same aggregations that
your DTS jobs are doing doesn't seem like a particularly good idea to me.
Your performance will be better on the aggregated data already stored in the
reporting database server.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBAsql