Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time.

But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication?

Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server?

UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication?

share|improve this question

1 Answer 1

UPDATE: It seems it is with use of merge replication and republishing, what about transactional replication?

The near to same functionality can be achieved using Peer-to-Peer Transactional Replication. This is the only SQL Server replication technology that allows simultaneous writes at multiple nodes.

Peer-to-peer replication is a logical extension to bi-directional transactional replication handling the data layer only. The replication engine is used to replicate like it says "from everyone - to - everyone".

More info can be found at Publication Types for Transactional Replication. Here is a good example of configuring it.

Refer this white paper for a comparison of SQL Server and Oracle features.

share|improve this answer
    
Thanks, but I am aware of Peer-to-Peer replication. One of possible requirements for my case might be good utilization of slow link between sequentially connected databases. Peer-to-Peer replication is not a good choice for such a case. –  sknaumov May 28 '13 at 15:22
    
@sknaumov your question was -- what about transactional replication? which I have given my answer as Peer-to-peer replication is nothing but bi-directional T-Rep. You did not mention anything about knowing/analyzing Peer-to-peer replication in your question ! –  Kin May 28 '13 at 15:28
    
Actually it is interesting what topologies are possible with merge/transactional replication using publishing subscribers. What about conflicts or cycling changes in this case? There is almost no information in MSDN about limitations of publishing subscriber technique and replication techniques described not as a building blocks, but as a complete solution with fixed replication topology. –  sknaumov May 29 '13 at 4:27
    
By the way, is it possible to combine peer-to-peer replication with other types of transactional replication or with merge replication? If yes, then what are limitations of such combination? –  sknaumov May 31 '13 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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