Replication for Transactional Data as a Solution RRS feed

  • Question

  • Hi,
       I have seen situations where replication was used for replicating master/static data from central to various stores. I have a scenario wherein even Transactional data that is being updated at each store, updates not only the store but also the central and in turn the central pushes the updates back to other stores. One of the approaches being considered is using SQL Server Replication.

    I am of the opinion of doing this from the application instead at the database level.

    Some of the points which lead me to that are-

    1. On insert/update of every records, this replication would need to kick in to achieve a near about real-time scenario. I am not sure if SQL Server would be able to handle that. I potentially see the database crawling in no time. I could be wrong though.
    2. The other thing is knowing that its very difficult to scale a database layer adds on to the above problem.
    Would appreciate your viewpoints on the same.

    Friday, February 2, 2007 12:38 PM

All replies

  • Hi Sai,

    Using any form of data replication or synchronization to address a near real-time transactional scenario is going to put a tremendous amount of load on your master data store under almost any architecture.  If you are in need of near real-time transaction processing, have you considered implementing an SOA architecture using a combination of synchronous and asynchronous interfaces instead of using individual data stores and replication?


    Friday, February 2, 2007 11:15 PM
  • I have seen several prototype systems that used Service Broker for this type of operation.  Service broker provides full control over transaction scope so data gets shipped in near real time when the applications and network is available but also gets queued transactionally for times when the network is down or the application isn't available.  The advantage of using the database for messaging is that messages enjoy the full transactional, high availability features that you use to ensure the integrity of your data.
    Sunday, February 4, 2007 1:41 AM
  • IMO the solution for your problem relies quite heavily on the use of the system with respect to update\modifying of transactional data (orders) in the system and the implementation of the data model inside the database.

    From the sounds of the description you have central node and several remotes nodes in the system, the remote nodes are required to send\update transactional (order) data to the central node in near real time - max 24 hr period lets say.

    Now the first question is then can the transactional data be modified from more that one place, i.e. does it only get modified at the remote node where it was created or can it be modified at other remote nodes or at the central node?

    The second question is near is 'near real time’?

    The third question how is the transactional data structured in the database ? - is it using a number (integer) based identity column or something more unique like a GUID, if it is using a GUID based identifiers then this makes replication of data very much easier as errors due to identical identity columns values should not occur.


    Ollie Riches

    Monday, February 5, 2007 12:33 PM