5

I created a transactional replication on PRE-PROD (via TSQL) and everything works as expected. On PROD platform replication is not working. While inspecting Replication monitor, I get this error message for Log Reader Agent:

The job failed. The Job was invoked by User sa. The last step to run was step 2 (Run agent.).

enter image description here

Any idea why I get this error?

SQL Agent is running under context of "NT AUTHORITY\NETWORKSERVICE". Configuration and security seems to be identical on PROD and PRE-PROD.

The publication and subscription have been correctly created. Everything is running on a single server, in the context of a user that is sysadmin and db_owner for publication and subscription DB:

enter image description here

2
  • Could it be the fact you have different Service Packs on your platforms?
    – bjnr
    Jan 6, 2014 at 11:40
  • Adding a verbose log to the Log Reader Agent job as explained in support.microsoft.com/kb/312292 would give more details for the job failure.
    – Nomad
    Jan 6, 2014 at 14:22

2 Answers 2

1

The recommended approach is to have replication agents run under Windows accounts, not the SQL Server Agent service account, and the accounts should be granted only the required permissions.

Create a dedicated Windows account for the Log Reader Agent, grant it the appropriate permissions covered in Replication Agent Security Model, and use this account for your Log Reader Agent Security.

2
  • Thank you for the valuable link. The question remains as long as it is performing well on PRE-PROD platform.
    – bjnr
    Jan 6, 2014 at 18:28
  • 3
    Something is different in your PROD environment then. Maybe a security policy or something else. Also, there is additional details that accompany this error message. Look at the Log Reader Agent job history and look at the previous step, it will contain additional details about why it is failing. Jan 6, 2014 at 19:24
0

I have had this problem, since then I never use NT AUTHORITY\NETWORKSERVICE for anything anymore.

I create dedicate active directory accounts to deal with replication. For example DEV\SQLReplication for the dev environment, and MY_DOMAIN\SQLReplication for production.

Give them a good password and you are good to go.

another thing I do sometimes, depending on the size of the snapshot to be generated, is to drop the subscription and create it back from scratch. I only use this when everything else fails, Including reinitialization.

Here you can see the scripts that I use to create a subscription in Transactional Replication.

Here you can see the scripts I use to check what has been read by the log reader and whatever and get extra information about any errors related to this

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.