Schema Changes - Merge Replication

Question

Schema Changes - Merge Replication

govind-211283

SSC Eights!

Points: 962
More actions
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

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

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

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

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

govind-211283 SSC Eights! Points: 962 More actions · Answer 2

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

Dylan Peters SSC Eights! Points: 916 More actions · Answer 3

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

Darius T. SSC Enthusiast Points: 147 More actions · Answer 4

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

govind-211283 SSC Eights! Points: 962 More actions · Answer 5

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