none
bidirectional transactional replication: scaling and behaviour on disconnect

    Question

  • Hi,

    how many servers can participate as subscribers to the central publisher in a bidirectional transactional replication topology? If there is a limit, what are the reasons for this limit?

    What happens if the connection between one of the subscribers and the central publisher fails? Will the replication between the two nodes continue as soon as the connection is reestablished or does it need manual intervention to continue replication?

    Thursday, May 02, 2013 7:49 AM

Answers

All replies

  • What version of SQL are you using?

    From my understanding you may struggle to have more than two subscribers with bi directional replication.

    If you are concerned about resilience you may be better using peer to peer replication (depending on your SQL version). Using this model means another server could take over should your primary database fail (as all subscriber data in validated and in sync). Bi directional replication is simply a dumb send with no validation and a single point of failure as it uses the hub spoke topology.

    This may help - http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/ca87a161-4270-454b-a1ae-078d68ba45a0

    Friday, May 03, 2013 10:24 AM
  • That depends on what you mean by bi-directional transactional replication. If you do not mean updateable subscribers or p2p, then the practical limit is 2, but you can do it with 3. Beyond 3 there is too much replication activity.

    I would think long and hard about doing it beyond 2.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, May 03, 2013 11:29 AM
  • I am using SQL Server 2008 R2 Standard Edition. In my case I don't want to use updateable subscribers, as it is marked as deprecated by Microsoft. I can't use p2p, because that is only supported by the enterprise edition and i need filtering to just send certain data to the subscribers. As conflict resolution is not important, but performance is, I don't want to use merge replication, because of the overhead the triggers add. But I have to support about 5 subscribers, maybe more later.

    How does doing it beyond 2 affect the replication? What sort of problems would occur in my scenario using more than two subscribers?

    Friday, May 03, 2013 12:51 PM
  • If you update node 1 it will replicate to node 2. This update will not go back to node 1 as you set the loopback_detection to true. But it will go to node 3. From node 3 it will not go back to node 2 as you set the loopback_detection to true, but it will go back to node 1.

    So you will find that you will get multiple writes applied multiple times for each write. This is what makes it not scalable. I have done it with 3 nodes but there is a lot of replication chatter.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Gunnar.Wurl Monday, May 06, 2013 7:26 AM
    Friday, May 03, 2013 3:49 PM
  • Thank you. I think i should consider using the merge replication then and see how i can improve the performance there.
    Monday, May 06, 2013 7:26 AM