Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wkm1925
Posting Yak Master
207 Posts |
 Posted - 2010-11-05 : 11:40:37
|
| Hi,Currently, I've 3 system. ERP, Warehouse Management ("WM"), and Transport Management ("TM"). All these 3 system using SQL Server 2005 Standard Edition (32 bit) on each ownWhat's have done so far as follow1. When ERP user push the data into WM and TM, the data place into special database named IntegratedDB.2. Then, one special program written in C# (running continuosly) will get the data from IntegratedDB, and push the data into WM DB and TM DB respectively every 5 minutesThis is what I think1. Current solutions architecture on integration, looks bad2. This is because, if there's no data is push from ERP into IntegratedDB, that special program still running. At this level, looks like a lot of resources used for nothing, epsecially memory.How to use SQL Server wisely to make it once user push the data in ERP, then this data insert into WM BD, and TM DB respectively. No need to store the data into temp DB first, then some program will do the insert job into another DB. I was thinking to using1. SQL Server trigger. 2. SQL Server Service BrokerLooks like Service Broker hard to implement compare than trigger. Did a Service Broker works like trigger? Do the transaction after insert, update, and delete. If I want to using Trigger, it's wrong?Really need an advice |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-05 : 12:28:00
|
| If you use a trigger what happens when it breaks? e.g. Trigger on a table on EPR database (I presume?) and the WM database is offline - what happens? User cannot update ERP? (that's a solution, but I expect not acceptable).With Service Broker my understanding is that, say, trigger could pass data to Service Broker and "some time later" Service Broker will pass the data to WM / TM - i.e. guaranteed delivery - its like a Queue.Alternatives are a batch process that queries ERP for new / changed records and freshens up WM / TM systems according. Records in ERP could have a status to indicate if they are on MW / TM, or not (THAT could be reset by a Trigger) and then just find records in ERP where status is "New / Modified", update them to "Copied" and transfer to WM / TM at the same time.If WM / TM must be updated in sync with ERP then your need some sort of 2-phase-commit.If WM / TM can run a little (or a lot) behind ERP the Service Broker, or a batch process, will probably be the most efficient. |
 |
|
|
wkm1925
Posting Yak Master
207 Posts |
Posted - 2010-11-05 : 23:10:21
|
| tq sir. your answer is my inspiration |
|
|
|
|
|
|
|
|
Need advice on database integration