Post reply

Altering tables in Peer-to-peer replication

  • September 4, 2010 at 2:25 am

    #239936

    Hi,

    We have a two node (say NODE1 and NODE2)peer-to-peer replication set up on our system SQLServer 2008.

    I would like to change the metadata of one of the tables. As this is a high-availability system, I would like to stop all the replication activities, make the changes on one node(NODE1), verify that the changes are working, make the same changes on the other node(NODE2), then re-enable replication. The ddl changes to the table are extensive enough that I have to clear them before executing. So I will handle re-populating the tables in a later step.

    I could do the above changes using ALTER statements,if it was adding new column(s) or datatype changes, without stopping the replication activities as I have set the replicate_ddl=True, but I want to drop the primary key from one column and add primary key back on another column in this scenario I can't do that using Alter statement as replication requires a table to have a primary key (mandatory)on atleast one column so I am using the following approach:

    Right now I'm just testing the process of stopping, doing ddl, then restarting.

    Following process using the SSMS interface (note that each step is conducted on both nodes):

    1.stop sync agents

    2.manually remove article: tblName1 with 'publications properties window'

    3.close 'publications properties window'

    4.make ddl changes to tblName1 (dropping primary key from one column and adding back primary key to another column)

    5.manually add article: tblName1 with 'publications properties window'

    6.re-enable agents

    But still I am getting the error:'Peer-To-Peer topologies require identical articles in publications at all nodes prior to synchronizing.'

    As I have made the same changes on both the nodes why am I getting this error ?

    Is there any other way of doing this please let me know.

    Please Please reply to this post as soon as possible.

    Thanks in advance.

  • September 4, 2010 at 2:29 am

    #1216511

    Please don't cross post.

    It is just a waste of resources if there are answers or additional information in one thread that would be helpful to those reading the other.

    Please respond here.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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