Data Warehouse Sync
Posted on 2008-11-07
I haven't posted on these boards in a long time. I have come to the point where I really need the outlook of other smarter people than I. So basically the situation is this:
We have a DTS package right now that runs every 10 minutes in a production environment. This was developed years ago and definitely is wreaking havoc on our system due to table level locking. I just came onboard with this system so I have limited knowledge of it thus far, but I do know what the DTS package does.
We have two databases-- one for sales and one for our warehouse. These systems were, unfortunately, developed at different times by different groups of people. This is the root of our problem, we now have to maintain duplicate data in two locations. Right now, they use DTS to map the fields and also utilize the ActiveX (VBScript) portion of DTS for some business logic. Since this locks the table, it also locks people out of the systems when they are trying to access the data.
My thoughts on it was that we are abusing DTS. Really, DTS is a data-pump. We need data into our tables, we can do that easily with DTS. However, we are using it for syncing and mapping (with some added business logic). So my question is this:
What would be the best mechanism for keeping the data synched between tables? They are different databases, with different field names, and some fields require additional business logic. Initially, I was thinking of putting in triggers for the INSERT, UPDATE, and DELETE and then it would keep the data synched that way instead of a DTS package. I have pretty vast knowledge of MS SQL, but am no DBA guru, I'm a developer.
Thanks for your help,