SQL 2008 Transactional replication and agent accounts
Posted on 2013-12-23
I'm having problem setting up transactional replication.
Once I set up publication, it seems that it generates snapshot file successfully. But then when I create subscription from another server, both publication and subscription in Replication monitor shows 'Retrying failed command'.
I think this is something has to do with the accounts I selected when I create publication and subscription.
What I did is, I created a new SQL account, then gave db_owner and publication for the database and make it as sys_admin then I set up publication with it.
Then on subscriber, I created another SQL account, the same name I created in publisher and gave same permissions for login and database mappings.
When I open Repl mon, it shows the account I use still can't make snapshot agent to go alive. I wonder how it successfully created the initial snapshot, then.
What are the proper account setup for replication agents on both publication and subscriber?