-
The project I'm on now might have a requirement to keep a copy of data in an Oracle database in a Sql Server database. We need to keep the Sql Server database up to date with changes from the Oracle database, and obviously seek some sort of transactional updates instead of dumping an entire table/schema/whatever.
I don't have much experience with Sql Server, but digging around, I found that Sql Server has a feature called Data Transformation Services, which should do what we need. I'm searching for what reading material I can find, but does anyone have experience with this type of data transfer?
Can I do some sort of incremental exports with DTS or am I limited to queries/full tables? If I'm limited to queries, can I do something to query fields by date? Eg, query on date1, next time DTS runs query anything added after date1 and so on? Maybe a stored procedure on the Sql Server side.
I believe we will seek daily updates, and there should be very few new records in the Oracle database so we're not talking about large transfers.
If at all possible I'd like to avoid anything in the Oracle database(stored procedures, etc), although exports or flatfile dumps might be a possibility.
Is DTS the right way to go or is there a better option?
Thanks
-
Ive heard DTS isnt too great. It might be worth coding something in java which can access both databases easily.
-
If your overall transaction number is low and you will be running it only once a day like you stated, DTS may be the way to go. We do something similar, and it works fine. You can query on dates so that you only get the updated/new records. You can use the Microsoft ODBC Driver for Oracle if you don't want to install any Oracle components on the SQL Server box.
If you're going to do something more complicated than that, you may want to look into a more sophisticated tool, but why spend the money purchasing and the time and effort building something else if you can utilize something you already have in-house.
Also, I've heard that improvements were made to DTS in SQL Server 2000 vs. 7.0.
Good luck!
-
I have been using DTS of SQL Server and StarQuest, a 3rd party tool to replicate data between Oracle and SQL Server7. I found both are very slow when the data is huge.
If your data is very small ( less than 5000 rows) , you can use either one of them.
For a huge data, I found BCP of SQL Server is very fast and good.
-
Thanks for the information all, I'll look deeper into DTS once our requirements are finalized.
