I have a problem with replication between 3 servers. I made something like this: server A replicate (transactional replication) to server B (to 'mydb' database), then server B replicate 'mydb' (using transactional replication) to server C. On the beginning it looks and works fine, but something wrong is going on (about 2-3 month later) and replication break up. SQL say that hi can replicate db because db is allready use to replicate. Any one had similar broblem? Mayby someone knows hot can I make alternative configuration to have similar funcionality?
1 Answer
I would recommend making both Subscribers (B and C) replicate directly from the original Publisher (A). This is a common scenario and should work correctly.
They only way this wouldn't work is if the replicated data is being modified on server B before replicating to C.
Edit: Not sure if replication is best solution for disaster recovery scenarios like you describe. I would consider using log shipping instead (transaction log backups taken on server A throughout the day are restored to servers B and C). I'd have to look up the details, but I would think that if you failed over to B, you could continue to do log shipping from B to C in case of a second failure.
For even higher-availibility, you would probably have to consider clustering.
-
OK. I understand. unfortunately my scenario is not a easy to replace. On server A I have system with db that should be usable anytime on server B (its my stand-by system). server C is cold reserve against B server failure. Mayby U can propose some alternative solution?– hubertusMar 17, 2010 at 22:43