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:
- 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.
- 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.
