This afternoon I got a call from a client with a problem with their replication setup: transactions were not being replicated across to the subscriber. Below is a record of the steps I went through to investigate this issue.
Step 1: Review Replication Monitor
This is always my first point-of-call. I fired up the replication monitor (RM), expanded the publications node and viewed the details for the offending publication. On the surface everything appeared well, but there was no replication history in the details panel. Oddly, there wasn’t even a history of the original initialisation (3 days ago). Time to dig deeper.
Step 2: Review Distribution Tables
I queried two distribution tables: [distribution].[dbo].[MSrepl_commands] and [distribution].[dbo].[MSrepl_errors]. The first table keeps a historic record of the replicated transactions, while the second records any errors in these commands. The two tables can be joined through the xact_seqno field.
[distribution].[dbo].[MSrepl_commands] showed that there had been replicated transactions earlier that day. And there were no recorded errors with these transactions. I found this odd, because these transactions did not show up in RM which brought up more questions and doubt than it solved.
Step 3: Validate the data in the subscription database against the publication database
There is a snazzy little sproc that will validate the actual data at the subscriber against the publisher database:
sp_publication_validation @publication='<publication name>'
There are two validation methods. The first method (and quickest) just checks the row count at both nodes. If the row count matches it returns a message: “Generated expected rowcount value of <n> for <x>”, where n = number of rows and x = article. The second method actual creates a row hash and compares these.
In this case, the validation came back all clear. So it appeared that the subscriber was completely up-to-date. This suggested that replication was working absolutely fine.
Step 4: Tracer token
On the assumption that replication was perfectly healthy, I went back to the RM, drilled down to the publication and clicked on the “Tracer Tokens” tab. I inserted a token and watched it replicate across to the distributor and from there to the subscriber. The combination of Step 3 and Step 4 was enough for me to go back to the client and confirm that replication appeared to be functioning perfectly well.
I was a bit lost for ideas after Step 2. Google proved to be the way forwards. I googled: “SQL Server Replication Troubleshoot” and the first link led to a Technet Resource. At the head of this was a great article, Data is not being delivered to the Subscribers. I highly recommend starting here if you are having problems with replication.