Retrieving newly added database records

Let's assume we have the following generic scenario:

1. An RDBMS as a data source, which is a live database (fills up with data all the time).
2. An SQL Server 2008 as a data destination, in a remote location.

We need to write a software solution that will:

1. After an initial run, it will frequently (let's say few times a day) extract some specific data from the source. The "specificness" of the data lies in the fact that once the mappings/transformations are designed, they will remain that way.
2. The extracted data will be placed into the destination, awaiting to be consumed by another process (outside our scope). Awaiting to be consumed means that they will stay temporarily there.

With the following characteristics:

1. The extraction can be a bit complex (meaning that it's not a straightforward extraction from a specific table, but a combination of joins).
2. Lots of data involved in the sources. Normally about tens of millions of rows but not expected to exceed a couple hundred.

W开发者_StackOverflowith the following desired restrictions:

1. Being as much database-agnostic from the source side as it is possible.
2. Maintain minimum intervention in the source RDBMS, because it doesn't "belong to us" and any changes/addition/requests follow an "unflexible" process.
3. We cannot take for granted that the tables involved in extraction from the source will have some kind of timestamp, auto-increment key or something else that will eventually help us do a "range query" and retrieve records from "this value and afterwards".

The question(s): Because we'll be frequently extracting data from the live source, how can we efficiently retrieve the newly added records bearing in mind the above characteristics/restrictions? And if you had to break one of the restrictions, which one would it be? Is there a term that describes this problem (something like data differential or...)? My main concern lies on how to retrieve that "difference" in an efficient manner.

NOTE: I support the idea of breaking the database-agnosticism, and putting into play useful mechanisms provided from the various RDBMS (metadata?) to get the most recently added rows from the tables we're interested into. I apologize for being generic, but I'm expecting a generic answer as well.

---

My main concern lies on how to retrieve that "difference" in an efficient manner.

How will you identify the difference in a useful way? Given that

• you can't base the difference on timestamps.
• you can't base the difference on sequential numbers.

You might have to rely on the only generally applicable approach: store the extracted keys, and use them to find the difference. (This is fine for new rows, but it doesn't help with updated rows.)

Whether you can do that efficiently depends a lot on where you're allowed to store the extracted keys, and what kinds of connectivity you're allowed to use between the live data and your stored keys.

---

Let me begin by saying that i do not envy you for having to deal with that kind of requirements.

That said, if there's no way to tell what has been added after the last import, one has to pull all the data and compare it to the target, no?

I can think of two ways to minimize the load on the source RDBMS:

1. Update the structure to allow for identification of new items by id or timestamp.

2. Add triggers to the RDBMS that forward any INSERTS and UPDATES to you and maintain a mirror that does not have the shortcomings of the source.

Then again someone else might come up with a better solution. Possibly involving voodoo :)

Good luck.