This article gives a reasonably detailed overview of the new Microsoft tool named Microsoft SQL Server Migration Assistant for easing database migrations from Oracle to Microsoft SQL Server. Here it is explained how the Microsoft tool, SQL Server Migration Assistant helps assess a migration task, convert PL/SQL code to T-SQL code, migrate data, test the migrated objects and deploy them reducing drastically the overall time for migration by automating several processes involved in database migration.
What is in this article?
• Installing SQL Server Migration Assistant (SSMA) and its extension packs
• Configuring SSMA options.
• Simulation of Oracle Packages, Sequences and Oracle-style exception handling in SQL Server.
• Migration Assessment Reports
• Schema Conversion and Migration
• Data Migration
• Converting Procedures, Functions, Views, Triggers
• Modes of Viewing
• Migration Testing
• Conversion on the fly (run-time code conversion from PL/SQL to T-SQL)
• Test-SQL
• Work-spaces
Detailed Discussion
Hitherto migrating Oracle databases to SQL Server used to be a tedious job for Database Administrators and Developers. Major challenges involved were estimating timeframes, converting Oracle PL/SQL objects (Procedures, Functions, Views, Tables, Triggers, etc) to SQL Server’s T-SQL, and equating differences in error/exception handling and usage of packages and sequences, datatype-matching, testing the migrated database objects and so on. There also used to be reservations regarding usage of third-party products as customers could not entrust a third-party with total responsibility of migration involving their mission critical databases either because the product company was little known or the product didn’t offer adequate support. Evaluating these challenges, Microsoft has come up with a new product for migrating Oracle databases to SQL Server. The product, named Microsoft SQL Server Migration Assistant (SSMA), not only provides accurate estimations and automates major tasks involved in migration (code conversion, data migration, testing the migrated database objects, etc) but also provides proper guidelines, timely issue-resolution and above all, comes with the support of a strong Microsoft expertise in Database Migrations and trained professionals. In this article, we would go through the various features of the SSMA. SSMA supports conversion and migration from Oracle versions 7.3, 8, 8i, 9i and 10g to Microsoft SQL Server 2000 and 2005.
1.1 Installation
Installation of SSMA involves two steps.
1.1.1 Installation of SSMA
This step installs the basic Graphical User Interface and other functional components like code conversion, data migration, etc. Installation can be done on the server hosting the Oracle database, the server hosting the SQL Server, or any remote server from which connectivity to both the Oracle database server and the Microsoft SQL Server can be established. After installation, licenses for SSMA have to be registered in order to enable its functionalities.
1.1.2 Installation of Extension Packs
This step comes after installation of the base SSMA software and involves creation of a database user called TEST_PLATFORM on the Oracle database and a database called Test_Platform_DB on the SQL Server. So during installation of extension packs, the Microsoft SQL Server and the Oracle database instance connection parameters would have to me mentioned. A database called SYSDB is also created on the SQL Server. SYSDB hosts simulations of Oracle methodology of exception handling, simulations of Oracle packages and sequences, Oracle string manipulation and date functions, etc. Packages, sequences and exceptions although are inherently implemented in SQL Server and are also much simple in their usage, SSMA tries to simulate Oracle methodology for code conversion in the database SYSDB. It is left to the user as to which technique they finally choose to implement.
The database Test_Platform_DB is used during the testing process where migrated database objects can be tested versus their source Oracle counterparts. Generated test scripts are stored in this database. During testing, calls to testable database objects in Oracle database as well as the SQL Server database originate from the Test_Platform_DB.
All extension pack objects in the SQL Server database SYSDB are owned by the database user , ssma. Objects in the SQL Server database Test_Platform_DB are owned by the database user, dbtest.
1.2 Configuring SSMA
Once Installation of SSMA is completed, the next step is to configure its various options. The primary options that must be configured are as follows:
1.2.1 Linked Server
For code conversion and deployment of the converted code to the destination SQL Server database (Target), linked server is not required. However, once a table schema is migrated, a linked server must be created for data migration. Similarly for testing the migrated target database objects against the original Oracle database (Source) objects, SSMA need to use the Source which must be configured as a linked server on the Target server. Linked server can be configured through a simple GUI within SSMA itself.
1.2.2 File Options
Logging is optional. Log files can be specified with their sizes, number of log files to keep and the type of events to log. SSMA generated Migration Assessment Reports can be stored as HTML as well as CSV files. Appropriate folder need to be specified for storing such reports.
1.2.3 Code Conversion Options
Code conversion can be specified for any or all of the Oracle system schemas apart from the user defined ones. User can also specify any or all of Oracle packages to be simulated on SQL Server. Options to whether convert Oracle Sequences to SQL Server Identities and exceptions and to generate ROWID columns on SQL Server target tables can also be specified.
1.2.4 Other Options
There are numerous other user-configurable options like parameters to include for generating Assessment Reports, generating test data for testing the migrated database objects or whether to use existing data in referenced tables for testing them, whether to generate DROP statements for converted objects, synchronizing SSMA workspace versions of database objects with the source or target databases, and so on. These options would be discussed as we go through the relevant topics down the module.
1.3 Simulation of Oracle Packages, Sequences and Exception Handling
SQL Server in itself offers numerous equivalents of Oracle Packages, Sequences and Exception Handling methods in terms of T-SQL system functions, identities, and system and user defined error messages and functions. However, SSMA offers a choice to users whether to keep Oracle methodologies or employ much simpler and easy-to-use SQL Server methodologies. For example, in order to read LOB columns in an Oracle database table, DBMS_LOB package may have to be used. Such a situation is handled internally by SQL Server without explicit usage of any package by the user. Another example would be the use of the Oracle exception TOO_MANY_ROWS which can be easily handled by capturing the SQL Server system error variable @@error or the function ERROR_MESSAGE().
In code converted by SSMA from PL/SQL to T-SQL, references to Oracle packages and exceptions are made through calls to their simulated counterparts in the SQL Server database, SYSDB where such simulations are stored.
While specifying conversion options, a user can choose to have SSMA simulate any or all of the Oracle Packages, do sequence-to-identity conversion or do conversion of exception handling.
1.4 Migration Assessment Reports
Estimating timeframes to implement an Oracle to SQL Server migration project is often tedious and in many cases undeterminable to a definite precision. This problem is solved to a large extent by a feature of high utility in SSMA called Migration Assessment Report.
SSMA calculates the complexity of the PL/SQL code based on the lines of code, the type of statements involved, usage of packages, sequences and exception handling, usage of aggregations, involvement of nested selects and cursors, etc. Based on the complexity thus calculated, it estimates the person hours required for migrating a particular database object from Oracle to SQL server. An Assessment Report also mentions as to what percentage of objects it can convert by itself and what percentage it can’t convert. For the portions that it can’t convert for some reason, it gives an estimate of the person hours needed for such task. Connectivity to just the source Oracle database is sufficient to generate a Migration Assessment Report.
An Assessment Report can be created per object or a group of objects or the entire source Oracle database itself. It can be saved as a CSV file or can be configured to be generated in HTML format. A typical Migration Assessment report is shown below:
Figure 1.1 Sample Migration Assessment Report.
1.5 Schema Conversion and Migration
The Schema Conversion feature converts the PL/SQL code for creating Oracle database tables to T-SQL code for creating SQL Server database tables. In SSMA, conversion of a table implies conversion of the table creation script, conversion of scripts for creating indexes and constraints on that table, conversion of triggers on that table. Single or multiple tables can be chosen for conversion at a time. Once converted, the resultant scripts for table conversion can be saved or simply applied to the Target database from within SSMA itself using the feature “Synchronize with Database”.
SSMA by default generates a ROWID column for each converted table. ROWID column is by default populated with uniqueidentifiers. If deemed undesired, the option to generate ROWID need to be disabled first.
SSMA by default converts Oracle data-types to equivalent SQL Server data-types without issues. However if an Oracle data-type is specified without a scale, then SSMA converts the same to the equivalent data-type in SQL Server with its maximum permissible scale. For example, Oracle VARCHAR2(36) would be converted to SQL Server VARCHAR(36) but Oracle VARCHAR2 specified without a scale would be converted to SQL Server VARCHAR(8000).
SSMA also offers a type-matching feature. User can explicitly choose the target data-type for a given source data-type. SSMA would then make conversions based on user-specified type-matching. Type-matching can be done at an object level or at a database level as a whole. A screenshot is given below in Figure 16.2:
Figure 1.2 Type-Matching in SSMA.
1.6 Data Migration
SSMA uses the SQL Server linked-server methodology to migrate data from Oracle database tables to SQL Server database tables. At a time, any or all of the tables can be chosen for data migration. In case multiple tables are chosen, SSMA migrates data sequentially. SSMA disables triggers and foreign key constraints on the target database tables before data migration. Post successful data migration, SSMA re-enables the triggers and constraints disabled earlier on the target database tables.
After data migration, SSMA generates a storable Data Migration Report with percentage of migration and success and / or failure messages.
SSMA does not support data migration for those tables that have LOB columns in them. For such objects, SQL Server’s OPENQUERY or OPENROWSET functions or utilities such as BCP and BULKINSERT can be used.
1.7 Converting Procedures, Functions, Views, Triggers
Code conversion is also a one click operation in SSMA. Any or all of the stored programs and views can be chosen at a time from the Oracle database to be converted from PL/SQL to T-SQL. SSMA not only converts the regular procedures but also procedures that include dynamic SQL. All Oracle exceptions and calls to Oracle packages in the source PL/SQL code are automatically converted. Converted objects can again be either saved to a SQL file on the physical disc or can simply be deployed on to target SQL Server database using the “Synchronize with database” feature.
It must be mentioned here that when converting functions, the SSMA option to convert exceptions must be set to off. Similarly, when converting triggers, the SSMA option to generate ROWID must be set to on.
SSMA can not convert Oracle database programs written in Java or PRO C.
1.8 Modes of Viewing
SSMA has three modes of viewing as mentioned below:
1.8.1 Synchronized Mode
In this mode of viewing, when an object in the Source Database is selected, the corresponding converted SQL Server object is automatically highlighted. This is especially useful when there are thousands of Source objects and the converted target database object needs to be found out.
1.8.2 Zebra Mode
In this mode of viewing, the code for an object in the Source Database and its converted counterpart in MS SQL Server are shown in color coding. This way, every line of the source code is mapped to every converted line in the target code. This simplifies decoding as to what methodology SSMA has used for code conversion.
1.8.3 Show-Diff Mode
The code in the workspace may be different from that in the database for the same object in cases, where the code has been modified in the database or in the workspace or in both and both versions have not been synchronized as yet. Show-Diff mode of SSMA points to existence of such differences between the database and SSMA workspace versions of database objects. Use can then choose to synchronize the workspace version with the database version or vice-versa.
While in Show-Diff mode of viewing, the capability to convert code and migrate data is disabled. User would have to exit the Show-diff mode to re-enable these functionalities.
If SSMA is not in any of the three modes of viewing mentioned here, then it is said to be in the normal mode of viewing.
1.9 Migration Testing
A feature of high utility in SSMA is migration testing available through SSMA’s Tester Wizard. Converted and migrated database objects like procedures, functions and Views can be tested using the Tester Wizard.
For procedures and functions, the same input parameters if any can be supplied to both the source and the target procedures/functions and the output parameters or result sets are compared. For views, the actual data is compared on both the sides.
Performing Migration testing involves several operations as detailed below:
1.9.1 Test Cases
A new test case can be created from within SSMA itself. The proceeding of the migration test would fall under the test case. After creation, test cases can be saved, held on for the session without saving, or dropped.
1.9.2 Selecting Objects
The next step would be to select a database object like a stored procedure for testing. A prerequisite would be that the Oracle objects should have been migrated to SQL Server database before being chosen for testing. At a time, one or multiple database objects can be selected for testing.
1.9.3 Preparing Test Case
This is the most crucial step in testing. User has the Option to use the existing data in referenced tables within a procedure, function or view for testing or can choose to have SSMA generate test data in the referenced objects. SSMA generated test data would be placed in auxiliary tables. User would have to backup the original data in tables, the facility for which is provided within the Tester Wizard. User can choose the number of test rows to be populated in each of the underlying tables. Facility to include a range of values based on the data-type of the underlying columns, specify chances of occurrence of nulls, or let the SSMA generate random values on its own also exists.
1.9.4 Backing up Real Data
In case the user chooses not to use existing data in tables for testing, SSMA offers the option to backup up underlying (real) data before executing the test case. It is safe to backup real data before testing the migration with random data in tables. Backing up of data is however optional in case the user chooses to use existing data in the underlying tables. However as a general rule, it is safe to backup real data before any kind of testing as stored programs may involve updates to the underlying tables, especially in an OLTP environment.
1.9.5 Executing the Test Case
This operation broadly involves actual execution of the test case for testing the migrated stored procedure, function or view. Now that the user has specified in the test case whether to use existing data or randomly generated data, and has chosen to back up the original data, SSMA prompts the user for input parameters. User can specify the number of different input values that the objects should be tested with. User can also specify the range or types of values for the input parameters based on their data-types as well as specify the chances of occurrence of nulls. Once this is done, SSMA displays the data that would be used against both the source (Oracle database object) and corresponding target object (Microsoft SQL Server database object).
Then when the user executes the test case, SSMA replaces the original data in the underlying tables with test data from auxiliary tables and then executes internally generated stored procedures in the database Test_Platform_DB, passing the same user-chosen input parameters to the T-SQL stored procedure / function as well as to the original PL/SQL procedure / function and compares the output parameters or result sets generated. This is repeated as many times as the number of iterations specified by the user. (For views, only result sets are compared.) If perfect matching of outputs from the source as well as target is observed, SSMA generates a test report with all the iterations. The Test Report displays the input parameters passed in each iteration of testing and whether the test was successful or not. The test report can be saved.
Migration Testing using SSMA can be done for one or multiple objects of same or different type at a time. It saves a lot of time in terms of generating test data, executing the objects and comparing the results. Practically, it is limited only by the hardware and the quality of the programs written.
1.9.6 Restoring Real Data
Once testing is finished for that object, real data backed up earlier can be restored back to the underlying tables replacing the test data. This facility exists within SSMA itself.
A screenshot of progression of the various phases in a sample migration testing is provided here:
Figure 1.3: Stages in Migration testing
1.10 Conversion on the fly
In several applications, some PL/SQL statements may be embedded in the application code itself instead of as calls to procedures and functions. It could be a very tedious exercise to trace such statements and then convert them to T-SQL. In order to solve such issues, SSMA hosts a feature called run-time-converter. The run-time-converter converts such PL/SQL code to T-SQL code at the run time through its SSMA wrapper. This enables usage of the same application with the migrated database with minimal changes to application code in terms of connection parameters and the like.
Currently SSMA run-time-converter is not transparent to the user and exists as a feature that the user can’t configure and control.
This feature can be better explained by the figure below:
JAVA Application Connected to Oracle:
JAVA Application Connected to Microsoft SQL Server:
Figure 1.4: Conversion on the fly
1.11 Test SQL
Test SQL is actually a node in the source database within SSMA using which a user can type in PL/SQL code and check its conversion to T-SQL with SSMA. This node is more for statement conversions only.
1.12 SSMA Workspaces
Initially, both the source Oracle database and the target SQL Server database have to be registered and connected in the current installation of SSMA tool. Once connection is established, user can save the work into a workspace file on the hard disc. On subsequent connections to SSMA, user need not connect to the source and target databases again. The saved workspace file can simply be loaded into SSMA. While working with workspace, all conversion capabilities work as normal. However the tasks of migrating data, testing the migration and deployment of objects to target database through SSMA wouldn’t work. When usage of such functionality is necessitated in any stage of the project, user can restore the database connection by passing the authentication credentials.
Workspace is actually a snapshot of the state of both the source and target databases. All user configured options in SSMA are also saved to the workspace and are retained when the workspace is loaded into the SSMA. Workspace file is many orders of magnitude smaller than the actual source and target databases.
1.13 Conclusion
Microsoft SSMA automates roughly around 90% of all code conversion from PL/SQL to T-SQL and data migration. It can be used for deployment of converted objects and for testing them as well. When converting to SQL Server 2000, it would be a good idea however for users to take precautions for tables that have row lengths greater than 8060 bytes in Oracle. With SQL Server 2005, such a precaution is not required due to row-chaining.
I have personally used SSMA in at least two migration projects this year with remarkable results in terms of precise estimations, accuracy of conversion, testing and above all, the enormous amount of time saved. Manual work was involved only in cases where tables had rows exceeding 8060 bytes in Oracle databases where alternative data-types for column definitions had to be used on SQL Server side.
It is advisable that users try the product with a simple Oracle database first and then try out migrating larger ones after some practice. SSMA Version 1.0 for Microsoft SQL Server 2000 is available from the Microsoft website. Version 2.0 for SQL Server 2000 and 2005 is in Beta right now and is also downloadable.
The link is following: http://www.microsoft.com/sql/migration/default.mspx
Please follow the link below on 'My Experiment With Truth' i.e. SSMA BY EXAMPLE.
http://ssmaexample.blogspot.com/
Search Engine Optimization and SEO Tools
