sql server replication: Merge Replication after data moved to a new server. -- sql server replication

Re: Merge Replication after data moved to a new server.

Conrad Gotzmann
3/24/2006 5:00:02 AM

Envoriment is the following.

Server A publication database
Server B Remote Server
Server C Remote Server

Server A using push subscription to server B & C.

Server D new server.

Process:
Allow agents to sync with Server B & C so all databases are in sync.
Backup Server A Publication Database.
Disable Server A subscriptions. (disable server subscription agents)
Restore Server A Publication Database to Server D ( Different Name )
Create new publication for Server D
Create new push subscriptions to Server B & C
All subscriptions are merge subscriptions only.

When data is changed on server B & C some data was merged ( as new data was
modified or added ) and some create replication errors where the agents
believe that the data was changed outside of replication. ( I beleive the
metadata on Server B & C did not believe the system was in sync and was
trying to update the new publication server while it believes the data was
changed on server A publication and not applied yet )

When I restored the publication database to Server D ( Differenet Server
Name) the metadata is cleared by design ( This was done to make sure metadata
was clean. I assumed because the data was in sync that the remote server B &
C also believed the data is in sync ).

Is there a way to clear all metadata of the remote servers replication
history ?

I was hoping that this process would re-create the initial install of the
databases which worked great for a long time.

[quoted text, click to view]

Re: Merge Replication after data moved to a new server.

Conrad Gotzmann
3/24/2006 5:40:04 AM

Server D is a new faster server replacing Server A
Server A will then become the test server for future application testing in
a replicated env. This is why Server D is using a different Name. Server A
will remain as is but witha role in the test env.

So the production setup is

Server D Publication Server using push subcription to Server B & Server C
Where the data changes are on Server B & C. There are no data changes on
Server D. and backup of the data is on Server D ( Central data center )

[quoted text, click to view]

Re: Merge Replication after data moved to a new server.

Paul Ibison
3/25/2006 8:59:10 PM

Conrad,
whenm doing this, I'd synchronize the A publisher with B and C. Restore A on
D then remove the replication setup (after scripting it all out). Then I'd
set up D as the publisher and do a nosync nitialization to B and C. AFAIK,
if you try to move replication in situ to differently named servers this is
not supported as the servername is hardcoded into too many meta data tables.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Re: Merge Replication after data moved to a new server.

Conrad Gotzmann
3/26/2006 2:52:40 PM

The step of

do a nosync intialization to B and C. not sure what you mean by this step.?

I setup a subscription where the the data and schema are not used is this
what you mean.

[quoted text, click to view]

Re: Merge Replication after data moved to a new server.

Paul Ibison
3/27/2006 12:00:00 AM

I think we're talking about the same thing. It's the option in the wizard
where you select to Not initialize the data because the subscriber already
has it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.