I am neither DBA on Oracle nor on SQL Server, but I know the middleware of
OneWorld. This guy converts you one data base into another. Different path
codes to different environments. Copy the one you want to have converted to
This way I performed the upgrade from OW 73.3.1/Oracle 8.05 to Xe/SQL
Server, and it worked.
We have finished migrating to SQL*Server two months ago and did encounter some small problems but over-all it went pretty good. We had a Microsoft consultant on-site to help resolve issues and to escalate if needed. I highly recommend you do the same if you are new to SQL*Server. Over-all the developers are happy with Micosoft's toolset and performance has been very good. The high points of the migration are as follows.....
- Oracle's default collation is Case sensitive, SQL*Server is case In-Sensitive. This is a real good way to find bad data that was input in mixed case!
- OneWorld does not support SQL*Server the same way it does Oracle. It does not allow you to specify filegroups for indexes and data as it does for tablespaces in Oracle. You must manually move the indexes to different drives if you are concerned about balancing I/O. This means you need twice the amount of disk space for the data filegroup until the indexes are moved. This also means that every time you re-gen a table in OneWorld you must manually move the indexes.
- We had some problems with fat clients until we realized we needed a update version of MDAC (2.6 or higher).
- We used DTS import/export utilities to migrate the data. Generating the scripts were a problem because of the number of tables. We played some tricks like creating a view owned by PRODDTA called all_objects that replaced the system view. The view was based on the user_objects view but only returned PRODDTA tables. When you try to generate the scripts you will understand why we did this!
- At the same time we migrated to Oracle we upgraded to XE from B73.3.1. This caused some other problems. XE now has primary keys on the tables. By definition primary key columns can not be defined as 'null' nor can it contain null values. B73.3.1 allows null values in those columns as they only have unique constraints on them. Conversion of the data discovered what amounted to bad data where null values were stored in the columns used to define the unique constraints.
I hope this helped