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-
- 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.
- The other thing is knowing that its very difficult to scale a database layer adds on to the above problem.
-Sai
Replication for Transactional Data as a Solution
poodle
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.
HTH
Ollie Riches
allandippy
Mayero
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
Graham