SQL Server Migration Assistant - 22 Aug 2007

After attending a SQL Server 2008 (code-named Katmai) review session and Microsoft's first annual BI Conference in Seattle this past spring, I'm impressed by the improvements Microsoft has made to the scalability and extensibility of the SQL Server technology stack. Microsoft continues to make significant strides in shedding the long undeserved perception that SQL Server isn't a powerful database platform. With SQL Server 2008 due for release later this year, migrating to SQL Server from another database management system (DBMS) has become even more compelling, although it can be an increasingly difficult task to prepare for and accomplish in complex environments. However, Microsoft provides a free tool—SQL Server Migration Assistant (SSMA) for Oracle—to help you migrate to SQL Server from other DBMSs. SSMA can help you outline a migration task, convert PL/SQL code to TSQL code, migrate data, test migrated objects, and deploy your migrated database. Let's examine how to install and configure SSMA, as well as how to use the SSMA Testing Wizard to test your migrated database.

Installing SSMA and Extension Packs

Installing the SSMA toolset is a two-part process. First, you have to install the main application on a system that can access both the source (Oracle) and destination (SQL Server) database servers. Although SSMA is free, a SQL Server license is required for use. Connecting, registering, and saving the associated license file is easy. The main SSMA application has a small footprint of about 9MB and takes only a few minutes to install.

Once you've installed the main application, you must install extension packs on the source and destination database server instances. Specific permissions on both systems are required because users, databases, and other supporting objects need to be created. DBAs don't give these permissions out easily (for security reasons), so you'll need to work with them to create a login with permissions to CONNECT, Create any Procedure, Type, Trigger, Execute any Procedure, and Select any Table and Sequence. This part of the installation process takes just few minutes to complete if connectivity and permissions have been established.

In SSMA 2.0, a user ID called TEST_PLATFORM is created in the Oracle instance, whereas the SQL Server database receives two databases named SYSDB and TEST_PLATFORM_DB. All the objects in the SYSDB database are owned by user ID ssma. SYSDB is used to simulate certain internal Oracle features, such as exception handling, packages, sequences, date functions, and string manipulation. Although SQL Server supports its own versions of packages, sequences, and exceptions, SSMA uses the simulations during code conversion. Ultimately, the end user must determine which method to deploy and support. The Test_Platform_DB database is used to test migrated objects and data from the Oracle instance; testing scripts are stored in this database. Testing calls to objects in the Oracle instance and the SQL Server destination originate from Test_ Platform_DB. Objects in Test_Platform_DB are owned by user ID dbtest.

SSMA Configuration

SSMA has several configuration options. The following sections outline the most important settings.

Linked servers. Initial connectivity to the Oracle instance for the schema extraction doesn't require a linked server; if you choose to let SSMA manage data migration, you must create a linked server. The linked server is also used to compare results from Oracle and SQL Server when testing is complete. The linked server can be configured through SSMA's Connect to Oracle dialog box, which Figure 1 shows.

Logging options. You can enable SSMA to generate a log of conversion and migration tasks in comma-separated value (CSV) or HTML formats. You can also specify the size of the log files and the destination directory. Configure the logging options to best fit your project's auditing requirements.

Code conversion options. Most migrations will involve a user- or application-specific schema, although SSMA can also convert system schema objects, which is useful if there are code elements referencing specific system objects. You can also specify Oracle packages to be simulated on SQL Server. In addition to the simulation option, sequences can be converted to identities and exceptions and ROWID columns can be specified.

Additional options. Additional user-configurable options, such as parameters used to generate SSMA Assessment Reports, test data for testing the migrated schema objects, Data Definition Language (DDL) creation script adjustment, and SSMA workspace synchronization with schema versions between the source or target databases, are also available. I'll discuss many of these options in more detail later.

Oracle packages, sequences, and exception handling. SQL Server and Oracle have different methods for handling certain database tasks. For example, Oracle has a more robust exception-handling function that's difficult to reproduce in SQL Server. These differences are often at the center of migration challenges. To ease these challenges, SSMA enables the simulation of Oracle's packages, sequences, and exception handling, which are stored in the SYSDB database. SQL Server offers many comparable native solutions to Oracle's packages, sequences, and exception-handling methods in terms of T-SQL system functions, identities, and system- and user- defined error messages and functions. SSMA lets you continue using Oracle's methodologies or use SQL Server's methodologies instead. SSMA can also be configured to simulate Oracle packages, perform sequence-to-identity conversions, and handle exceptions.

Assessment Reports. The SSMA Assessment Report calculates the complexity of the Oracle PL/SQL code based on several factors, including the number of lines of code, the statement types involved, package usage, sequences, exception handling, aggregations, and the complexity and presence of nested Select statements and cursors. Based on these factors and other considerations, SSMA then estimates the man-hours required for migrating schema objects from Oracle to SQL Server. You can configure the Assessment Report to include the percentage of objects it can convert and the percentage it can't convert. For the code that can't be automatically converted, an estimate of the man-hours needed to do so manually is given. An Assessment Report can be created before any task is started and needs to be connected only to the source Oracle database. Figure 2 shows a typical SSMA Assessment Report.

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