Change the clustered Index on a replicated table

Question

Change the clustered Index on a replicated table

vky3

SSC Eights!

Points: 994
More actions
March 25, 2010 at 8:45 am

#220327

Hi All,
I need to change the clustered index on a a table that is involved in peer to peer replication.
for ex : The clustered index is on column A. I need to change it to column B
What is the best way to do it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

Someone correct me if I'm wrong, but I think you'll need to break your replication first, then drop and recreate the clustered index, then apply the snapshot and resync.

Converting oxygen into carbon dioxide, since 1955.

vky3 SSC Eights! Points: 994 More actions · Answer 2

Thanks for the response.

Can it be Done without the Resync?

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

Yes. You can apply the same changes across each subscriber by dropping and recreating the index on each. When you re-initialize the replications, skip the syncronization. Assuming no changes at the publisher.

Converting oxygen into carbon dioxide, since 1955.

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

The table on the subscriber can have any indexing strategy that is required for the intended use of the table on the subscriber. If you want a different clustering index - that is fine. The only logical restriction is that any constraints on the subscriber need to be compatible with the data that is coming from the publisher (e.g. a unique constraint on the subscriber but not on the publisher which could cause inserts and updates to fail).

To make a change, stop the distribution agent, change the definition of the table and assuming everything went OK and all the data remained, restart the distribution agent. There is no need to re-initalise etc.