Feeds:
Posts
Comments

Posts Tagged ‘LogReader agent’

Interesting one today:

This may be common knowledge, but upon running some tests its evident that Snapshot Replication does both the things:

  1. Deliver a complete snapshot (of all articles chosen) to the Subscribers
  2. Also delivers, incremental changes to those articles a.k.a INSERTS/UPDATES / DELETES

Or let’s state it differently:

  1. Snapshot and Transactional replication are two different replication types
  2. When we set up replication, we create:
    1. LogReader Agent : Reads TRN Log at Publisher and writes changes to Distributor
    2. Snapshot Agent: Creates a complete snapshot/backup/data of Publisher database to be created at Subscriber (but does not deliver it to Subscriber)
    3. Distributor Agent: This agent delivers data created in the above two steps to the Subscriber.
      1. Delivers the continuous changes that LogReader brings
      2. Delivers snapshot that Snapshot agent creates
  3. Transactional Replication:
    1. When we create Transactional Replication, the continuous activity at Publisher is replicated to Subscriber. This is carried out by LogReader Agent & Distributor agent.
      1. Distributor has replication procedures for INSERT, DELETE & UPDATE that take each DML activity at Publisher and re-play it as Subscriber
    2. But Transaction replication expects database & tables to already exist at the Subscriber
      1. And that the object structure matches — as the replication procedures (INSERT & UPDATE) depend on the Table & Column names and column order
    3. Creating Snapshot Agent is an optional step while establishing Transactional Replication. Its not needed to create Snapshot Agent, if the database objects already exist at Subscriber;
    4. And all we care about is replicating data from this point onward (and not the old data)
  4. Snapshot Replication:
    1. As part of creating Snapshot replication we create Snapshot Agent & Distributor
    2. We create all replication procedures (for each article) to replicate incremental changes on top of initial snapshot/backup/data

Summary

  • Yes, when we set up Snapshot replication, not only is the initial data from Publisher is replicated to the Subscribers, it also replicates incremental changes happening at Publisher to all Subscribers.
  • Snapshot Agent is different Snapshot Replication

 

Hope this helps,
_Sqltimes

 

Read Full Post »