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.
Lets start with a simple issue that I have struggled with a few times. I try to start SQL Server Configuration Manager and is met with this puzzling message:
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]
Who cannot appreciate a error message blaming someone else for their mess?
The Internet list many culprits and most agree that WMI is not to blame this time. Exactly what causes the error seems to be somewhat random. Someone blames 32 vs. 64-bit mixed installations. I have experienced it after patching or installing multiple different instances on the same server, or even just patching an instance. Lets all agree it is caused by change of some kind.
Solution
Usually this issue is rather easy to resolve. Open CMD as administrator and type (copy) the following (take care to replace 000 with your SQL Server version code.
If you have the simple problem, then this will resolve your issue, and it might look something like the screenshot below. Your luck might vary. If the the easy to remember file name sqlmgmproviderxpsp2up.mof is not in the catalog, you might need to search a bit on your server to see if you have it lying around somewhere else.
If you do not recall version number, there is a list below. However you are always welcome to do as me and impress someone looking over your shoulder by trying and failing a few times.
Thanks for joining me! I will very soon start putting TSQL-scribles, thoughts, ideas and information that I need to remember and keep easily available.
Good company in a journey makes the way seem shorter. — Izaak Walton
