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?
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 ;)