5

I'm trying to set up transactional replication between two SQL Server 2008 R2 databases and, no matter what I try, it just doesn't seem to work, and I'm not sure where to look for further information about what may be going wrong.

I've created the publication and subscription by running through the wizards on both servers, and everything appears to be up and running - I get successful messages on the Publisher that it's created the publication and added the articles to the publication etc. I also subscribe the subscription database successfully to this publication. The replication monitor shows everything OK - but the synchronisation status of both the publication and the subscription always show as "No replicated transactions available" even when I manually add / modify data in the publication database.

I've tried deleting the replication data from both servers, dropping and recreating the databases from backups, reinitializing the subscription database etc all with no joy.

Any advice on where to look for some more info on what may be going on would be gratefully appreciated.

Improve this question
5
  • 3
    Are you sure that the data you are working with is part of the articles in your publication?
    – Thomas Stringer
    Jul 21 '13 at 15:40
  • Yes - when I initialise the subscription, all the tables are created and populated with the data. All the tables, stored procedures etc are there as expected. Just nothing else gets replicated after that.
    – David Heggie
    Jul 21 '13 at 18:05
  • 3
    Just because the wizards succeed doesn't mean things have been configured correctly. I've found that in particular, the security settings are very picky, yet don't fail any kind of validation checks. I would start with verifying that. I found this article very helpful -- be sure to go through the list meticulously for your scenario.
    – Jon Seigel
    Jul 21 '13 at 18:39
  • Thankyou Jon - that article was a big help. I've managed to get it working now by, as you say, following through the list of permissions very meticulously. I think it was an issue with the account that the log reader agent was running under. Thanks again for that - it was a huge help.
    – David Heggie
    Jul 21 '13 at 20:09
  • You're welcome. Glad to help.
    – Jon Seigel
    Jul 22 '13 at 1:52
3

SQL Server Replication can in some situations hide errors from you. The behavior you are seeing could for example be caused by the snapshot agent not finishing, likely because of an incorrectly configured permission set.

Check out my Stairway to Replication here: http://www.sqlservercentral.com/stairway/72401/

Levels 1 - 5 give a solid foundation to the security requirements when setting up replication. Level 10 describes how to troubleshoot a replication setup. This includes information about where to find error messages and how to interpret them.

Improve this answer
1
  • Thanks for that - some good information there. I managed to trace the problem down to incorrect permissions on the account that the log reader agent was running under, and all appears to be working correctly now.
    – David Heggie
    Jul 22 '13 at 13:52

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.