none
What is the best way to drop and recreate a Primary Key in the Replication Table?

    Question

  • I have a requirement to drop and recreate a primary key in a table which is part of Transaction replication. What is the best way to fo it other than remove it from replication and add again?

    Thanks


    Swapna

    Thursday, July 03, 2014 9:42 PM

Answers

  • Hi Swapna,

    Unfortunately you cannot drop columns used in a primary key from articles in transactional replication.  This is covered in Make Schema Changes on Publication Databases:

    You cannot drop columns used in a primary key from articles in transactional publications, because they are used by replication.

    You will need to drop the article from the publication, drop and recreate the primary key, and add the article back into the publication.

    To avoid having to send a snapshot down to the subscriber(s), you could specify the option 'replication support only' for the subscription.  This would require the primary key be modified at the subscriber as well prior to adding the article back in and should be done during a maintenance window when no activity is occurring on the published tables.

    I suggest testing this out in your test environment first, prior to deploying to production.


    Brandon Williams (blog | linkedin)

    Thursday, July 03, 2014 10:50 PM
    Moderator

All replies

  • Hi Swapna,

    Unfortunately you cannot drop columns used in a primary key from articles in transactional replication.  This is covered in Make Schema Changes on Publication Databases:

    You cannot drop columns used in a primary key from articles in transactional publications, because they are used by replication.

    You will need to drop the article from the publication, drop and recreate the primary key, and add the article back into the publication.

    To avoid having to send a snapshot down to the subscriber(s), you could specify the option 'replication support only' for the subscription.  This would require the primary key be modified at the subscriber as well prior to adding the article back in and should be done during a maintenance window when no activity is occurring on the published tables.

    I suggest testing this out in your test environment first, prior to deploying to production.


    Brandon Williams (blog | linkedin)

    Thursday, July 03, 2014 10:50 PM
    Moderator
  • Thank you Sofia.

    I removed the articles from Replication, drop and recreate the constraints and  added them again.

    Thanks.


    Swapna

    Monday, July 07, 2014 4:51 PM