Post reply

Change the clustered Index on a replicated table

  • 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?

  • March 25, 2010 at 8:53 am

    #1139689

    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.

  • March 25, 2010 at 9:10 am

    #1139715

    Thanks for the response.

    Can it be Done without the Resync?

  • March 25, 2010 at 10:32 am

    #1139793

    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.

  • March 25, 2010 at 9:38 pm

    #1140118

    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.

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

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