We have 50 tables on our live Database instance. I need to replicate 10 of these tables for reporting and archving purposes. What is the best approach. Any good sites recommended for reference? Database is SQLServer 7.0.
I can tell you about our experience here. We are in the same boat that you are in, we have a production server and a reports server. I've implemented 2 types of replication Transaction and Snapshot.
Easiest to implenent, however if these tables have a lot of data could put a stress on the network and Disk IO on servers.
Required us to create indexes using PRIMARY KEY constraints NOT CREATE INDEX. A little more complicated, but less data during the transmission, therefore less stress on system
It also depends on how up-to-date the data needs to be on the reporting server. For use it was 24 hrs behind, so it represented data as of yesterday. In this case I could setup transactional to be every 4 hrs. For Transactional should know your production servers daily activity, highly active server with a large window for replication (ie. 4hrs for use) could cause system stress and therefore the replication window may need to be decreased.
A lot to think about also you need to know were you will place the Distribution Service, we placed ours on the Reporting server.