Microsoft SQL Server Migration Assistant

Introduction

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