Merge replication across disconnected networks

Question

I have 2 separate networks:
NetA
NetB

Each network has 2 Sql Server 2005 databases:
NetA_Pub
NetA_Sub
NetB_Pub
NetB_Sub

Setting up merge replication between databases on the same network is no problem, but I need to replicate between the NetA_Pub and NetB_Pub (does not matter which one is Publishes and which one rePublishes), and the gotcha is that I can move data from NetA to NetB, but not visa-versa. Therefore, NetB_Pub will have data not present in NetA_Pub (not a problem).

I have tried doing routine backups of NetA_Pub and restoring to NetB (the servernames for both NetA_Pub and NetB_Pub are the same, so the KEEP_REPLICATION option for restore works), however after about a week this stopped working without any error messages to point me to the cause. I suspect the snapshot expired on NetB_Pub, and when the restored NetA_Pub tried to sync it wanted to reinitialize, but couldn't because NetA_Pub is also a publisher. (just a guess)

Any suggestions on what the problem is, or a better way to accomplish this?

Thanks!

Answer 1

0

If you need a complete copy of NetA_Pub sent to NetB, a backup/restore seeems the best way to go. What I don't understand from your question is the KEEP_REPLICATION section. You are restoring NetA_Pub as a separate database and then copying that data into NetB_Pub, right? In that case, you don't care about the replication settings any more, as you only need the data.

I also don't understand the reason for merge replication in this scenario, or are the subscribers making updates that should go to the publishers?

I suggest you try the following:

Setup NetAPub - > NetASub merge replication
Setup NetBPub - > NetBSub merge replication
Backup NetAPub -> Restore on NetB as NetAPubDB
Copy data from NetAPubDB into NetBPubDB (using T-SQL or SSIS or.....)
Drop NetAPubDB on NetB

You then run steps 3,4 and 5 at the necessary intervals.

This would keep both sides as merge replications in their own domains, but allow you to copy the information you need from NetA to NetB.

Does that help, or am I misunderstanding you?

answered Oct 27 '10 at 01:21 AM

WilliamD
25.3k ● 16 ● 18 ● 41

@WilliamD Congrats on your own 5K! My upvote made it just now :)

Oct 27 '10 at 07:23 AM Oleg

@Oleg - many thanks.

Strange thing is; I hit 5k about 2 hours ago, then all of a sudden I was back down on 4995 (someone must have taken a thumbs up back?)

Oct 27 '10 at 08:14 AM WilliamD

@WilliamD Yea, I see, it does look strange. Anuj, Grant and Greg voted you up about 3 hours ago and even congratulated you on reaching the mark. The total number of upvotes on that question is 3, so it matches the activity. I guess someone undid the upvote on some other question, maybe by accident, who knows.

Oct 27 '10 at 09:07 AM Oleg

(comments are locked)

Answer 2

Hi WilliamD,

Thanks - yes, you got the gist for the most part. I restore NetA_Pub onto NetB, keeping the db name of NetA_Pub. So on one server on NetB, I have a SS2005 instance with 2 databases:

NetA_Pub
NetB_Pub

However, where your Step4 - copy the data from NetA_Pub to NetB_Pub, that's where I have set up merge replication as well. The method may have been kind of convoluted:

on my development server, set up merge replication, with NetB_Pub as Publisher, NetA_Pub as subscriber
backup NetA_Pub, restore (KEEP_REPLICATION) to NetA
backup NetB_Pub, distribution, msdb, restore (KEEP_REPLICATION) to NetB
set up merge replication on NetA with NetA_Pub as (re)Publisher, NetA_Sub as subscriber
set up merge replication on NetB with NetB_Sub subscribing to existing publication on NetB_Pub
daily backup NetA_Pub, restore (KEEP_REPLICATION) to NetB as NetA_Pub; replication syncs NetA_Pub to NetB_Pub ( worked for about a week)

I've experimented with instead of 'replicating' between the restored NetA_Pub and NetB_Pub, by creating a procedure that invokes tablediff on all the tables, but that's kind of clunky and doesn't behave exactly how'd I'd like. For example if a record is deleted in NetA_Pub, then I'd like it deleted in NetB_Pub as well. However, if a record is created in NetB_Pub, then leave it. However, tablediff will want to delete the record in NetB_Pub in both cases since the record is not in NetA_Pub when the procedure is run.

When coming up with the solution, I thought that just using the same merge replication across all the db's would be the easiest to setup and maintain, but now having second/ third thoughts. I'm not familiar with SSIS, is that something that could be a solution?

Sorry if this is hard to follow ;)

Merge replication across disconnected networks

Follow this question

Related Questions