Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 sproc, and set properties of This Stored Procedure Article, but 'Replicate' is grayed out. Ok - so I go to the sproc 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 lies.

If I go back in and check the properties for all sprocs, 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 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 - thankyou!

share|improve this question

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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