I have database D1 as a publisher, D2 as a subscriber and they are using Distributor 'DIST' which is reside on the subscriber side. Both D1 and D2 database having DR databases called DR1 and DR2. I have setup the Tran. Replication from D1 to D2, D1 to DR1 and D2 to DR2 as shown in below diagram: enter image description here

When my production incoming traffic is on to database D1 and data is replicating to databases D2, DR1, and DR2. so obviously there might be some delay in data transferred to DR1, and DR2. so lets say a disaster happen and my D1, Dist, and D2 database server got crashed, in that case i have to divert my traffic to DR1 and i have to setup Tran. replication between DR1 to DR2 (with Nosync option, as i don't want to reinitialize it) but because of delay in data transfer there might be data mismatch between DR1 and DR2.

is there any setting on distributor that can give me the surety that the data transfer to DR1 and DR2 be in same transaction. or is there any alternative ways to handle this problem ? Note: i am using SQL SERVER 2008, Transactional Replication. I can not afford to re-initialize the replication between DR1 and DR2.


Why don't you setup your environment to replicate from DR1 to DR2 instead of from D2 to DR2. That way you don't have to re-initialize or -synch anything on failover. You just need to make sure that you fail over both D1 to DR1 and D2 to DR2 together.

If that is not an option, you could also look into clustering. if D1 and DR1 build one cluster C1 and D2 & DR2 make up cluster C2 you can setup replication from C1 to C2. In that setup a failover of either site won't affect replication.

  • thanks for the reply. so when we setup the replication between C1 and C2 they don't use D1 Server name instead they uses cluster name ? i know after disaster if i can rename D2 and DR2 servers as D1 and DR1 my replication will work. so when we setup the replication on cluster, it uses the cluster settings not the DB server settings ? – Piyush Patel Apr 12 '13 at 5:40
  • That is correct, you can replicate from the cluster directly without identifying the nodes to replication – Sebastian Meine Apr 12 '13 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.