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.
End of Article
