Best option for Server dedicated to reports

[alebox20]

Nowadays I have one SQL Server 2000 and my ERP access it for transactions and reports.

A lot of them have high processing, but doesn't need real time data, I mean could be the data before today.

My idea is devide the servers, so I have one server for transaction and another one for report (this have to update itself once a day).

What is the best way to do it? Please give me some direction.

thanks.

 

[mrdenny]

Moving reporting to it's own server is an excellent idea. If you need the reporting system to be updated daily then DTS or SSIS will be the best bet for moving the changed records to from the OLTP database to the DSS database.

Only copy the tables which you need, and only pull over the records which have changed. Hopefully all the tables which you need have a column which says which records were updated that day. This is typically an UpdateDate column, but it can be a bit flag which is set to 1 when the record is updated and 0 when the data has been copied. I prefer the date column approach.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com