Where Are Replication Agent Jobs Running ?

by

SQL server Replication is one of the most interesting topic. Transactional Replication is most widely used for various purposes.

When any issue related to Transactional replication occurs, many DBAs face difficulty in identifying locations of Replication Agent Jobs. Snapshot Agent, Log Reader Agent & Distribution Agent are three main replication related jobs whose history need to be checked to identify the real cause of the issue.

In this article we will simplify the way to quickly identify the replication agent jobs location.

In Transaction Replication , We have mainly three type of database instances.

  1. Publisher
  2. Distributor
  3. Subscriber

Replication Publishing Model Overview :

https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replication-publishing-model-overview?view=sql-server-ver15

Distributor and Publisher could be on same instance as well. In this case it is called Local Distributor. Otherwise, it is called Remote Distributor. This could easily be identified by running below command at Publisher instance.

sp_helpdistributor

Distributor and Subscriber could also be on same database instance.

Where does Replication Agents run ?

  1. Snapshot Agent : This job will always be on Distributor Instance. If you see this job on Publisher, it means it is local distributor case. If you see it on subscriber ,it means subscriber and distributor are on same instance. So always look at Distributor.
  2. Log Reader Agent : Same as discussed for Snapshot Agent. So always look at Distributor.
  3. Distribution Agent : This Job’s location is decided based on type of subscription. It will be on distributor in case of Push Subscription. It will be on subscriber in case of Pull Subscription.

Now, How to identify Push or Pull Subscription?

Run below command at Publisher DB instance on database being published.

USE [PUBLISHER DB NAME]
sp_helpsubscription

The output of above query will have one column ‘subscription type‘. Value of this determines if the subscription is PULL or PUSH.
1 – Pull Subscription (Distributor Agent Job will be on Subscriber)
0 – Push Subscription (Distributor Agent Job will be on Distributor)

There is one more simple way to identify subscription type. In SSMS , GO to Replication Folder ,Expand Publication and Hover on Subscription. It will pop up a box which will tell distribution agent location.

I hope it will help you while troubleshooting replication issues.

Happy Learning !

Post Views: 91