replication when some source records are deleted

[voxinforma]

I have a production server that contains sales data for the following years:
2002
2003
2004

I want to create a second, reporting (read-only) database containing all the 2002-2004 data, and then delete all but the 2004 data from the production database so that current transactions will process faster (I'm simplifying the scenario here). Thereafter, I want records inserted/modified/deleted on the production database to be mirrored on the reporting database, which should continue to hold all 2002-2004 data.

What is the best strategy for doing this? Thanks.

 

[Corran007]

I would take your current production database and copy it to the backup server, then just delete your data from production, then start up replication. if you set it up correctly it will be fine. if you replicate, it cannot be readonly. only way you could keep it readonly would be if you set up logshiping for it. yes this is simplifing it, but you should get the idea.

 

[nigelrivett]

You might consider keeping all the years in separate tables and accessing via a partitioned view - then you can keep all the data on the production database if you wish and it shouldn't affect queries that don't access the old data (look at the query plan to check).
Even if you don't keep all the data it might make it easier to manage.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[voxinforma]

thanks for the response. im leaning toward partitioned views as being far easier to manage than replication; also it seems too easy to make the error of launching a second snapshot of the source production data after the older records had been deleted, and then all the effort of configuring replication would have been wasted.

thanks again for suggestions.