12-21-2009 06:45 AM
I am looking over a project where our client is looking to migrate away from Oracle into SQL Server. I have looked at a few different ways for the migration of data.
2. Full Copy
3. SQL SSIS
I am leaning towards the SQL SSIS process, I am looking for ideas on this to see if anyone has already migrated Oracle Data to SQL and if so what gotcha's did you come accross in the process..?
Anyone might have a better way to move the data I'd be glad to hear it..
12-21-2009 07:16 AM
12-22-2009 03:36 AM - edited 12-22-2009 03:40 AM
We've been using SSIS for quite sometime and I have to say that so long as you don't mind doing some scripting it works extreemly well. In fact I have build an entire SSIS solution to migrate/share data between two different SalesLogix systems. While we've not integrated an Oracle to SQL server scenario so I can't illuminate snags you may run into but I can tell you some things we had to watch out for between two SQL server SLX databases.
First of all we made our solution so it could work on an ongoing basis and would not break remotes. This means that we used the provider whereever possible. The downside is that with the SalesLogix provider you can't easily use all the data flow tasks in SSIS. We had a lot of inconsistancies with how SSIS pulled field data types through the provider so we ended up using a lot lookups using native database connections instead.
In the end I found that all updates were better handled by a scripting task. We had build subroutines to evaluate values field by field so that we only made the absolutely necessary changes to keep sync logs to a mininum.
Some of the main things to watch out for in general are:
1. If you created your security groups and users disparately in the two systems then you'll have to be careful to map all the ids appropriately (i.e. the AssignedTo field on the Ticket record actually using seccodeid instead of userid
2. UTC fields - You'll have to watch these types of fields carefully.If you read from a native connection you'll get the underlying GMT value but if you write using the provider it will convert this into GMT again which puts you off by whatever timezone you're in. Because we ended up using native lookups for most of the data we had to do a special merge of a data reader using the provider to end up writing the correct values to UTC fields.
3. RecordIds - If you use the same recordids your job can be simpler but you'll have to remember to update the keys in your destination table so records created manually don't generate duplicate ids and cause failures. In our SSIS jobs we ended up generating new ids using the provider for all records and cross-referenced them to avoid this as well as any other conflicts from other databases (we had to bring in data from two different SLX databases into one).
While I haven't used FullCopy in a while I did once use it to convert a SQL to Oracle. I would say that this might be your best bet if this is just a one time job and then you can SSIS any issues which arise later.
One last thing. Although I've never attempted this, have you ever considered creating a remote office database in SQL Server from your Oracle host and then changing the dbtype on your remote office database to be the host. Back when I first started creating remote offices I was amazed at how completely the remote office database was. It would be a pretty quick way to do what your attempting if it works and might be worth a try (Probably not recommended by Sage though).
12-22-2009 09:37 AM
Hi Jim -
Say, might not be something you ever revisit, but just in case.
For the UTC issue - it is possible to set the SLX OleDB Connection to use a specific timezone. If you use GMT/UTC with no daylight savings, then the SLX OleDB provider would not change any of your DateTime values.
12-23-2009 01:46 PM
12-30-2009 01:28 PM
You can try making a remote database using an instance of SQL 2005/2008. I understand that remotes lack data in certain security tables bus yo may be able to copy those using DTS jobs.
Import Wizard may work well also, since the tablenames and field names in the source and target DBs will match.