I'm trying to get stored procedures to execute on the subscribers, as seems to be best practice. I already have transactional replication running happily.
I add a new stored procedure, and set properties of this stored procedure article, but 'Replicate' is grayed out.
Ok – so I go to the stored procedure root and set properties for all stored procedure articles. Under 'Replicate' I set it to: "Execution in a serialized transaction of the SP", which is the recommended setting.
Yay! All is good!
But SQL Server lies.
If I go back in and check the properties for all stored procedures, it's changed the 'Replicate' setting back to "stored procedure definition only". It doesn't actually keep my change.
The only mention of this issue I could find was here: http://www.informit.com/articles/article.aspx?p=343672&seqNum=4 where the solution is:
"To make stored procedure execution options available, you must drop any subscriptions from this publication and drop the article from the publication. After you add the article back to the publication, all options will be available to you again."
So – apologies for the long pre-amble, but here's my question:
If I remove my subscribers, make my necessary changes, and add the subscribers back, is SQL Server going to want to create a full snapshot?
I'm asking because a full snapshot takes a few hours & chews up our server, so it needs to be carefully planned.
Any insight anyone can provide would be awesome – thank you!
Best Answer
When you create a publication, you can choose whether or not you want to create a snapshot. Similarly (and more importantly), when you create a subscription, you can say if you want it to initialize or not. Choosing not to is probably what you need, but I suggest you test it before doing it in production