SQL Server Migration Assistant - 23 Aug 2006

As SQL Server has climbed the ranks of enterprise-level database servers, it’s become a popular replacement for existing Oracle databases thanks to its comparable scalability, built-in business intelligence (BI) functionality, lower cost, and ease of use. However, converting Oracle databases to SQL Server has always been a manual process fraught with pitfalls, including migrating incompatible database objects and code from one data-base platform to the other.

With SQL Server 2005, Microsoft released a new tool, SQL Server Migration Assistant (SSMA) that’s designed to facilitate the migration of Oracle databases to SQL Server 2005. You can download it free at http://www.microsoft.com/sql/solutions/ssm/ssmav2.mspx. SSMA provides a single IDE that handles most of the tedious tasks required for migration.The 8MB download runs on Windows Server 2003 or Windows XP and converts from Oracle 10g,9i,8i,8, and 7.3 to SQL Server 2005 or 2000. Let’s look at the four most important features SSMA offers.

Migration Analysis

SSMA’s migration analysis feature estimates migration time and difficulty.It generates an assessment report with time estimates for manual migration as well as percentage estimates for how much of the database can be automatically migrated. As you might expect, the larger and more complex the database, the more time-consuming and costly its migration. In general, the complexity increases when you have more business-logic objects such as stored procedures, functions, and triggers as opposed to more database objects such as tables and views.

Schema Conversion

Another important feature in SSMA is its ability to perform database schema migration.The schema is the basis for defining the databases, tables, and views used by the Oracle database. Because of differences in the database capabilities, not all objects are mapped on a one-to-one basis. For example, if an Oracle table uses multiple BLOB columns per table, the second BLOB occurrence will be mapped to a second SQL Server table because SQL Server supports only a single BLOB column per table.

Data Migration

SSMS’s data migration feature lets you specify how you want the data moved.You can migrate the entire database all at once or you can perform a table-by-table migration. DTS or SQL Server Integration Services (SSIS) can be used with more complex transformations.

Language Conversion

Probably SSMA’s most important feature is its ability to convert stored procedures, triggers, and functions from Oracle’s PL/SQL programming language to SQL Server’s native T-SQL language. SSMA’s conversion tool can’t automate the entire conversion because of the syntax and procedure differences between PL/SQL and T-SQL, but it can successfully migrate most objects.You can use an included SSMA tester tool to automate the testing of the converted Oracle database objects.