We have recently setup transactional replication on our test and production databases. As part of that change, we've had to make changes to how we create upgrade scripts as well. The goal is to make changes that do not require invalidating the current snapshot.

Thus far, we've had two significant changes:

  1. For stored procedures, use ALTER instead of DROP/CREATE. This involves creating a dummy empty procedure the first time (by checking if exists) and then altering in all cases.
  2. For renaming table columns, instead of sp_rename, add new column / populate column / drop old column.

I'd like to know what other types of changes we may need to make in order to successfully upgrade the database without invalidationg the snapshot.

  • Have you ever got a definitive answer to this? If so, it would be great if you could share your experience with others in the form of a self-answer. – Andriy M Aug 2 '17 at 8:23
  • Never got an answer. The two items listed are all that we've implemented. So far, so good. :-) – GaTechThomas Aug 2 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.