Preface

  • Article
On This Page

Overview Overview
Intended Audiences Intended Audiences
How to Use This Solution Guide How to Use This Solution Guide
Job Aids Job Aids
Contributors Contributors
Feedback Feedback

Overview

The Oracle on UNIX to SQL Server on Windows Migration Guide provides practical guidance on the processes and procedures to be followed while migrating from Oracle databases (versions 8i and later) on UNIX platforms to Microsoft® Windows® Server™ 2003 and Microsoft SQL Server™ 2000. This guide also presents strategies and procedural guidance to convert existing applications for use within the SQL Server 2000 and Microsoft Windows Server environment. This guide provides additional information and references to online resources about migrating other components of your application ecosystem, including the server and network infrastructure, development and test environment, and user accounts.

The guide is based on the experience of consultants working in the field and organizations that have successfully migrated from an Oracle on UNIX to a SQL Server on Windows environment, and it comprises the best lab-tested, customer-proven, cross-product technical guidance from Microsoft on planning, building, deploying, and operating the migration solution. All processes and procedures contained in this guide have been validated through the Microsoft Technology Adoption Program (TAP), which is a beta program that consists of customers and partners who have evaluated and validated the various technologies and practices described in this guide.

An important assumption made in this guide is that your organization has decided to migrate all or parts of the Oracle database and application environment from UNIX to SQL Server 2000 in a Windows environment. Because of this assumption, this guide does not present a competitive analysis of Oracle versus SQL Server 2000, or UNIX versus Windows. This guide does help you decide which migration strategy is best for your organization given your specific business and technical requirements, and it explains how to plan for and execute that strategy.

This process-driven approach to migration is applied to the Oracle database, the UNIX applications that rely on the database, and the application programming interfaces (APIs) that connect the applications to the database. The strategies and methods for migrating these components vary according to the size and complexity of the database, application, and infrastructure of the Oracle source system. The strategies and methods also vary according to any new business or technical requirements your organization develops as a result of migrating to the Microsoft Windows platform.

Because there is so much variance in the possible approaches to migration, this guide provides numerous examples and best practices to help you better understand both the concepts and detailed processes involved in migration. A series of job aids (templates, spreadsheets, and questionnaires) are included as part of this solution guide to help you better analyze, plan, structure, and perform the migration.

The information under the following two headings outlines the technical scope of the migration strategies and methods provided in this guide.

Technical Subjects Covered in This Guide

The following technical topics and subject areas are included in this guide:

  • Migration of a typical Oracle database, including objects (such as tables, indexes, views, triggers, and stored procedures) and the contents of the objects to SQL Server.

  • Methods and procedures that focus on taking advantage of existing applications on UNIX platforms by communicating with SQL Server on Windows.

  • Migration of applications and APIs to work under Microsoft Win32®, Microsoft Windows Services for UNIX, and Microsoft .NET.

  • Application and API interoperability with SQL Server, including:

    • Java 2 Enterprise Edition (J2EE) and Java Database Connectivity (JDBC)

    • Open Database Connectivity (ODBC)

    • Perl, including the DBI and DBD modules

    • PHP

    • Python

    • Pro*C

    • Oracle Forms

  • Microsoft offers the SQL Server Migration Assistant toolset for the migration of Oracle databases to SQL Server, including:

    • Migration Analyzer—analyzes the source Oracle databases and produces vital statistics on the size and complexity of the migration.

    • Schema and Data Migrator—migrates the Oracle schemas and moves the data into the new SQL Server objects.

    • SQL Converter—converts code found in Oracle objects to their T-SQL equivalents.

    • Migration Tester—automatically tests the converted code.

These tools can be downloaded from https://www.microsoft.com/sql/migration/. The tools are in beta as of the date of publication for this solution. Version 1 of the tools is slated for release in June 2005 and will be available for download at the same location.

Technical Subjects That Are Not Covered in This Guide

