4

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

migrated from stackoverflow.com Jan 6 '14 at 12:11

This question came from our site for professional and enthusiast programmers.

  • Could it be the fact you have different Service Packs on your platforms? – Mihai Bejenariu Jan 6 '14 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 '14 at 14:22
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.

  • Thank you for the valuable link. The question remains as long as it is performing well on PRE-PROD platform. – Mihai Bejenariu Jan 6 '14 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. – Brandon Williams Jan 6 '14 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, privacy policy and cookie policy

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