Post reply

convert clustered index to nonclustered, on subsciber table

  • June 14, 2012 at 4:31 am

    #258069

    There is a table with 2 column clustered primary key index and 1 column non-clustered index:

    create table Compdata(LocId int, BranchId int, InsDate datetime

    CONSTRAINT [PK_LocBranch] PRIMARY KEY CLUSTERED

    )

    CREATE NONCLUSTERED INDEX [IX_InsDate] on CompData

    ( InsDate ASC )

    This table resides on replication (transactional) Subscriber.

    Now, on Subscriber only, I want to convert:

    clustered [PK_LocBranch] -> non-clustered [PK_LocBranch],

    non-clustered [IX_InsDate] -> clustered [IX_InsDate]

    Replication requires that tables must have PK, so we can't just drop the PK and re-create it, right ?

  • June 14, 2012 at 5:26 am

    #1500911

    Why you want to keep different schema.?

    Replication strong requires same PK definition

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • June 14, 2012 at 5:55 am

    #1500919

    I'm not changing the schema.

    Only changing index type between clustered and non-clustered

  • June 14, 2012 at 6:18 am

    #1500929

    You'll need to suspend replication whilst you do it, but as long as you don't change the PK definition (i.e. column list) you should be OK.

  • June 14, 2012 at 6:50 am

    #1500961

    How do I suspend (Pause) replication onto Server B, and Resume it afterwards, safely ?

    Specs:

    Server A (2005, OLTP, Publisher + Distributer) . Company DB (250 GB)

    --transactional,push-->

    Server B (2005, Reporting, 200 tables, one of which is CompData 40 million rows) , Server C (2005, Reporting, 100 tables)

    Want to either:

    - Pause replication for only CompData table. Other tables must be synced as usual

    - Pause replication as a whole at Server B only (Server C must not be affected). I guess transactions keep collecting at Distributor, and reapplied after Resuming replication.

    - (Worst case) Reinitialize snapshot for CompData table only

    Don't want to:

    - Reinitialize snapshot of entire DB all tables.

    - Delete & recreate the subscription

    Some forums advice stopping Log Reader / Distribution agent etc., but there are so many jobs, not sure which one? also is it absolutely safe (no data loss during sync)?

    I couldn't find clear step-by-step instructions for this.

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

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