The following technical topics and subject areas are not included in this guide:

  • Oracle versions earlier than 8i and Oracle 10g

  • SQL Server versions earlier than SQL Server 2000

  • Design of hardware architecture

  • Modeling and design of databases

  • Procedures for database administration functions, such as capacity planning, backup and recovery, performance tuning, and monitoring

  • Aspects of the database that could be rendered inapplicable in the new environment, such as archived data and backups

  • Features available in Oracle that do not have corresponding features in SQL Server, such as spatial data, XML DB, flashback query, SQLJ, and XSQL

  • Installation of application clients, or client connectivity to the application server or database

  • Migration of the operating system to Microsoft Windows Server 2003

  • Support for Open Management Group (OMG), an open-source, java-based standard for metadata

  • Data extraction from enterprise resource planning (ERP) systems

While these technical subjects are not explicitly discussed in this guide, information about many of them is available from Microsoft. Where appropriate throughout this guide, links to more information are provided.

Intended Audiences

Generally, the intended audience for this solution guide includes medium and large-sized IT organizations that want to migrate their database and applications from Oracle on UNIX to SQL Server 2000 on Windows. More specifically, this guide is written for all of the members of the migration team, from business decision makers to development to operations. As a result, not all chapters in this guide are relevant to all team members, and the knowledge prerequisites for using this guide are expressed as an aggregate of the knowledge required of the team. The rest of this section provides brief descriptions of the typical audiences for this guide, followed by a brief statement of the aggregate knowledge prerequisites for developing the solution described in this guide. The intended audience for individual chapters is identified in the "How to Use This Solution Guide" section later in this Preface.

The following list organizes common job titles and organizational responsibilities under the Microsoft Solutions Framework (MSF) role most commonly associated with that title or responsibility. The relationship between this solution guide and MSF is discussed in more detail in the "How to Use This Solution Guide" section. In some organizations, a role may be filled by a single team member or many.

  • Stakeholders and High Level Decision Makers. These team members can be subdivided into two groups:

    • Business Decision Makers (BDMs). This group often includes the CIO and IT director of an organization. These personnel decide the business priorities for purchasing and implementing solutions within the organizations. IT BDMs require a high-level understanding of the technical solutions being explored so that they can assess the value that the solution can provide to the organization.

    • Technical Decision Makers (TDMs). The focus of TDMs is to determine the technology used to solve business problems. IT TDMs must understand the business drivers the organizations face along with the functionality delivered by multiple technology scenarios.

  • Product Management Role. This role usually includes team members with the following job title and responsibilities:

    • Product Manager. This team member acts as a link between the team members of the project and the customers of the solution by handling high-level communication and managing the customer's expectations. The Product Manager ensures that the team addresses the customer's needs, concerns, questions, and feedback throughout the project. Product managers must understand the business needs of the solution, including operations, business processes, and policies.

  • User Experience Role. This role usually includes team members with the following job titles and responsibilities:

    • Usability Expert or Technical Writer/Editor. Ensures that the new solution is easy for customers to use. The Usability Experts ensure that documentation and training is provided to end users to use the new solution effectively.

  • Program Management Role. This role usually includes team members with the following job title and responsibilities:

    • Project Managers. These personnel are responsible for managing the resources and schedule for the migration project from start to end.

  • Development Role. This role usually includes team members with the following job titles and responsibilities:

    • IT Architect. The primary role of the IT architect is to carry out decisions made by the TDMs. Team members with this responsibility are most involved during the Planning, Developing, and Stabilizing Phases of the migration project.

    • Oracle Database Administrators. These personnel are knowledgeable about the physical characteristics, logical characteristics, performance, and service requirements of the source database. These personnel also have knowledge of any scripts and tools that are used in database administration.

    • SQL Server Database Administrators. These personnel are expert in architecting and implementing SQL Server databases. These team members will design the target SQL Server database environment and perform key tasks during the actual migration.

    • UNIX System Administrators. These personnel have complete knowledge of the UNIX servers and the operating system installations, such as versions, packages, configuration, and security.

    • Windows System Administrators. These personnel have complete knowledge of the Windows operating system and the Microsoft application product line. These team members should have some experience in defining and implementing server technologies in Windows.

    • UNIX Application Developers. These personnel have knowledge of the applications that are running in the current UNIX environment and their implementation details.

    • Windows Application Developers. These personnel have performed development in the Windows environment for the technologies involved in the solution.

    • Security Specialists. These personnel have a wide base of knowledge about security issues, including how security relates to networks, servers, databases, and applications.

  • Test Role. This role usually includes team members with the following job titles and responsibilities:

    • Test Engineering Managers. These personnel have knowledge about testing application and database solutions and also have managerial experience.

    • UNIX Database and Applications Testers. These personnel have knowledge about all databases and applications that are running in the current UNIX environment and provide input when test plans are created for the migrated environment.

    • Windows Database and Applications Testers. These team members have knowledge of databases and applications in both UNIX and Windows. These personnel are experienced at creating and executing test plans.

  • Release Management Role. This role usually includes team members with the following job titles and responsibilities:

    • Deployment Manager. These personnel have knowledge of managing the deployment of the new database and applications in the production environment.

    • Technology Specialist. These personnel have knowledge of the technology and processes in a data center operation. They have intimate knowledge of the solution being developed and will aid in transitioning them into the production environment.

