I've set up transactional replication between my on-prem database (SQL 2017) as a publisher and Azure as a subscriber.

My CHANGES (insert / update, alter stored procedure, alter table, etc) are coming over, but my NEW stored procedures aren't. In SSMS 17, I went into Replication > Local Publications properties and checked the new stored procedure in the [Articles] tab, but it's still not making it over.

It's not clear what I need to do to get the new stored procedure to come over. I looked at sp_addarticle but it looks like it would be the same that I did with the checkboxes in the GUI.

  • Why not just create missing SPs on subscriber ?
    – Kin Shah
    Commented Jul 11, 2018 at 20:55
  • @Kin because they can change after creation. Ideally they need to replicate just like the stored procedures that existed when we set up the replication. Commented Jul 11, 2018 at 21:59
  • How about this.Get alist of user stored procedures in a user database by querying information_schema.routines. Cross reference this list against the articles in your publication (dbo.MSarticles, dbo.MSpublications). If new procedure detected, call a process to add the article to the publication. I dont know all the calls that need to be made or what order, but I think you could find out by adding an article to a publication through the GUI and scripting to SQL. You then use the output to assist you in automating this process that runs 1x a night...just a thought Commented Jul 11, 2018 at 22:21

1 Answer 1


New objects need to be added since they are not part of the original publication when you established replication between the publisher and subscriber.

You can add objects to the existing publication w/out needing to stop replication activity. Please see this link and the section titled "Replication Maintenance"

  • I get that they need to be added, I'm just trying to figure out the best way how to do it, ideally without having to spend an hour regenerating a snapshot of the whole database. Commented Jul 11, 2018 at 20:47
  • Can't you create an article consisting only of stored procedures? In my former job we split data and schema into separate articles
    – Alen
    Commented Jul 11, 2018 at 20:55
  • Unfortunately a snapshot must be created when adding new articles.The distribution agent copies over the changes, but the existing publication is not reinitialized. Sorry I don't think there is any way around this.. Commented Jul 11, 2018 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.