SQL Server Performance

Database Integration

Discussion in 'SQL Server 2005 General Developer Questions' started by wkm1925, Dec 18, 2010.

  1. wkm1925 New Member

    Hi,
    Currently, my database logical view as follow
    1. ERP database server on 192.168.1.2
    2. WMS database server on 192.168.1.3
    3. TMS database server on 192.168.1.4
    How ERP, WMS and TMS communicate each other?
    1. ERP will insert the data into IntegratedDB on ERP database server
    2. Then, WMS database server and TMS database server will do the pickup from IntegratedDB and inserting/updating IntegratedDB respectively
    3. Finally, ERP will query back from IntegratedDB and updating ERP database respectively
    *IntegratedDB on 192.168.1.2
    *Looks like IntegratedDB is a middle database
    *There's no acknowledgement will pass back to the sender if the data has been processed successfully

    FYI, there's no integration tools such as MSMQ, SSIS, SQL Service Broker, AS2 and so on was used.
    Recently, this was happen
    1. The data appear in ERP Database
    2. The data appear in IntegratedDB
    3. The data appear in WMS Database
    4. But, the data not appear in TMS Database
    *At this level, looks like the data was imbalance
    *At this level also, there's no guaranteed delivery

    Once the problem was rectified, looks like TMS Integration Component was corrupted. I saw, this database integration architecture have no rollback capability if unexpected problem happen
    My humble opinion, if any of the database partner in this database integration architecture have a problem, all the database should fallback as a usual.
    My question as follow
    1. Did this database integration architecture is acceptable?
    I hope someone can give the comment.
  2. satya Moderator

    In the recent times I worked on the similar design & implementaiton for a retailer, so I guess we can take this offine if you wish. If so get in touch with me (look at my signature strip for more information)_

Share This Page