SQL Server Migration Assistant for Oracle Issues: November 2006

SSMA

Microsoft SQL SERVER MIGRATION ASSISTANT for Oracle Version 2.0

Welcome to the second release of Microsoft SQL SERVER MIGRATION ASSISTANT for Oracle.

http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx

This release brings about three major features:

1) Migration from Oracle to SQL Server 2005 or 2000. The previous release only supported migration to SQL Server 2000.

2) Globalization support. Automated migration of Oracle database with localized data or business logic.

SSMA UI currently only supports English.

3) Greater coverage of converter features.

Microsoft SQL SERVER MIGRATION ASSISTANT for Oracle automates all of the major steps in the database migration process from Oracle to SQL Server.

It provides comprehensive assessment reports; one-click conversion of all schema and data;

One-click conversion of almost all database-resident business logic;

All of these capabilities are made available from within our migration IDE.

Please see User's Manual for detailed documentation of the product features.

For any questions, suggestions or problems,

Please contact ssmahelp@microsoft.com.

To get more information on Oracle to SQL Server migration program,

Please visit http://www.microsoft.com/sql/migration/

Download SSMA for Oracle from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en

i.e. SSMA-V2-Setup.zip

It contains:

- SSMA-Setup-2.0.0.380.exe (Setup)

- SSMAExtPack-2.0.0.380.exe (Extension Pack to be installed on SQL server machine and Oracle server machine)

1. Installation:

----------------

Run the installation SSMA-Setup-2.0.0.380.exe. Follow the questions during the installation.

The important prerequisite is JRE 1.4.2, which should be installed on your computer.(Download it from https://sdlc2d.sun.com/ECom/EComActionServlet;jsessionid=5606D4B41B019B32E565717BE72C3B57)

2. Connection to Oracle database:

---------------------------------

Connect to the source Oracle database by specifying the following:

1) Oracle Server Host

2) Oracle Server Port (1521 - by default)

3) Oracle SID (from tnsnames.ora config file)

4) Username

5) Password

3. Connection to SQL Server database:

-------------------------------------

Connect to the target SQL Server database by specifying the following:

1) MS SQL Server Host

2) MS SQL Server Port (leave empty if default)

3) MS SQL Server Instance Name (leave empty if default)

4) Target Database

5) Target Schema

6) User Name

7) User Password

==================================

SQL Server Migration Assistant requires certain scripts to be installed on SQL Server you want to work with.

Please run Extension Pack Installer before running SQL Server Migration Assistant.

==================================

User's Manual can be found inside SSMA installation directory.

FAQ: How to Use SQL Server Migration Assistant

Published: March 25, 2005 | Updated: November 7, 2005

Review this FAQ to find answers to common questions about using the SQL Server Migration Assistant and related technologies.

Q. What is SSMA?

A. SQL Server Migration Assistant (SSMA) defines and automates all of the major steps in the database migration process. SSMA provides comprehensive assessment reports, one-click conversion of all schema and data, one-click conversion of almost all database-resident business logic, and automated testing of the converted code. All of these capabilities are made available from within a single migration IDE.

Q. What is Assessment Report?

A. Assessment Report provides quantitative and qualitative assessment. The set of reports provided by the platform allows the user, project manager, or decision-maker to evaluate the project complexity, size, and cost. The statistics provided by the Assessment Report include the total number of database objects, lines of code, estimated conversion complexity, and estimated amount of manual work. You can view these statistics through SQL Server Migration Assistant, or save them to a file in a Microsoft Excel-compatible format. You can also view an HTML report to see areas of conversion difficulties. See the SQL Server Migration Assistant product documentation for more information on how to run Assessment Report.

Q. What objects can be converted with SSMA?

A. With a single click, SSMA converts all database objects including stored procedures, functions, triggers, views, packaged procedures, packaged functions, and sequences. SSMA also provides context–sensitive warnings and output messages. The conversion engine encapsulates Microsoft's patent–pending technology that represents one of the most complete PL/SQL to Transact-SQL conversion capabilities available.

Q. How can I test the converted code?

A. You can use SSMA Tester to automate the testing of all converted database objects against the original Oracle-based code. Using automatically generated data sets, SSMA Tester verifies that the behavior of the converted code is identical to that of the original code, flagging those code objects that do not conform. Tester UI has an intuitive wizard that guides you through all necessary steps in order to test a particular procedure or function. For more information on SSMA Tester, see the SQL Server Migration Assistant product documentation.

