Few days back there was a replication issue in one of the QA servers. Since the DBA was out of office that day, I was called in to look into the issue. Though I am not an expert in replication, I was able to identify the issue. Just wanted to share the troubleshooting steps that I performed. This is for beginners only. If you are an experienced DBA with Replication in your arsenal, please review and correct, if anything is interpreted wrongly.

Replication has 3 components, Publisher , Subscriber and Distributor. In simple term Publisher is the source of data, Subscriber is the target (one which receives the data) and Distributor is the facilitator. You can read about the Replication terminology here.

My QA was able to give me the publisher server name. So  I had to

(a) find out the Distributor and Subscriber server.

(b) find out what are all replicated

(c) find out where the error occurred, in Publisher or Distributor or Subscriber

I logged into the Publisher to figure out the servers of Distributor and Subscriber. In the SSMS you can  find a separate folder for Replication

Right clicking on the Replication folder brought up the below property screen, which tells where the Distributor server is located. Note that Distributor may be on the Publisher itself or in another SQL SERVER.

Clicking on the “Publication Databases” displays what are all databases published from this server. Also if you note carefully, it gives you the type of Replication also. It is not necessary that all the tables or rows or columns are published in a single publication.  We can set up  multiple replication. We have to identify in which replication (publication) our objects are published. You can get this by expanding the “Local Publication” under Replication folder in SSMS.

The “Articles” lets you know the objects that are published under the specific publication.

The other property that got my attention was whether the subscription is push/pull subscription and also whether any schema changes are also published.

It took me a while to figure out the Subscriber, however in the process I noticed one more tool, “Replication Monitor”. You can launch the Replication Monitor from SSMS by right clicking on “Replication” folder.

In the Replication Monitor you could see your publisher listed. Just click on the Publisher and in the right side window, you will see all the Subscriber listed

In the above screenshot, the  Subscriber is in error status. Just double-click on it to the subscription history.

Note that in the above screen, you can find 3 tabs, Publisher to Distributor, Distributor to Subscriber and Undistributed Commands. In a single screen you can find, not just the status but the reason of failure, of all the publication from the current publisher.