Be familiar with important terms of replication in SQL Server

Be familiar with important terms of  replication in SQL Server

After reading replication articles two of my regular blog reader asked me question about some terms I have used in articles. Here is their asking in question format for all blog reader.

Q.1) What is PULL replication which you have used in almost all your replication article?

Ans.) PULL and PUSH are two different types of replication which SQL Server offers.

PULL: PULL subscription demands the data from publisher as needed.

PUSH: PUSH subscription pushes data from publisher to subscriber via distributor based on configuration eg: on demand, on schedule basis or continuously.

Q.2) What is “Snapshot Agent” which is used in Snapshot Replication and transactional replication in your articles?

Ans.) While working with replication, you have to familiar with following five main agents.

Snapshot Agent: Snapshot Agent plays very important role for any type of replication and runs from distributor database. Snapshot agent keeps information about synchronization; prepare schema and data for initial transfer.

Queue Reader Agent: This agent comes into the picture if you have snapshot/transactional replication configured with queued updating option, again this agent runs in distributor database.  It reads messages stored in a Microsoft SQL Server queue or a Microsoft Message Queue and then applies those messages to the Publisher

Log Reader Agent: – This Agent is used with transactional replication. It moves transactions marked for replication from in transaction log from Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor.

Merge Agent: Merge Agent comes in picture when there is a merge replication setup. It applies the initial snapshot to the Subscriber and merges incremental data changes. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber to update it. Unlike snapshot / transactional replication, merge agent runs either from distributor or from subscriber.

Distribution Agent: Distribution agent runs either from distributor or from subscriber based on your configuration in snapshot / transactional replication. Initial snapshot will be transferred from publisher to subscriber via distribution agent. It also moves all transaction held under distribution database to the subscriber.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

One thought on “Be familiar with important terms of replication in SQL Server

Comments are closed.