Merge replication schema changes

I want to implement merge replication between SQL Server 2005 and SQL Server
2005 express. My problem is, when we make table schema changes like adding
and removing columns, we drop and re-create the table. Is there any way that
this methodology can work with merge replication. I know the replication
trigger only track ATLER TABLE statements.
Thanks
Mark,
I'm not too sure what is happeng here. Are you saying that the ALTER TABLE
statement causes reinitialization? Or is a new snapshot being created? Or do
you manually drop the table, change the columns and then add the table
again? Just trying to get the gist of the issue
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
We manually drop the table, change the columns and then add the table again.
The documentation I've read says that merge replication only tracks ATLER
TABLE statements. (Which we don't use).
Thanks for the quick response.
"Paul Ibison" wrote:

> Mark,
> I'm not too sure what is happeng here. Are you saying that the ALTER TABLE
> statement causes reinitialization? Or is a new snapshot being created? Or do
> you manually drop the table, change the columns and then add the table
> again? Just trying to get the gist of the issue
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Mark - not sure I follow - how do you make the changes to the table's
schema?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
When we need to change a tables schema, we copy all the data out into a new
table with the new table structure. Then we delete the old table and rename
the new table to be the old table. If we want to add a new column to a table
we do not issue and ALTER TABLE add <column_name>... statement. We create a
new table with the new column in it and then copy the data over from the old
table. Update the new column with appropriate values. DROP the old table and
RENAME the new table. How can we get these schema changes propegated to the
subscribers.
Can we remove the table first from the publication. Drop and CREATE the
table and then add it into the publication again? What about pending
subscriber changes. Would they be lost in that case?
Thanks again.
"Paul Ibison" wrote:

> Mark - not sure I follow - how do you make the changes to the table's
> schema?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||I would advise to use the inbuilt merge replication's support to ALTER
TABLES and propagate schema changes.
However I can also understand that it may be difficult to change the app. In
that case, yes you could drop the article and readd it after you change the
schema. The subscriber's unsent data will cause a problem in this case.
You may think that setting a pre_creation_cmd of 'none' to save the
subscriber's unsent changes. But remember that the schema is now different
between publisher and subscriber.
One thing you can do is to save off subscriber's unsent changes into a temp
table and then reinsert these rows after re-synchronizing the (new schema)
table.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Mark (WHSCC)" <MarkWHSCC@.discussions.microsoft.com> wrote in message
news:63DB1088-15DE-4A3C-8FEE-A91C79D4AC9E@.microsoft.com...[vbcol=seagreen]
> Hi Paul,
> When we need to change a tables schema, we copy all the data out into a
> new
> table with the new table structure. Then we delete the old table and
> rename
> the new table to be the old table. If we want to add a new column to a
> table
> we do not issue and ALTER TABLE add <column_name>... statement. We create
> a
> new table with the new column in it and then copy the data over from the
> old
> table. Update the new column with appropriate values. DROP the old table
> and
> RENAME the new table. How can we get these schema changes propegated to
> the
> subscribers.
> Can we remove the table first from the publication. Drop and CREATE the
> table and then add it into the publication again? What about pending
> subscriber changes. Would they be lost in that case?
> Thanks again.
> "Paul Ibison" wrote: