SQL Server Magazine

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.

   Prev. page   [1] 2     next page