1

Requirements:

  • 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.

Questions:

  1. Given my requirements, what is the performance overhead of snapshot replication vs transactional replication on the production server?

  2. Is there any other recommended solution for this other than snapshot replication or transactional replication?

Improve this question

2 Answers 2

Reset to default
1

Answers:

  1. "Given my requirements, what is the performance overhead of snapshot replication vs transactional replication on the production server?"

At the amount of data you're talking about, unless your tables are really wide (lot of columns / large data types) the performance overhead of either will likely be pretty negligible on the Publisher server. But depending on the read requirements of the Subscribing server, the overhead will depend on how frequently you schedule the Snapshot to run, should you choose Snapshot Replication.

When Snapshot Replication reinitializes a Subscriber, it DROPs (or TRUNCATEs if you configure the Article options accordingly) all of the published tables at the Subscriber, and then re-creates them, thus re-inserting all of the rows of that table. You'll find increased downtime due to the table not existing and / or locking at the Subscriber every time this occurs, especially regarding your larger tables in the millions of rows. The entire job for Snapshot Replication could potentially take longer than 10 minutes based on the sizes of your tables, roughly guessing from my previous experience.

If you plan to run the job for Snapshot Replication multiple times a day, then in my opinion, Transactional Replication would be the better choice here since with Transactional Replication you don't normally need to recreate the entire table at the Subscriber (except with specific DDL changes or when certain replication issues occur that require a "turn it off turn it on again" solution). Transactional Replication will just continue to incrementally update the published tables at the Subscriber whenever a change occurs to them (in near-realtime), for only those new changes. With only 5,000 rows being added per table per day, that's a very tiny amount of data changes that realistically shouldn't take more than a few seconds to replicate.

  1. "Is there any other recommended solution for this other than snapshot replication or transactional replication?"

There's also Merge Replication which is useful for when you have tables that need to be replicated but don't have a primary key on them. Though Transactional Replication is usually the primary choice with the scenario you described, if you only need to replicate in one direction.

Improve this answer
1
  1. Given my requirements, what is the performance overhead of snapshot replication vs transactional replication on the production server?

Obviously, transactional replication will be "cheaper" in terms of resource usage (IO, disk space, CPU). Imagine copying ~ 8.5 M rows (combined) each day (or whatever the schedule will be) in snapshot replication vs copying "just" 20 K rows daily in transactional replication

  1. Is there any other recommended solution for this other than snapshot replication or transactional replication?

Given that you don't want to copy rest 21 out of 25 tables - log shipping, database mirroring, availability groups is not recommended for you. Snapshot replication seems like a waste of resource in your case, so you are left with transactional replication

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.