2

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 Jul 11 '18 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. – Scott R. Frost Jul 11 '18 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 – Goforebroke Jul 11 '18 at 22:21
2

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. – Scott R. Frost Jul 11 '18 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 Jul 11 '18 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.. – Goforebroke Jul 11 '18 at 20:55

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.