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.
Best Answer
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.