Q. What is SSMA Extension Pack?

A. SSMA Extension Pack is a run-time library that emulates Oracle system functions (that come with Oracle system packages). Functions such as substr, round, and lpad are just three examples of Oracle functions emulation.

Note: Extension Pack Installer should run on a local SQL Server machine.

Q. What are the system requirements for SSMA?

A. SQL Server Migration Assistant requires Microsoft Windows XP, Windows 2000 Server, or Windows Server 2003, Microsoft SQL Server 2000 or 2005, and Java Runtime Environment (JRE) 1.4.2 and later. You can download JRE 1.4.2 from http://java.sun.com/j2se/1.4.2/download.html. SSMA is thoroughly tested with Oracle 8, 8i, 9i, and 10g.

Q. What versions of SQL Server will SSMA migrate to?

A. SQL Server 2000 or SQL Server 2005.

Q. How do I get a license key?

A. To get a license key, perform the following steps:

1. Install and start SQL Server Migration Assistant.

2. A pop-up window requests a license key. Follow the instructions in that dialog box:

• Click Create License Request.

• Click the link that takes you to the Passport login page.

• Enter your Passport credentials and contact information.

• You will be taken to a page where you can download a license key.

• Download the license Key file and put it into a license directory. Click the Refresh License button in the dialog box.

3. The Licensing dialog box should close.

Q. How can I scope my migration project?

A. Run Assessment Report and send the results to SSMAinfo@microsoft.com.

Q. How do I run Assessment Report?

A. To run Assessment Report, perform the following steps:

1. Go to Options, choose Report Preferences, and then Queries, and click the "Select All" button. Click OK.

2. Go to Options, choose Report Preferences, and then select the HTML Report Generator panel.

Select the "Generate expanded HTML database report" option.

Select a directory where you want to store your reports. Click OK.

3. Connect to the Oracle database you want to migrating from.

4. Connect to the SQL Server database you want migrate to. This is not a necessary step but it is helpful.

5. In the object-browsing tree on the left, select a schema you want to run Assessment Report on.

6. Run Assessment Report by going to Tools and then choosing Reports, then the Create Assessment Report menu, or just click the "Assessment" button. Be aware that the report may take a long time to complete because it is actually performing the conversion on the entire schema.

Upon completion, you will get a visual report with multiple statistics. You can save the report to a comma-separated format or click the "View expanded HTML report" option to look at the detailed HTML report.

7. When the report is complete, archive all HTML files from the directory configured in Step 2 above.

Q. How do I get started with SSMA?

A. Start by running the Assessment Report. It produces reports based on trying to automatically convert each of the Oracle objects (tables, triggers, stored procedures, functions, packages, and so on). Based on these reports you can see which objects have to be converted manually and which statements require manual intervention. You will also get an estimate of the complexity of manual conversion. After you finish assessing the potential migration issues, you can start converting objects.

Q. What are some main features of SQL Server Migration Assistant?

A. The following are some key features of SSMA:

• SQL Server Migration Assistant provides you with the ability to convert all objects at once or convert them one at a time. Additionally, you can move the data on a per-table basis or all tables at once.

• Just reading objects from a file turns out to be not enough in most cases, because data types cannot be recognized (for example, in %TYPE or %ROWTYPE cases), so the conversion is not complete, and a lot of manual effort is required.

• The converted objects do not move into the SQL Server database directly, but are stored in a local workspace, so that you can continue working on your conversion project without being connected to the database.

• A console window gives you an opportunity to enter any SQL statement or a group of statements to convert, even if they are not stored within the database.

Q. How can I get help while using SSMA?

A. For all technical questions, send an e-mail to ssmahelp@microsoft.com.

Q. What is new in SSMA V2.0?

A. SSMA V2 has the following new features:

• Migration from Oracle to SQL Server 2005 or 2000. SSMA V1.0 only supports migration to SQL Server 2000. Greatly improved PL/SQL to Transact-SQL conversion engine.

• Globalization support. SSMA V2 automates migration of Oracle database with localized data or business logic. SSMA UI currently only supports English.

• New Tester Wizard which greatly simplifies user interface of Tester component. You can now very easily test your converted code. See Tester section of SQL Server Migration Assistant User Manual for a detailed guide of the wizard.