Post reply

Schema Changes - Merge Replication

  • August 15, 2005 at 5:31 am

    #102707

    Hi All,

    How can I alter some field in merged-replicated table without using drop/add trick? + I don't want to reinitialize the subscription. Any trick from Masters?

    Thanks,

    --Govind

  • August 15, 2005 at 7:03 am

    #582116

    Govind,

    I have encountered this issue many times and unfortunately, there is no easy solution.  The options availabe are limited:

    1.  Use sp_repladdcolumn and sp_repldropcolumn going through the tedious drop/add trick.  An important note here is that this process will change the order of columns, possible affecting any queries that improperly use select * and rely on an expected column order.

    2.  Drop subscription, remove article, alter column, add article, add new subscription.

    I have searched in vain for easier solutions to making schema changes to replicated databases and have not had any luck.

     

    Gordon

    Gordon Pollokoff

    "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

  • August 15, 2005 at 11:09 pm

    #582340

    Hi Gordon,

    This can be a big missing element in Sql2k Server and I don't want that drop/add trick even-though I've not used * anywhere. What about Sql2k5 should we hope the availability of this feature in that?

    Thanks,

    --Govind

  • August 16, 2005 at 10:26 am

    #582552

    I've used the drop and add with sp_repladdcolumn and sp_repldropcolumn before without any problems.  Last night I had to change a column from varchar(5) to varchar(50) (grumbling at the developers while waiting for 105,000 rows to update and replicate).  Column order shouldn't be a problem.

    From what I've heard, sql2k5 will have support for replication built into DDL, so you can just use Alter table add column...  Though this wouldn't satisfy the column order question.

     

    Dylan Peters
    SQL Server DBA

  • August 18, 2005 at 1:59 pm

    #583299

    Hi,
     
    Be carefull.
    There is a bug in MSSQL2000. 
    If columns are dropped and added through sp_repldropcolumn and sp_repladdcolumn on existing merge replication, You will get non-convergence on new subscriber(s) if you add them after such a change.
    This is if you use column level conflict tracking (default in this version of SQL).
     
    I had a case about this with MS. It's confirmed.
     
    Darius
     

  • August 18, 2005 at 11:18 pm

    #583399

    Hi,

    Even I'm very happy using drop/add trick. But changing the ordinal position doesn't look good when you see your table like your main field is lying at the bottom/most RHS. Anyways.

    Thanks for spending time on my question.

    Govind

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

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