Adding a subscriber which already has the schema and data

Hello,
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>

adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

Add new tables to replication

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

Rather than making a schema change to an existing table article in the merge publication, it seems like you are trying to add an incremental article to an existing table article. Also from what I see in the scenario, it seems like the subscriptions are set up to be NO SYNC (SP_addmergesubscription @.sync_type='none'). The NO SYNC means you are populating the subscriber server with the initial data manually via backup/restore.

In order for you to add a new table or two to the existing merge publication you may not have to break replication. Here are some steps to try:

1) Create the new tables at the publisher. These new tables must include a rowguid column (i.e. [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL) because merge will use this for change tracking. This scenario will not work without rowguid columns on the new tables.

2) Populate the table

3) Add the new table as a table article in the existing merge publication. You will be warned appropriately that the full snapshot needs to be rerun.

4) Run the full snapshot agent job.

5) BCP out the data from the new table article to a file and send it to the remote subscriber server

6) Create the new table schema at the subscriber

7) BCP in the data on the subscriber server

8) Execute the merge agent. This is required to have the merge triggers built on the subscriber for change tracking. *** Do not do any DML changes on the subscriber server until the merge agent is executed at least one time.

9) Now you should be able to execute DML on the new table at both the publisher and subscriber and sync the changes.

Try this out on a test system to verify it works. I tried on my system and it accomplished what you wanted.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

add new colums to existing table with Access

is it possible to add new columns to an existing MSDE table using
Access? If not what is the best practices modify a table schema without
loosing data?
Thanks for any suggestions.
Micheal,
You can register this msde server to another sql server's enterprise
manager. Or, you could use osql at the command prompt, then do an ALTER
TABLE and add the columns you need.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Micheal,
You can register this msde server to another sql server's enterprise
manager. Or, you could use osql at the command prompt, then do an ALTER
TABLE and add the columns you need.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.