SQL Server Migration Assistant - what are the conversion rules between Oracle and SQL Server during the migration?
This was the big question posed by the users when a migration project is initiated at one of client's place.
SQL_Server_Migration_Assistant is a must tool to asses the issues when you have a mammoth task of data migration between SQL Server and other data sources such as Oracle & SYBASE. You may not know that not all Oracle database objects have direct equivalents in SQL Server. There are many additional objects are created while this tool is executed that will provide a proper phase of emulation for the migration rules that covers few important aspects such as:
-
Each object in Oracle such as table is converted to SQL Server format that includes indexes, constraints and triggers that are defined in Oracle data source, more information from this Migrating Oracle Data Types link.
-
A small exception arises for Oracle View, during the conversion process this view is created as normal table in SQL Server.
-
Now it comes to stored procedures topic, when the Oracle procedures consists nested subprograms, which means that another procedure or function can be declared and called locally within the main procedure the current version of SSMA does not support nested subprograms, for such conversions refer to this Converting Nested PL/SQL Subprograms link.
-
About User Defined Functions (UDF) in Oracle, they are created as 2 objects such as function and stored procedure. In this stored procuedure the logic within the function is incorporated which is executed in a seperate process.
-
Now for triggers here is better reference that talks about trigger conversion in Migrating Oracle Triggers.
-
One of the Technet article refers that SQL Server has no exact equivalent to Oracle sequences. SSMA can use one of two sequence conversion methods. The first method is to convert a sequence to an SQL Server identity column. That is the optimal solution, but as Oracle sequence objects are not linked to tables, using sequences may not be compatible with identity column functionality.
You may be aware that both Oracle and SQL Server use different dialects of the SQL language such as PL-SQL & T-SQL. Though both have different dialect the base language is ANSI-SQL where SSMA solve most of the problems introduced by this. For example, Oracle uses CONNECT BY statements for hierarchical queries, while SQL Server implements hierarchical queries by using common table expressions. The syntax of common table expressions does not resemble the Oracle format, and the order of tree traversal is different.
The only exception for SSMA is conversion of Dynamic SQL statements that are included in actual statement of Oracle procedures or views, because the actual statement is not known until execution time and, in most cases, cannot be reconstructed at conversion time. Here is the manual piece of task you need to perform by using the SSMA by referring to metabase tree that contains a special node named Statements in which you can create and convert ad hoc SQL statements. If you can manually reproduce the final form of a dynamic SQL command, you can convert it as an object in the Statements node.
So the main task of conversion needs to be performed as a step by step process irrespective of database size within your environment. I don't suggest to use or depend entirely on SSMA tool but when you have this tool in hand it will be easy to understand the migration process and data conversion issues between these 2 major data sources. Best practices dictates to roll through the migration within your development environment first and then follow the guidelines within production servers, practice makes perfect.