Question

I m trying to determine if there s a better way to handle replication than the way we re currently doing things.

We re essentially trying to determine 2 things:

Is there any way to add an existing column from a table to replication without reinitializing the whole publication

Can you just select a specific article to reinitialize instead of all of the articles in a publication?

(I m a bit new to replication... trying to get up to speed, so I apologize if my terminology doesn t make sense)

Right now we have about 30 publications so that if any have to be reinitialized there s a minimal impact... because several of our tables are pretty massive. We d prefer to only have several publication.

Any ideas would be greatly appreciated.

Update

When we try to add a column to an article we receive the message:

You have changed one or more properties that require all subscriptions to be reinitialized. Saving these changes marks each subscription that supports automatic reinitialization to be reinitialized from a snapshot the next time its Distribution Agent runs. You must run the Snapshot Agent to generate the snapshot.

We want to avoid reinitializing all subscriptions.. we re using transactional replication... again we want to add an existing column to an existing publication without having to reinitialize all subscriptions.

Answer 1

What kind of replication? Snapshot, Merge, Transacitonal, Peer-to-Peer?

1) Yes. See Making Schema Changes on Publication Databases:

To add a new column to a table and include that column in an existing publication, execute ALTER TABLE ADD . By default, the column is then replicated to all Subscribers. The column must allow NULL values or include a default constraint.

To include an existing column in an existing publication, use sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or the Publication Properties - dialog box.

2) It depends on the replication type. See Reinitializing a Subscription:

Reinitializing a subscription involves applying a new snapshot of one or more articles to one or more Subscribers: transactional and snapshot replication allow individual articles to be reinitialized; merge replication requires all articles to be reinitialized.

友情链接