asked
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!
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?
@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.
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 ;)
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.
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!
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.
