Microsoft SQL Server Migration Assistant
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)
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.
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
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
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,
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
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
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
1.3 Simulation of Oracle Packages, Sequences and
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
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
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
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
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.
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