Need advice on database integration

Question

Need advice on database integration

Little Nick

SSCrazy

Points: 2202
More actions
November 5, 2010 at 3:48 am

#242623

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 own
What's have done so far as follow
1. 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 minutes
This is what I think
1. Current solutions architecture on integration, looks bad
2. 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 of SQL Server trigger.
Really need an advice

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

A trigger would work. You could also just run a job every 1 or 2 minutes to check for the presence of data, and do it's work if it's there.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I think you should look into Service Broker. This sounds like a classic case for it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Little Nick SSCrazy Points: 2202 More actions · Answer 3

Jack Corbett (11/5/2010)
I think you should look into Service Broker. This sounds like a classic case for it.

Sir,

Looks like Service Broker hard to implement compare than trigger. Did a Service Broker works like trigger? Do the transaction after insert, update, and delete.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

Service Broker may be a more difficult initial setup, especially with a learning curve, but it is designed for this sort of thing.

I have not yet implemented a solution using Service Broker, but if I were in your shoes I'd do it. You'd still have a trigger that inserts into a queue then you have that queue send a message to a queue in the other DB (converstaion) and a process that reads the destination queue and processes the message. The benefit is that all the Service Broker stuff is asynchronous, once you insert into the queue your transaction on the source DB is complete. Everything is queued until processing is complete so if you need to take down one of the servers the source DB will save the messages in the Queue until it comes back up. Also no .NET programming needed.

Your current process works and looks similar to things I've done in the past, except we used MSMQ to move data between servers so we had services running on both servers to read and write to MSMQ.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Little Nick SSCrazy Points: 2202 More actions · Answer 5

Little Nick

SSCrazy

Points: 2202

November 5, 2010 at 9:16 am

#1247184

tq sir

Little Nick SSCrazy Points: 2202 More actions · Answer 6

If I want to using Trigger, it's wrong?

Need explanation

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Little Nick (11/5/2010)
1. Current solutions architecture on integration, looks bad
2. 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.

May I? - in such a situation I would ask to myself...

-How much memory uses the C# program?

-How much of a impact in performance is this causing?

-Are there users complaining about performance in timeslots that actually match the running timing of the C# program?

-What makes me think a different solution will use less resources?

-Am I attempting to fix something that isn't broken?

Hope this bring a different point of view to the matter at hand.

_____________________________________
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.