x

Merge replication across disconnected networks

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!
more ▼

asked Oct 27 '10 at 01:02 AM in Default

patrick2525 gravatar image

patrick2525
11 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

  1. Setup NetAPub - > NetASub merge replication
  2. Setup NetBPub - > NetBSub merge replication
  3. Backup NetAPub -> Restore on NetB as NetAPubDB
  4. Copy data from NetAPubDB into NetBPubDB (using T-SQL or SSIS or.....)
  5. 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?
more ▼

answered Oct 27 '10 at 01:21 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 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)
10|1200 characters needed characters left

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:

  1. on my development server, set up merge replication, with NetB_Pub as Publisher, NetA_Pub as subscriber
  2. backup NetA_Pub, restore (KEEP_REPLICATION) to NetA
  3. backup NetB_Pub, distribution, msdb, restore (KEEP_REPLICATION) to NetB
  4. set up merge replication on NetA with NetA_Pub as (re)Publisher, NetA_Sub as subscriber
  5. set up merge replication on NetB with NetB_Sub subscribing to existing publication on NetB_Pub
  6. 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 ;)
more ▼

answered Oct 27 '10 at 02:30 PM

patrick2525 gravatar image

patrick2525
11 1 1 2

You could use simple upsert logic to get the delta moved from A to B. Tablediff is not the tool to use here IMO, merge replication isn't either.

Something like this would do the inserts of missing data:

INSERT INTO NetB_Pub.dbo.TableA
SELECT src.*
FROM NetA_Pub.dbo.TableA src
LEFT JOIN NetB_Pub.dbo.TableA trg ON trg.KeyCol = src.KeyCol
WHERE trg.KeyCol IS NULL
If you were on SQL Server 2008, you could use the MERGE command to do the insert, update and delete in one step.
Oct 27 '10 at 02:41 PM WilliamD

Yes, that would work for the inserts, but not the updates. I tried writing a procedural MERGE type functionality for SS2005, but again it was clunky and I had to compare every column - that's why I then opted for the TABLEDIFF (but still didn't get the job done correctly).

Thanks for your input!
Oct 27 '10 at 10:13 PM patrick2525

You can do the updates in a similar way:

UPDATE trg
SET Col2 = src.Col2
FROM NetA_Pub.dbo.TableA src
INNER JOIN NetB_Pub.dbo.TableA trg ON trg.KeyCol = src.KeyCol
You can list all the columns and let the update run. IIRC, updates that don't actually change anything don't really happen, SQL Server is clevere enough to know that the value hasn't changed and ignores the update - I could be wrong though.
Oct 27 '10 at 11:56 PM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x293
x22

asked: Oct 27 '10 at 01:02 AM

Seen: 1910 times

Last Updated: Oct 27 '10 at 06:47 AM