Post reply

Altering Merge Replication Article

  • May 5, 2005 at 2:52 am

    #89139

    All,

    I have A Merge Publication, with 91 articles. The articles structures may change quite frequently.

    I would like to konw the following solution will work or not.

    1. back up the data for the atricle

    2. Drop all the Subscription for the atricle's publication

    3. Drop the atrticle from the publication

    4. alter / recreate the structures

    5. Restore the backedup data to the new / modified table

    6. Add the table as an article to the same publication.

    here i got some questions.

    1. will the schema chages will be propagaed to the subscribers automatically?

    2. what will happen to the existing data that the subscribers already have?

    3. Do i need to manually recreate the snapshot for the article?

    any help will be highly appreciated

    thanks

    Ramesh

  • May 9, 2005 at 4:52 am

    #557557

    All,

    Any suggestions on the above question?

    can this be done?

    thanks

    ramesh

  • May 9, 2005 at 7:58 am

    #557586

    I'm watching your topic with interest, and hoping for replies from the gurus as well.  One key piece of info that would help your cause would be knowing what version of SQL server you are running.  We (still) only have Version 7 here and I know that the replication functions have been improved immensely starting with 2000.

     


    Student of SQL and Golf, Master of Neither

  • May 17, 2005 at 12:42 am

    #559173

    All our SQL servers are SQL Server 2000 with sp3a

    can this be done?

    thanks

    ramesh

  • May 18, 2005 at 2:28 am

    #559504

    AFAIK, in merge replication, there is no possibility of dropping the subscription on a per article basis, unlike in transactional and snapshot replication.

    Almost all changes can be made in place using sp_repladdcolumn, sp_repldropcolumn. See this article for more info: http://www.replicationanswers.com/AddColumn.asp

    HTH,

    Paul Ibison, SQL MVP


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • May 18, 2005 at 2:40 am

    #559506

    Dear Paul Ibison,

    Thanks for your immediate reply.

    but if i need to use the sp_repldropcolum

    i have to drop all the constraints and the indexes for the particular column.

    is it adviceble to do this.

    actually i have tried to do this using SQLDMO in C#. im able to remove the check constranits and indexes that are associated to the column and we have methods to call the drop procedure thru the same sqldmo.table object.

    is it adviceble to take this route? or is ther any other alternative way to achive this

    if i use this mentod, will the schema changes can be sent to the subscribers during the next sync?

    and what will happen to the existing data that each subscribers have or the chnaged/ added records in the publisher from the previous sync?

     

    thanks

    ramesh

     

  • May 18, 2005 at 3:14 am

    #559511

    Ramesh,

    indexes and constraints can be altered using sp_addscriptexec, to be run before the sp_repldropcolumn.

    Rgds,

    Paul Ibison, SQL MVP


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • May 18, 2005 at 8:31 am

    #559595

    Ramesh,

    Following your scenario, here is my take:

    1.will the schema chages will be propagaed to the subscribers automatically?

    -- Yes, depending on your article properties. If you went by default, the existing table at the subscriber would be dropped (if the name of the article is the same) and the table with the modified structure would be created. You defintely would need to regenerate your snapshots again.

    2. what will happen to the existing data that the subscribers already have?

    -- The existing data will be wiped out as the table is dropped.

    3. Do i need to manually recreate the snapshot for the article?

    -- Well, you would have to regenerate your entire snapshot.

    If you have minor modifications to columns, I would use sp_repladdcolumn or sp_repldropcolumn.

    Hope that helps.

    Vas Lakshman

Viewing 8 posts - 1 through 7 (of 7 total)

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