Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Nickel Contributor
Posts: 66
Registered: ‎03-30-2009

Interesting Ideas anyone have some ...???

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.

 

1. Scribe

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..

 

Thanks

Highlighted
Employee
Posts: 191
Registered: ‎03-13-2009

Re: Interesting Ideas anyone have some ...???

would it be all data across entities in the schema or would it be selected entities such as the main entities? maybe account,contact,opp,ticket,campaign,lead,address,products, orders?
Highlighted
Nickel Contributor
Posts: 66
Registered: ‎03-30-2009

Re: Interesting Ideas anyone have some ...???

All data, All Tables .. essentily a copy of the orcale database to the SQL database
Highlighted
Employee
Posts: 191
Registered: ‎03-13-2009

Re: Interesting Ideas anyone have some ...???

what bout plugins and customizations? i guess that would be also one of the requirement?
Highlighted
Copper Super Contributor
Posts: 27
Registered: ‎06-23-2009

Re: Interesting Ideas anyone have some ...???

[ Edited ]

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).

 

Regards,

 

Jim Sutton

Message Edited by jamessutton on 12-22-2009 06:40 AM
Highlighted
Employee
Posts: 535
Registered: ‎03-30-2009

Re: Interesting Ideas anyone have some ...???

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.

 

 

Todd Hardin
SalesLogix Professional Services Group
Highlighted
Employee
Posts: 320
Registered: ‎03-30-2009

Re: Interesting Ideas anyone have some ...???

Did you need a one-time-only migration, or something that will synchronize for a period of time?
Highlighted
Nickel Contributor
Posts: 66
Registered: ‎03-30-2009

Re: Interesting Ideas anyone have some ...???

One time only Phil
Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Interesting Ideas anyone have some ...???

Phil,

 It sure would be VERY nice to resurrect the Full Copy Utility to handle these kind of things. 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Highlighted
Bronze Super Contributor
Posts: 153
Registered: ‎04-08-2009

Re: Interesting Ideas anyone have some ...???

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.