Knowledge Prerequisites

It is assumed that the technically-oriented audience for this guide has, in aggregate, the following specific technology competencies and proficiencies:

  • Expert level of UNIX development and administration

  • Expert level of Windows development and administration

  • Expert level of SQL Server and Oracle administration

  • Knowledgeable staff to maintain the new environment and the technologies involved

  • Knowledgeable staff that understand any interaction between the UNIX and Windows environment when interoperability is created and maintained

How to Use This Solution Guide

The organization of this guide is based on the industry-proven need to manage IT projects according to a disciplined process that improves the likelihood of the project's success. It is designed to be used with a companion guide, the UNIX Migration Project Guide (UMPG). While this guide contains the technical and solution-specific information needed for the project, the UMPG provides the disciplined process steps for using this information in the context of a migration project and a team organization model ("people and process" guidance).

To facilitate their side-by-side use, both guides use project phases as an organizational device. Specifically, they follow the structure of the Microsoft Solutions Framework (MSF), which defines five distinct phases for IT projects: Envisioning, Planning, Developing (or Migrating), Stabilizing, and Deploying. Each guide presents the information (process or technical) needed for a phase within chapters named for that phase. For example, in the solution guide, business and technical information needed for the initial decision-making is in the Envisioning chapter and detailed procedures and scripts are in the Migrating chapters.

The UMPG is essentially "MSF applied to UNIX migration projects." It begins with an overview of MSF and then describes the processes that belong to each phase and the team roles responsible for them — it is not, however, meant to serve as a comprehensive introduction to MSF. In-depth information about MSF is available to interested readers on the Microsoft Solutions Frameworks Web site at https://www.microsoft.com/msf.

The reason for separating the process guidance from technical and project-specific guidance is to keep this guide as lean as possible. It is recognized that some readers need to focus narrowly on project tasks, while persons with project management and team lead responsibilities need to digest the UMPG guidance and apply it to the project.

Because organizational personnel and project team members tend to have different levels of involvement during different phases, the division of content according to project phase also supports the ability to focus on the material that is most relevant to a particular responsibility.

It is important for you to note the content scope of this guidance. The application migration and interoperation-related information in this solution only discusses issues pertaining to the database connectivity logic. It does not discuss general application porting issues between UNIX and Windows such as mapping UNIX system calls to Windows APIs and migrating the graphical user interface (GUI). For detailed information about UNIX to Windows application migration, refer to the UNIX Application Migration Guide (UAMG) available at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnucmg/html/ucmglp.asp.

