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