Migrating from Microsoft applications to Oracle
How can I easily convert my SQL 2005 databases to Oracle's latest iteration? We are moving away from Microsoft applications and want to migrate to Oracle. What do I need to do to prepare?
Software/Hardware used:
SQL 2005
Software/Hardware used:
SQL 2005
ASKED:
July 15, 2010 6:10 PM
UPDATED:
July 15, 2010 9:55 PM
RELATED QUESTIONS
- Migrating data from Oracle 9i Database to SQL Server 2005
- Errors: Violation of UNIQUE KEY constraint 'UN_FILENAME'. Cannot insert duplicate key in object
- Oracle to SQL Server Migration - User Defined data types
- Are there any specific considerations we should be aware of as we attempt to migrate applications to a Hyper-V virtualized machines?
- Migrating SQL Server 2005 back to SQL Server 2000
Answer Wiki:
You'll need to do a full code review to ensure that Oracle even supports all the features that you are using. Any unsupported features you'll need to find a way around.
Then you'll need to script out the schema, switch all the data types from the SQL Server data types, to the Oracle data types, and import some data for testing.
Then you'll need to rewrite all the stored procedures in PL/SQL (this includes any inline code within the application). Don't forget to change all your variables to the Oracle data types.
Then you'll want to rewrite any large batch operations as row by row cursors so that they perform better in Oracle (Oracle does better with row by row operations, where SQL Server does better with batch operations).
This will take months (if not longer) and will probably cost you a fortune. What's the driving factor to move from Microsoft SQL Server to Oracle?
To see all answers submitted to the Answer Wiki: View Answer History.
The process doesn’t need to be completely manual.
Both vendors would want to facilitate the process of migrating from other vendors to their products, and this is not the exception. As Microsoft has its SQL Server Migration Assistant to migrate from Oracle databases, Oracle offers a tool that will help with the migration from SQL Server to Oracle.
Oracle SQL Developer is a tool that will help with this process, although I don’t think such migration can be completely automated.
Take a look at these articles:
Migrating from Microsoft SQL Server to Oracle
Migrating a Microsoft SQL Server Database to Oracle Database 11g
These are the core features of the tool regarding this migration (but it seems that SQL Server 2008 is not currently supported):
-Automatically converts column data types to appropriate Oracle data types.
-Automatically resolves object name conflicts, such as conflicts with Oracle reserved words.
-Parses and transforms T-SQL stored procedures, functions, triggers, and views to Oracle PL/SQL.
-Provides advanced customization capabilities such as the ability to change data type mappings, delete and rename objects.
-Generates reports about the status of the migration.
-Generates the DDL scripts for the creation of the destination Oracle database.
-Generates scripts for data movement
-Displays informational, error, and warning messages about the migration in a progress window.