Chapter 4 - Developing: Databases - Introduction

On This Page

Introduction and Goals Introduction and Goals
Migrating the Database Migrating the Database

Introduction and Goals

For a migration project, the Developing Phase is the time when the team builds the solution components — code and infrastructure as well as documentation. Typically, this work consists of modifying existing code in a way that enables it to work within the new environment. When new code is written, some aspect of the original component usually remains unchanged — for example, exposed APIs, or specific component behaviors. Both the modification of existing code and development of new code in this context are considered to be migrating activities.

Although the development and migration work is the focus of this phase and this guidance, the entire team is active. For example, some team members are creating deliverables such as training materials, rollout and site preparation checklists, and updated pilot and rollout plans while others are doing functional testing. Some development work may continue into the Stabilizing Phase in response to test results.

This chapter serves as an introduction to the database development-oriented chapters. Chapters 5 through 8 describe and demonstrate how to implement a Microsoft® SQL Server™ database that resembles the source Oracle database as closely as possible. These chapters also describe how to produce a solution that meets your organization's requirements for performance, functionality, availability, scalability, and recoverability. The following list provides an overview of the content in each of the database development-oriented chapters:

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

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

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

  • Chapter 7: Developing: Database — Migrating the Database Users. This chapter contains detailed steps in 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: Database — Migrating the Data. This chapter explores the different options in migrating the application data from Oracle to SQL Server. It provides details in the usage of each of the options.

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

The Developing Phase formally ends with the Scope Complete Milestone. At this major milestone, the team gains formal approval from the sponsor or key stakeholders. All solution elements are built and the solution features and functionality are complete according to the functional specifications agreed upon during planning. Table 4.1 describes the major tasks that need to take place while migrating the database.

Table 4.1: Major Tasks and Deliverables



Migrate the Oracle database architecture

SQL Server instance that is equivalent in structure to the source

Migrate the schemas

SQL Server databases housing application schemas

Migrate the users

SQL Server users with similar privileges as the source

Migrate the data

Complete database ready for use

Migrating the Database

Because they are both relational databases, Oracle and SQL Server have more similarities than differences in their features and functions. Both Oracle and SQL Server provide all the core functionality expected of a RDBMS, including concurrency, security, backup, recovery, scalability, availability, session management, transaction management, storage management, resource management, and so on. Both exhibit a moderate degree of compliance to ANSI standards. Hence migrating from Oracle on UNIX to SQL Server on Windows® poses less of a challenge than would normally be expected when migrating from one proprietary computing system to another. Because SQL Server and Oracle are relational databases, it is not necessary to redesign the database. It is not necessary to rearchitect the application for performance or security because the two DBMSs are very similar in their architecture. The majority of the work involved is in accommodating the differences in the implementation of items such as indexes, data types, and SQL and DBMS features, such as partitioning.

To simplify the migration of the database, the entire process can be organized into four major tasks:

Figure 4.1 Tasks in the migration of a database

Figure 4.1 Tasks in the migration of a database

Each of the four major tasks includes a set of subtasks as follows:

  1. Migrate the database architecture

    1. Build the SQL Server instance

    2. Configure the server

    3. Migrate the storage architecture

  2. Migrate the schemas

    1. Migrate the schema

    2. Migrate the schema objects

  3. Migrate the users

    1. Create user accounts

    2. Create roles and grant privileges

  4. Migrate the data

    1. Plan the data migration

    2. Execute the data migration

In Chapters 5 through 8, examples are provided for each of the major tasks and subtasks at the end of the chapter. The discussion of testing the migrated database is in Chapter 9, "Developing: Databases — Unit Testing the Migration." Oracle DBAs who are new to SQL Server should also read Appendix A, "SQL Server for Oracle Professionals," which provides a condensed guide for educating Oracle DBAs about the internal architecture of SQL Server by leveraging existing knowledge of Oracle. The appendix also provides information about how to perform some of the common administrative functions in SQL Server.


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


Send us your comments or suggestions