This mystery often starts with the land of Replication Montor being very unhappy, providing a lot of red errors.
Unhappy-land in Replication Monitor can in this instance be relatively easily fixed.
If you manage to keep your cool and look deeper into the errors, you would probably see something like this:
Could not find stored procedure 'sp_MSdel_SalesCustomerTransactions'. (Source: MSSQLServer, Error number: 2812)
Something has happened with the procedures in the replicated database. It is not unlikely that someone with a bit too much access has done some tidying, or maybe a deployment has cleaned out the procedures needed.
Luckily it is not too hard to fix once you know what to do:
USE WideWorldImporters
GO
EXEC sp_scriptpublicationcustomprocs @publication='Replication'
GO
The procedure sp_scriptpublicationcustomprocs should be run in context of the database you have issues with (so not the replicated database) and you need to specify the publication name.
You will then be presented with the wonderful list of all the procedures that should exist in your replica database. If you know which are missing, you can go ahead and execute those, and if you don’t have a clue, you could run the whole script. Note that if you ever made any changes to the procedures, you need to remember what those changes was and apply those again, as the procedure will create the default replication procedures.
How does the sp_scriptpublicationcustomprocs work? In case you are curious, you can look at the blog from Percy Reyes to find more information (linked below).
It reads the replication metadata from your publication and executes inside another replication stored procedures such as sp_scriptinsproc, sp_scriptdelproc (or sp_scriptxdelproc) and sp_scriptupdproc (or sp_scriptxupdproc) for each article in order to generate the INSERT, UPDATE and DELETE procedures.
I ran into an issue with transactional replication setup that I thought I would mention here, as I spent quite some time to discover the cause.
In my setup the server SQL01 uses SQL02 as distributor. When I set up publication on a database in SQL01, the Log Reader Agent is created in the distributor instance. I used the same AD-account for the snapshot and connecting to the publisher.
Nothing fishy here.
The publication setup finished without a hitch, but I noticed that something was not quite working as intended.
Replication Monitor shows that Log Reader Agent is not happy.
Looking directly into the agent job logs I uncovered a surprising error:
Unable to start execution of step 2 (reason: Error authenticating proxy lab\repluser, system error: The user name or password is incorrect.)
Immediately I checked the password for the user, I changed the password to a simpler one, and triple checked that the account was valid. Then I checked usual Kerberos offenders like SPN and delegation. I tried to recreate the publication, and even re-establish the distribution setup. Nothing solved the issue.
The Event Viewer Security log did not make me much smarter either, but I was able to observe the error there as well.
“Unknown user name or bad password”… Not really helpful.
In the end I checked the service account for the Agent Service, and noticed something I had not seen before. The service user was defined as the full domain name “lab.int” rather than the short version “lab”.
The service will log in and work fine, but running jobs with different AD accounts seems to cause issues.
Change the domain for the service account, click “Apply” and restart the service as requested…
Fixing this small detail, made the Log Reader Agent much happier.
Order is restored in Replication Monitor land.
I was surprised to discover that the SQL Agent Service AD account actually was the culprit here, and since I spent a lot of time troubleshooting this odd issue, I thought it would be best to have it here as a reminder should I encounter this issue again. Below is the information from Event Viewer, so that it is searchable.
