Schema conversion. To facilitate the code migration, you use
SSMA's Schema Conversion feature, which converts the Oracle PL/SQL code to T-SQL
code. The first step in the code conversion is to create DDL scripts for tables
and all supporting structures such as indexes, constraints, and triggers. You
can convert single tables or all the tables at once. The scripts are generated
to the SSMA workspace for verification, editing, and saving for future use,
but they don't actually create the objects on the destination server. Once you're
satisfied with the converted DDL, the scripts can be loaded to the target server
by using the Load to Database tab located on the top menu bar of the
SSMA for Oracle page or by right-clicking an individual object and selecting
Load to Database, as Web Figure
1 (http://www.sqlmag.com, InstantDoc ID 96569) shows. SSMA handles type
mapping and conversion with some specific considerations to account for inconsistencies
such as a type without a scale. In this case, SSMA will convert Oracle data
to the same data type in SQL Server with the maximum permissible scale. For
example, Oracle VARCHAR2(10) would be converted to VARCHAR(10) but Oracle VARCHAR2
would be converted to VARCHAR(MAX) (as Web
Figure 2 shows). SSMA also offers a type-matching feature. You can choose
the target data type for a given source data type by using the Target type
drop-down menu, as Web Figure 3
shows. Type matching can be done at an object level or a database level.
Conversion of functions, procedures, and triggers. Object code
other than table DDL is also easy to create in SSMA. Objects can be selected
from the source Oracle schema for conversion to the target SQL Server machine.
All objects are first scripted and saved in the SSMA workspace for review or
further changes and then loaded into the SQL Server database. SSMA is also designed
to convert object code to dynamic SQL. Oracle PL/ SQL code with calls and exceptions
is automatically converted to dynamic SQL.
Modes of conversions. SSMA supports five configuration settings
including Project Information, General (Conversion and Migration), Loading Objects,
GUI, and Type Mapping. Within these five configuration settings are four additional
settings for more end-user control: Default, Optimistic, Full, and Custom (as
Figure 3 shows). SSMA recommends
using the Default mode for most users. The Optimistic mode is designed to retain
more of the Oracle syntax and tends to be easier to read, although it might
not be as accurate as Full mode. Full mode does the most complete conversion;
however, the resulting code is harder to read than code converted with the Optimistic
mode. Custom mode gives users complete control over how the code and type conversions
occur from Oracle to SQL Server.
Data Migration
Although SSMA 2.0 uses a linked server to migrate data from Oracle to SQL Server,
SSMA 3.0 doesn't require a linked server. In both versions of SSMA, you can
migrate data by right-clicking the schema objects and choosing Migrate Data.
If multiple tables are selected, SSMA will migrate the selected table data sequentially.
Triggers and foreign key constraints on the target database tables are disabled
before data migration and enabled upon completion. The reported results of a
completed data migration appear in the Data Migration Report, which Figure
4 shows. Data migration from Oracle to SQL Server is a bulk-load operation
that moves rows of data from Oracle tables into SQL Server tables in transactions.
The project settings let you configure the number of rows loaded into SQL Server
in each transaction. Note that SSMA 2.0 doesn't migrate tables that have large
object (LOB) columns. Instead, you'll have to manually migrate tables containing
LOB columns by using OPENQUERY or OPENROWSET functions or bulk utilities such
as BCP and BULKINSERT.
Testing
One of the most important and often neglected steps in migration projects is
testing, but SSMA's Testing Wizard simplifies the testing process. The Testing
Wizard is used to verify that converted and migrated objects such as procedures,
functions, and views are properly functioning in SQL Server. During procedure
and function testing, the same input parameters can be supplied to both the
source and the target procedures and functions, so that the output parameters
or result sets can be compared. Migration testing consists of several steps,
including preparing and executing test cases.
Test cases. When creating a new test case, you must select
one or more objects for testing that are already migrated to the SQL Server
destination. One of the most important steps in testing with SSMA is determining
whether to use the existing data in referenced tables or have SSMA generate
new test data for referenced objects. If you choose to use SSMA-generated test
data, it will be placed in secondary tables. The Testing Wizard then backs up
the real data in the referenced tables and uses the generated data from the
secondary tables. As a best practice, it's recommended (although optional) to
back up real data before testing because stored programs usually perform some
sort of Data Migration Language (DML) conversion on the underlying tables. SSMA
offers the option to back up underlying (real) data before executing any test
case. If you want more control, you can set the row counts and the arrays of
values based on the data type of the base table columns and possible nulls and
generate test data accordingly.
Test case execution. After you specify whether to use
real or generated data and the data is backed up, the user is prompted for input
parameters. The input parameter options include the total number of different
values, the range or types of values, and the possibility of null occurrences.
Before final execution, SSMA displays the data used against the Oracle source
and matching SQL Server target. When you execute the test case (which Web
Figure 4 shows), SSMA replaces the original data in the underlying tables
with test data from the secondary tables and executes internally generated stored
procedures in the Test_Platform_DB database. This process is completed by passing
the user-defined input parameters to the T-SQL stored programs as well as to
the original PL/SQL stored programs. Once the test case is finished on both
the source and target database, the results are compared. This process is repeated
as many times as necessary. A Test Report is generated upon completion of the
entire test case; the Test Report displays the input parameters passed during
each run and whether the test was successful. Migration testing using SSMA saves
time in terms of generating test data, executing the objects, and comparing
the results. Once testing is complete, the real data can be restored to the
base tables.
Application and Dynamic SQL Conversion
As is the case in every database environment, not all the code for accessing
data is stored in the database as stored programs. Application logic is often
embedded within client applications, dynamic SQL, and other called programs.
This problem can have a significant effect on any migration task because of
the complexities of rewriting, testing, and deploying all-new code to support
the migrated database. SSMA addresses this problem with the run-time-converter.
The run-time-converter converts embedded and dynamic PL/SQL code to T-SQL code
at runtime through a wrapper. Although converting code at runtime might not
be an optimal long-term solution, it certainly offers a viable option until
the code can be rewritten to take advantage of SQL Server's features.
Translation
SSMA includes a platform for Test SQL in which PL/SQL code can be typed and
converted to T-SQL. Generally, Test SQL is used for statement translations or
conversion.
Migrating to SQL Server
SSMA is a great tool for automating the conversion of data and database code
from Oracle to SQL Server. The added feature of data migration makes
SSMA a viable option for migrating from Oracle to SQL Server. For more information
about migrating to SQL Server, go to the Migrate to SQL Server Web site at http://www.microsoft.com/sql/solutions/migration/default.mspx