Note Although the two guides are designed to be used together, it is not necessary to follow the MSF processes and team guidance described in the UMPG if the organization has an alternative project methodology in place. In that case, the UMPG would be used merely to map the MSF phases and team structure to the elements of the organization's methodology. Readers who will use this guide to implement a project should read at least the overview of MSF in the UMPG to familiarize themselves with the MSF Process Model, the MSF Team Model, and MSF terminology.

Figure 0.1 represents the chapters of interest to readers with different areas of responsibility.

Figure 0.1 Chapters of interest by team role

Figure 0.1 Chapters of interest by team role

Organization by Chapter

The following chapter list provides an overview of the content and structure of this guide.

  • Preface.

  • Chapter 1: Introduction to the Microsoft Solutions Framework. This chapter provides a brief introduction to the Microsoft Solutions Framework (MSF) and how it is applied to migration projects.

  • Chapter 2: Envisioning Phase. This chapter provides guidance to carry out the various activities of the Envisioning Phase in an Oracle on UNIX to SQL Server on Windows migration project. These activities include understanding your business and technical goals, assessing your current situation at a high level, building a high-level solution concept, setting up the project team, and assessing project risks.

  • Chapter 3: Planning Phase. This chapter provides guidance to carry out the various activities of the Planning Phase in an Oracle on UNIX to SQL Server on Windows migration project. These activities include creating a detailed assessment of the current environment, developing the solution design and architecture, and producing detailed project plans and project schedule.

  • Chapter 4: Developing: Databases — Introduction. This chapter introduces the different tasks in migrating the Oracle database to SQL Server 2000.

  • Chapter 5: Developing: Databases — Migrating the Database Architecture. This chapter describes the steps for creating an instance of SQL Server which is equivalent in architecture to the original Oracle database.

  • Chapter 6: Developing: Databases — Migrating Schemas. This chapter shows how to migrate a schema owner and its objects to a SQL Server database.

  • Chapter 7: Developing: Databases — Migrating the Database Users. This chapter contains detailed steps for creating users in the SQL Server databases and granting them the same kind of privileges they had in the original Oracle database.

  • Chapter 8: Developing: Databases — Migrating the Data. This chapter explores the different options for migrating the application data from Oracle to SQL Server. It provides details in the usage of each of the options.

  • Chapter 9: Developing: Databases — Unit Testing the Migration. This chapter contains the processes for testing the migrated database, its objects, and data.

  • Chapter 10: Developing: Applications — Introduction. This chapter introduces the procedures to migrate the application and the API (the connectivity tier between the database and the application) from the existing solution to the proposed solution.

  • Chapter 11: Developing: Applications — Migrating Oracle SQL and PL/SQL. This chapter provides specific steps and procedures for converting SQL statements and PL/SQL code for use with SQL Server.

  • Chapter 12: Developing: Applications — Migrating Perl. This chapter provides specific steps and procedures for migrating Perl applications to the SQL Server on Windows environment.

  • Chapter 13: Developing: Applications — Migrating PHP. This chapter provides specific steps and procedures for migrating PHP applications to the SQL Server on Windows environment.

  • Chapter 14: Developing: Applications — Migrating Java. This chapter provides specific steps and procedures for migrating Java applications to the SQL Server on Windows environment.

  • Chapter 15: Developing: Applications — Migrating Python. This chapter provides specific steps and procedures for migrating Python applications to the SQL Server on Windows environment.

  • Chapter 16: Developing: Applications — Migrating Oracle Pro*C. This chapter provides specific steps and procedures for migrating Pro*C applications to the SQL Server on Windows environment.

  • Chapter 17: Developing: Applications — Migrating Oracle Forms. This chapter discusses the options available for migrating an Oracle Forms-based application.

  • Chapter 18: Stabilizing Phase. In this chapter, the solution is tested in an environment similar to the production environment. This chapter describes the various testing options and processes that can be implemented to ensure the quality of the solution.

  • Chapter 19: Deploying Phase. This chapter describes the procedure for moving the solution into the production environment and transferring ownership of the solution to operations. The chapter also identifies the tests that need to be performed on the solution during deployment.

  • Chapter 20: Operations. This chapter provides references to existing operational guides to administer a Windows-based server infrastructure and SQL Server 200 based databases.

  • Appendix A: SQL Server for Oracle Professionals. This appendix provides information to help Oracle DBAs who do not have SQL Server experience learn about its administration.

  • Appendix B: Getting the Best Out of SQL Server 2000 and Windows. This appendix provides reference to material to optimize and take advantage of SQL Server and Windows.

  • Appendix C: Baselining. This appendix provides detailed information about collecting statistics in SQL Server.

  • Appendix D: Installing Common Applications and Drivers. This appendix provides installation instructions for applications and drivers that may be used while migrating the application.

