Post reply

Bidirectional transactional replication

  • June 19, 2009 at 5:26 am

    #205762

    We plan on keeping two databases on separate servers in synch. The DBs will service identical web applications that also sit on each server.

    Internet traffic is shared between the two servers using a load balancer.

    The split between the two servers is hoping to.

    1. Provide redundancy in case of failure.

    2. Provide increased processing capacity when both servers are online.

    I'm wondering if using bidirectional transactional replication will be OK or will it eat into the processing capacity of each server effectively reducing the overall processing capacity of the system to that of a single server.

    My other option was to go with Mirroring and a hot standby.

    Both servers in the same data center.

    Any thoughts?

    Thanks

    Allen

  • June 19, 2009 at 5:43 am

    #1012287

    SQL Server 2005 comes with a 'better' alternative to bi-directional replication. Its peer to peer replication. You can have 2 or more servers participating in p2p replication with load balancing. Each of them will be active. You'll need to consider horizontal partitioning carefully here.

    I had downloaded a whitepaper on p2p replication from msdn site, cant find that url right now so attaching the document here!! It's good paper and tells u from scratch on how to setup p2p replication, its features and benefits.


    Pradeep Singh

  • June 22, 2009 at 6:04 am

    #1013240

    Thanks Pradeep. Also thanks for the pointer about partitioning!

    Regards

    Allen

  • June 22, 2009 at 7:11 am

    #1013265

    Glad I could help 🙂


    Pradeep Singh

  • June 22, 2009 at 8:13 am

    #1013306

    Be aware that the peer to peer feature is only available on Enterprise Edition of SQL server,

    I personally favour merge replication for this scenario as it has the ability to manage conflicts and depending on your application structure this could be a big issue.

    however merge replication does appear to use slightly more resource.

  • June 22, 2009 at 8:17 am

    #1013309

    Oh yeah. Thanks for mentioning that point Steve. It's available in enterprise edition only.

    I believe if the partitioning is done properly, conflicts can be avoided to a large extent.

    Also, Identity columns have to be used carefully and managed manually unlike transaction replication.

    EDIT - Typo error corrected.


    Pradeep Singh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply