1

I have inherited a reporting structure that is quite the Rube Goldberg device. I have a collection of MS Access 2003 databases that I have begun to migrate to SQL Server. My current hangup is that we have a VPN/ODBC connection to an Oracle database that belongs to an external entity, and the server for this hosted service is over 2000 miles away. While the Access "solution" does "work", it is extremely slow and any kind of development is difficult and time-consuming.

We decided that we would create a mirror database at our local site for all reporting and analytic services, and we were able to transfer a good portion of the data to our local "mirror" database using Microsoft's Oracle migration tool. Unfortunately this tool doesn't seem to provide a functionality to perform deltas.

I have been able to query the remote database with applications such as MS Access and Flyspeed SQL Query, bu they have their limitations. For example, Access 2003 is having a lot of trouble pushing data into the local SQL database. It can do very small data sets, but it chokes on anything more than a few thousand lines. Flyspeed can see all the data sets but I am unable to perform an "INSERT INTO" query for whatever reason, and it times out very easily. It also lacks the ability to schedule a query, from what I can gather, and I don't see that it can be scripted.

Is there a methodology by which I can perform deltas with the VPN / ODBC constraint, as well as with the SQL Server / Oracle differences?

Thanks,

Nate

migrated from stackoverflow.com Jul 29 '14 at 20:24

This question came from our site for professional and enthusiast programmers.

  • I don't see that I am in a "programming" category. How can I move the question? Or shall I just re-ask in Database Administrators Stack Exchange? – n8. Jul 29 '14 at 20:02
  • This is a programming site. See What topics can I ask about here in the Help Center. There's no way you can migrate by yourself. You need the moderator's help. To get the moderator's help, you click Flag and then select Other. Once in Other, ask that the question be migrated to DBA SE. – jww Jul 29 '14 at 20:07
  • Assuming you have no ability to modify the Oracle database, your options for performing a "diff" efficiently are fairly limited. In SQL Server you could add a "linked server" to the Oracle database that would allow you to directly query the Oracle database. You could use that to compare rows in the SQL Server database to rows in the Oracle database, copying new/modified rows to the SQL Server database. – Max Vernon Jul 30 '14 at 4:43
  • If you have the ability to modify table structures in the Oracle database, you may be able to add a hash value to each row, with a corresponding hash value in the SQL Server database for each row - then you'd simply compare the hash values to see if any rows have changed. – Max Vernon Jul 30 '14 at 4:45
  • 1
    @n8. you can merge your 2 accounts. See here: I accidentally created two accounts; how do I merge them? If you have any trouble, flag a question (this one!) for moderator attention and explain what the problem is. – ypercubeᵀᴹ Aug 3 '14 at 12:04
1

In terms of getting the data into the SQL Server database, you can use that ODBC connection directly via the "linked servers" feature (see http://msdn.microsoft.com/en-us/library/ms188279.aspx) - this would allow you to transfer data directly from the remove Oracle instance down to your local SQL Server one with statements as simple as INSERT <localtable> SELECT <stuff> FROM <server>.<database>.<schema>.<table> WHERE <condition>. I've used this to pull data from both local and remote databases (over VPNs or other tunnels) and found it to be reliable as long as the connectivity between the sites is reliable. It is worth making sure that either your VPN or ODBC setup are performing stream compression: this will increase latency a little but could greatly improve transfer times as your data is most likely to be quite compressible.

Pulling down changes only is going to be something that will depend heavily on the database design.

The best case scenario is if everything is audited so you can easily know which updates have happened since you last picked up changes: check current "last audited action", pull down rows that have been added/changed between that and the last recorded action last time, merge these rows into your local tables, then record the ID/timestamp of the last audited action for next time. The reason to check the audit id/stamp only once at the beginning rather than for each table is to help consistency: you don't have more uptodate data in the tables you transfer last than you do in those you check first. Any other audit/history recording format can be used of course (records holding a "last modified date" and so forth).

If the data has no obvious audit structure like this then things are going to be more convoluted - comparing data in large tables between the two sites is not going to be very efficient. You can reduce the amount of data being transferred by pulling down from each table the primary key column(s) and a hash of all the others, compare the hashes with those generated locally, then request the rows that don't watch locally. You are still going to be transferring something for every row here though (for an UUID PK and a SHA1 hash that is 36 bytes/row plus any padding if transferred in binary form, and it is unlikely to be easily compressible so the VPN can speed it up that way) and if the table contains thinner rows on average than the length of the hash you should transfer the lot as you'll end up doubling (or worse) the transfer with the initial hashes.

You might find you have to use a mix of techniques (updates from audit, checksums for wide tables with no audit, just plain complete transfer for thin tables with no audit) if some of your data is audited and some not.

Another option you might be able to consider (though it requires cooperation from the other end to implement, and may have cost implications as you'll need a local Oracle license) is to use Oracle's equivalent of SQL Server's log shipping method to keep a copy up-to-date on an Oracle instance locally (and move the data from there to SQL Server or Access if that is an explicit requirement rather than just a convenience because that is what you already have installed locally). It is unlikely it that this is possible as an extra for you as it may interfere with the database's primary backup strategy, but if a log shipping equivalent (or differential backups) is being used already for the DB's backup plan then perhaps you could "piggy back" onto the arrangement by having the logs or differential backups sent to you as well as the backup infrastructure. This way the DBMS handles only pushing changes to you (though may push more than by other methods if the same rows are updated many times in the time covered by a given log segment, as you'll get the log of each change to replay not just the final result).

What you are essentially doing with any of the above is emulating replication, so if you have any say on infrastructure or replication is already in use then using that may be an option too.

  • I ended up setting up a SQL Server on the machine with the VPN to act as a bridge between the remote DB and our data warehouse, then pulling daily data via OPENQUERY and Dynamic SQL. It works very well for getting the data over, now I'm trying to figure out a faster way to sync in the delta data. My criteria is "WHERE REMOTEDB.PK > LOCALDB.PK or REMOTEDB.EDITDATE >= @inputeDate" so that it pulls new or edited records. I don't know what you mean by "audited", but it's fairly rudimentary what I'm doing. In any case, many thanks for your feedback. – n8. Aug 6 '14 at 18:28
  • By "audited" I was meaning the system keeps an easily queryable list of actions that have been taken. This would make it easy to identify what has been inserted/updated/deleted since you last ran your sync. You might find just using the EditDate check instead of comparing the PKs too to be more efficient, and note that neither of those checks will identify deleted records (unless the application does soft deletes). Also when comparing incrementing integer keys, remember that it is possible to (SET IDENTITY INSERT ON and so on) for these to be out of order. – David Spillett Aug 7 '14 at 9:08
  • You nailed it with the PK integers, some very old records migrated from a prior server had a higher integer set, so this criteria ended up being invalid. But yes, we have "deleted" and "deleteDate" columns, so we can track deletions this way, and those count towards edited records... at least for the tables I am syncing. Unfortunately, I don't believe there is an action list that I can query. But so far things seem to be rolling along fine. So we'll see! – n8. Aug 18 '14 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.