locked
MSSQL Transactional replication for HA RRS feed

  • Question

  • Hi All,

    I am quite new to MSSQL administration. I am trying to setup HA for my ticket tool (Manage Engine Servicedesk Plus MSP) which allows to use MSSQL. I dont know if this is the right purpose to use MSSQL transaction replication or not. I am performing as below.

    I have 2 machines and Servicedesk installed on both the machines with MSSQL 2008 R2 standard on both of it. Now, I am publishing the database from one machine using "transactional replication with updated subscriptions" and subscribing from the other machine using "Push" feature. Everything works fine and I can see all the tables replicated from Publisher to subscriber. I am able to create new data on the Primary using the application and can see the same on the secondary. However, when I try to create data on the secondary, the application throws an error. Although i can manually create tables on the secondary server and sometimes it crashes. When I remove the subscription, it works fine.

    My question is, are there any part of the database not allowed to perform write operations while in replication or is anything else required to get this working bidirectional. Or is any other method of replication more relevant in my case. Please suggest.

    Thanks,

    Aliasgar

    Monday, October 2, 2017 10:01 AM

Answers

  • My question is, are there any part of the database not allowed to perform write operations

    Hello,

    Yes, because Transaction Replication is one way, therefore the subscriber is by default "read-only", see Transactional Replication :"By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher"

    Merge Replication replicate data changes in both ways, but it requires table design changes, like an additional RowGUID column.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 2, 2017 10:29 AM

All replies

  • My question is, are there any part of the database not allowed to perform write operations

    Hello,

    Yes, because Transaction Replication is one way, therefore the subscriber is by default "read-only", see Transactional Replication :"By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher"

    Merge Replication replicate data changes in both ways, but it requires table design changes, like an additional RowGUID column.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 2, 2017 10:29 AM
  • Transactional replication is not a tool for HA.  You would need to use Availability Groups or database mirroring to accomplish true HA.

    Please see:

    https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/high-availability-solutions-sql-server

    Here is a list of the limitations of replication:

    https://technet.microsoft.com/en-us/library/ms171864(v=sql.105).aspx

    Monday, October 2, 2017 11:52 AM
  • As you are using transactional replication with updateable subscriptions data movement will be both ways.

    What exactly is the error message you are getting?

    I would use bi-directional transactional replication over transactional replication with updateable subscriptions as updateable subscriptions is tuned for use cases where the majority of your transactions originate at your publisher.

    If you are using the enterprise edition you might also want to look at peer to peer replication.

    Monday, October 2, 2017 1:19 PM
    Answerer
  • Hi All,

    I setup merge replication, which works like a charm!.

    Thanks everyone for the suggestions.

    Thursday, November 30, 2017 8:41 AM
  • Merge replication may work ok for low workloads. For heavy workloads you will struggle. Also you can't be offline for significant time periods, ie you need to keep the publisher and the subscriber replicating frequently otherwise you will run into problems - especially with identity ranges being exhausted.
    Thursday, November 30, 2017 2:45 PM
    Answerer