Replicating the schema while preserving existing data

Hello All,
Here is my situation:
I have a core database that is used to define the schema
of a client-owned database for use with some software. As
the software evolves and changes so too will the schema of
the core database. Ideally these changes should be
propogated down to the client-owned database;altering the
schema to match the new core version without destroying
the data that may already exists.
My attempts to use replication for this have failed when I
perform the following:
-The core database has a single table with three fields.
-The client database has nothing.
*I configure and activate replication
- Now the client database has an identical copy of the
table from the core database.
*I make changes to the core database schema by adding
three more fields to the table.
- Now the client database and core database have
differeing schemas.
*I add data to the client database table.
*I initiate replication from the core to the client
database expecting my three new fields to be appended to
the table without destroying my existing data.
-I don't get what I expect, instead I get a the original
three field table that was first replicated into the
client database and the data is missing.
How can I replicate a schema change from one database into
another without destroying any of the data that may
already exist on the target database?
Thanks,
Marc Deskins
marcdeskins@.hotmail.com
Marc,
you'll need to use sp_repladdcolumn, and after that there's no need to
reinitialize or do anything else, provided you haven't done a nosync
initialization. Just synchronizing will be enough.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick reply. I'll look into using
sp_repladdcolumn.
Marc Deskins
|||Paul,
Using sp_repladdcolumn was only a partial success. While
the newly added column is propogated down to the client
database from the core database the data that was added to
the client database before synchronizing is deleted.
What I am trying to do is have the schema changes to the
tables in the core database propogate down to the client
database without deleting data that was entered before
synchronization.
I understand this is sometimes unavoidable (dropped
columns etc.) so what I am concentrating on right now is
synchronizing the changes to the schema due to column
additions.
So if I have a table that looks like this in the client
database:
Field1|Field2|Field3
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
And I make a change in the core database to the table so
it now looks like this:
Field1|Field2|Field3|Field4
Then I a synchronize the client and core databases so the
tables have matching schemas.
I want my newly synchronized table in the client database
to look like this:
Field1|Field2|Field3|Field4
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
Not like this (which is what it does now):
Field1|Field2|Field3|Field4
Is there something I am missing or can this not be done
using SQL Server 2000 replication?
Thanks,
Marc Deskins

>--Original Message--
>Marc,
>you'll need to use sp_repladdcolumn, and after that
there's no need to
>reinitialize or do anything else, provided you haven't
done a nosync
>initialization. Just synchronizing will be enough.
>HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>.
>
|||Marc,
something is definitely wrong here. Can you post up the table schema
(Field1|Field2|Field3) and script out the publication and post it up. Also,
please confirm that you are on sp3. I'll try to reproduce, because what
you're seeing shouldn't happen.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)