- I want to setup a one-way replication for 4 tables from the database on this server into a database on another server for the purpose of reporting.
- The total number of rows currently in the tables is as follows- Table1: 403060, Table2: 301223; Table3: 6984451; Table4: 812398
- Every day there are around 5000 records added to each table
- 10 minute delay is acceptable.
- There is no requirement such as - x number of update queries at source should result in x number of triggers on the destination.
I am exploring the use of snapshot replication or transactional replication. I understand that transactional replication is near real time and also supports the trigger example given above, although I am happy even if not supported. Where as snapshot replication will have a delay and doesn't support the trigger scenario. So both of them look like suitable for me.
I don't want to use log shipping because the replication needs to be done only for 4 out of the 25 tables.
Given my requirements, what is the performance overhead of snapshot replication vs transactional replication on the production server?
Is there any other recommended solution for this other than snapshot replication or transactional replication?