First of all sorry about my english, and most importantly, my database ignorance, I'll do my best ;)

Until now, the scenario that I have is as follows:

  • In a server under our LAN, we have a database in a SQL Server 2008 R2 instance that is replicating as a publisher in a transactional replication with a DB (SQL Server 2008 R2 too) subscriber under a client LAN, both of them communicating through internet without using any VPN

Today the world turned black... at least for me, I'm on the developer side and this kind of requirements makes me spend a lot of time to get little progress: The client requested that we set up bidirectional transactional replication.

I know the client doesn't want a bidi-trans-replication exactly; what they really want is to interact with data that we have to see like they see ours via replication, and here it's where my doubts appears:

  • Is there other solution to this scenario than bidi-trans-replication?, I read something about mixed replications, but I don't know if this is better or worst :S

  • In case of bidi-trans-replication, does the client need to keep open ports in order to maintain the bidi-trans-replication? Currently we distribute the snapshot via FTP. I understand that we have to keep ports 21 and 1433 opened, but, does the client needs the 1433 port opened too?

  • 1
    Have you looked into merge replication? I think it's what you want. There's no bidirectional transactional replication, except the one with updateable subscriptions, but go for merge. More straightforwards. – Marcel N. Jul 10 '12 at 18:10
  • Transactional replication with updatable subscriptions is deprecated in favour of peer-to-peer. What do your clients want to do with the data? Opening up writes at multiple locations is a huge change. – Jon Seigel Jul 10 '12 at 19:56
  • I have to second Jon's warning. Going for master-master replication involves serious preparation and often requires application changes (identity range management to give an obvious example). – Remus Rusanu Jul 10 '12 at 20:08
  • (the coon)With bidirectional transactional replication I mean maintain the transactional replication that I already have, and set another transactional replication in Client -> We direction (having in mind @loopback_detection), but I still think there's a better solution... – mkloco Jul 10 '12 at 20:29

Your Answer

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

Browse other questions tagged or ask your own question.