Document Conventions

This guide uses the style conventions shown in Table 0.1.

Table 0.1: Document Conventions

Text Element

Meaning

Bold text

Bold text is used in the context of paragraphs for commands; literal arguments to commands (including paths when they form part of the command); switches; and programming elements, such as methods, functions, data types, and data structures. User interface elements are also identified with bold font.

Italic text

Italic text is used in the context of paragraphs for variables to be replaced by the user. It is also used to emphasize important information.

 

Monospace font

Used for excerpts from configuration files, code examples, and terminal sessions.

 

Monospace bold font

Used to represent commands or other text that the user types.

 

Monospace italic font

Used to represent variables the reader supplies in command line examples and terminal sessions.

UPPERCASE

Transact-SQL keywords and SQL elements.

%SystemRoot%

The folder in which Windows Server 2003 is installed.

Job Aids

The job aids included with this guide can be used to support the various tasks performed during the different phases of the migration. The aids are in the form of questionnaires, tools, and templates used for designing, planning, and managing the migration project. The job aids are either in Microsoft Excel spreadsheet or Microsoft Word document format. They are available in the Tools and Templates folder in the zip file download of this guidance at https://go.microsoft.com/fwlink/?LinkId=45289.

The purpose of providing job aids is to help you speed up the development of your project documents. New or additional guidance is not provided in the job aids. Instead, the job aids are generic templates that you should customize to fit your requirements.

Table 1.2: Job Aids Descriptions

Job Aid

Description

Vision/Scope

Use this template to describe the goals of the project and the approaches that the team will use to achieve those goals.

Project Structure

Use this template to define how the project is structured and managed and how the solution will be created. This template will help you control and coordinate the entire project.

Migration Risk Exposure Rating Form

This spreadsheet will help you identify the possible migration risks and their consequences. In addition, this document also helps you to prioritize the risks and prepare a mitigation plan for each risk.

Assessing the Current Environment Template

This template provides separate spreadsheets to assist in assessing the application, database, and server environments.

Development Plan

This template helps you to describe the solution development process used for the project. This plan provides the technical details of what will be built, and it provides consistent guidelines and processes to the teams creating the solution.

Test Plan

This template helps you to describe the strategy and approach used to plan, organize, and manage the project’s testing activities. It identifies testing objectives, methodologies and tools, expected results, responsibilities, and resource requirements.

Pilot Plan

This template contains information to help prepare and execute a successful pilot. It includes pilot scope, user interactions and pilot evaluation

Deployment Plan

This template helps you to describe the actions necessary for a smooth deployment and transition to ongoing operations. It covers the processes of preparing, installing, testing, training, and transferring the solution to operations.

Contributors

The following organizations and individuals contributed to this project.

Program Manager

Dhilip Gopalakrishnan (Microsoft)

Author

Scalability Experts

Editors

Thomas Olsen (Volt Technical Services)

Gaile Simmons (Microsoft)

Architects

Peter Skjøtt Larsen (Microsoft)

Jason Zions (Microsoft)

Test

Sandor Kiss (Microsoft)

Infosys Technologies

Feedback

Please direct questions and comments about this guide to cisfdbk@microsoft.com.

Download

Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows

Update Notifications

Sign up to learn about updates and new releases

Feedback

Send us your comments or suggestions