0

I am trying to add a few new articles to an existing sql server replication publication. The publication is for one way transactional replication. I have done this multiple times on multiple servers, and in fact have done it several times on the server I am working on now. This time I am running into problems.

I am using the ssms gui to add the articles, by going to the publication properties, selecting the new articles to include, and clicking ok. Once that is done, I rerun the snapshot agent, which shows the new articles and it finishes successfully.

The problem is that the new tables are never created on the subscriber. The tables are missing, as well as the associated stored procedures (sp_MSins*/sp_MSdel*/etc). I don't see any indication of errors that are occuring during the process though.

If I leave the new tables in the publication, eventually all replication grinds to a halt, as it tries to push data changes to the affected table, and the replication agent jobs are unable to find the required tables/stored procedures. I have removed the affected articles from the publication and now transactions are being replicated again as expected.

At this point I really don't know whats going on, I don't see errors in the logs on either side, and I am not sure what my next step is, besides opening a case with microsoft, any advice for a solution, or even just someplace else to look for errors in the process would be appreciated.

  • 1
    I worked a lot with sql server replication but I never saw this behavior. Could you try to add articles using sql command instead of interface just to see if the behavior continue. – Krismorte Apr 23 at 14:25
  • The trouble with Transactional Replication is that it's almost trouble-free-enough that you don't have to have a deep knowledge of how it works and be able to troubleshoot it. almost but not quite. Start here docs.microsoft.com/en-us/sql/relational-databases/replication/… – David Browne - Microsoft Apr 23 at 14:28
  • What is the version of Management studio that you are using? – jesijesi May 1 at 2:47
1

I reached out to another DBA, and they said they had seen similar issues in the past, without a clear cause, but that adding the articles 1 at a time had resolved the issue, that seemed to work in this case as well. Adding 1 table at a time vs adding 10 tables at once allowed the tables to be created, and they are now replicating successfully.

| improve this answer | |

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.