November 8, 2010 at 9:11 am
Hi,
I've as follow
1. ERP DB
2. Warehouse Management DB
3. Transport Management DB
*All database using SQL Server 2005 Standard Edition (32 bit)
All of this DB Server installed on their dedicated machine, and data in SAN within LAN.
How ERP DB push/pull data to/from Warehouse Management DB and instead?
Answer:
1. ERP will generate XML files and place into folder. Then, one special program (running continuosly using Task Schedule) will pickup this XML Files, process and insert/update/delete into Warehouse Management DB.
2. Warehouse Management DB will generate XML files and place into folder. Then, one special program will pickup this XML Files, process and insert/update/delete into ERP DB.
How ERP DB push/pull data to/from Transport DB and instead?
Answer:
Same technique as above
Based on my current Database Integration Architecture, I hope someone can comment. Right or wrong.
My most concern is, how if one or more my DB Server is offline?
November 8, 2010 at 11:04 am
I normally do ETL through SSIS, not through an external XML file, but it sounds like it should work, if I'm understanding you correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 6:15 pm
GSquared (11/8/2010)
I normally do ETL through SSIS, not through an external XML file, but it sounds like it should work, if I'm understanding you correctly.
Sir,
How about data integrity? Atomicity, 2-phase-commit, and so on
November 9, 2010 at 5:25 am
Little Nick (11/8/2010)
GSquared (11/8/2010)
I normally do ETL through SSIS, not through an external XML file, but it sounds like it should work, if I'm understanding you correctly.How about data integrity? Atomicity, 2-phase-commit, and so on
:blink: Meaning?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 9, 2010 at 6:32 am
Sir,
My question on my current solutions.
***************************************************
I've as follow
1. ERP DB
2. Warehouse Management DB
3. Transport Management DB
*All database using SQL Server 2005 Standard Edition (32 bit)
All of this DB Server installed on their dedicated machine, and data in SAN within LAN.
How ERP DB push/pull data to/from Warehouse Management DB and instead?
Answer:
1. ERP will generate XML files and place into folder. Then, one special program (running continuosly using Task Schedule) will pickup this XML Files, process and insert/update/delete into Warehouse Management DB.
2. Warehouse Management DB will generate XML files and place into folder. Then, one special program will pickup this XML Files, process and insert/update/delete into ERP DB.
How ERP DB push/pull data to/from Transport DB and instead?
Answer:
Same technique as above
Based on my current Database Integration Architecture, I hope someone can comment. Right or wrong.
My most concern is, how if one or more my DB Server is offline?
***************************************************
Did my current solutions guarantee the data integrity? Like Atomicity, 2-phase-commit, and so on
November 9, 2010 at 6:36 am
That will depend entirely on how you build your solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 6:37 am
It will be up to your 'special program' to guarantee Atomicity in the ETL process, it will need to have logic in it to roll-back incase a load fails.
November 9, 2010 at 6:51 am
Sir,
Can you give me the sample of logic based on my case? I can't imagine that.
November 9, 2010 at 6:55 am
It depends on how you are doing the inserts into the warehouse system. If you are using SSIS as suggested then could put all the insert data flows into one container and have set to load everything or incase of an error return the db back to the original state.
If you are custom coding this process then whatever code you create will need to have the same sort of logic.
November 9, 2010 at 7:13 am
ok sir. i'll try first. will get back to you. if i've any problem to built the system integration scripts.
January 9, 2011 at 7:16 pm
GSquared (11/8/2010)
I normally do ETL through SSIS, not through an external XML file, but it sounds like it should work, if I'm understanding you correctly.
steveb. (11/9/2010)
It depends on how you are doing the inserts into the warehouse system. If you are using SSIS as suggested then could put all the insert data flows into one container and have set to load everything or incase of an error return the db back to the original state.If you are custom coding this process then whatever code you create will need to have the same sort of logic.
Based on suggestion and others reading, I've an option to perform database integration as following
1. SSIS
2. SQL Service Broker
3. Linked Server
With GSquared and steverb suggestion, looks like I will choose SSIS.
I've question as following
1. My SQL Agent will run this SSIS Package periodically. Let's say the interval is 1 second. It's possbile sir? I'm worry that my SSIS will take a lot of server resources especially memory.
Need explanation
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply