Let's say you have a situation where Business Objects is hammering your OLTP system and you have brought up a second box to serve as the OLAP side of things. Due to constraints, a datawarehouse won't be happening soon, so you need something on the OLAP side of things for reporting to run against.
What method would you use to get the data over to the new server? If the data needed to be refreshed every 12 hours instead of every 24 would that change the method?
As with many things, "It Depends". Primarily it depends on the amount of data being moved and the percentage of the SQL Server 2000 database that it represents.
If the data is relatively small or represents a small portion of the SQL Server 2000 database, then Alvin's suggestion of SSIS is probably the best way to go.
On the other hand, if the database is relatively large and the amount of data you need to move represents most of the SQL Server 2000 database, then you can write a script to take a backup on the SQL Server 2000 database, copy it, and restore it on the 2005 server. This works especially well with a tool that will compress the backup up such as Red Gate SQL Backup 6.
I would use SSIS onn the SQL 2005 box to move and manipulate the data, regardless of whether it's every 12 or 24 hours.
Do you have to copy everything? Or can you do differential updates? That would depend on the structure of the data, or the "information" in the data.
I worked on a reasonably large scale system in which we rolled our own solution, because the back end was responsible for configuration, and the front end was responsible for transaction processing.
The solution picked up rows from designated tables at each end, and performed the relevant inserts/updates/deletes on the back end. It was a lot of work, but it was good because we had real time replication with fine control and the logging we wanted. This was before SSIS was available, so you could probably do something pretty similar using that, as long as you use a good method of tracking which rows have had changes made to them...
