We are using SQL 200 transactional replication. We have a need to update/add
various objects (tables, columns, stored procedures, etc.) I was wondering
what the best/cleanest way was to do this in order to make sure all the new
changes get replicated.

I am proposing the following:

1. Remove all the objects to be modified from the subscription.
2. Make updates to existing objects
3. Add new objects
4. Add existing and new objects to subscription.
5. Run spanshot.

While I understand that this may not be the fastest method, I was wondering
if there were any gotyas I should be aware of?

Thanks,

For everything but tables you should place them in a snapshot publication
and then run the snapshot and distribution agent when you make a change. You
can also use sp_addscriptexec for unc deployed snapshots.

For table changes use sp_repladdcolumn and sp_repldropcolumn. This will
handle most schema changes. However if you are changing a datatype, or a pk
column, or a column which has an index or constraint on it, you should
remove the article from the subscription, make the change and then add it
back.

Gotcha's typically revolve around dependencies. Ideally you will not
replicate dependencies when creating your publication.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]